I have a database which has two tables; "Names" which has the names of the patients of a medical practice. this table has 3 fields (names), (ID) and (DOB) the (names)filed contains the names of the patients and the field (ID) has auto-enter serial number, every patient has one record in this table.

the second table "visits" which has a record for every visit any patient has had (i.e. every patient can have more than one record). this table has the fields "Patients Name","Date of the visit" "Complaint" and "Treatment".

I want to create a relationship through which i can review all the visits a specific patient had using a drop-down list with its value comes from the (patient Names) field of the "Names" table. it seems easy but I dont want to see it in a portal. I'd rather see it as separate records in a form in layout view. and in this case i will use next and previous buttons to flick through these records.

I appreciate any help

Does the visits table have a patient ID field? The tables should be related by ID; if you're relating by name you're asking for trouble.

If I understand what you're trying to do:

Make a list layout in Visits.

Put a header part on the layout with a global PatientID field.

You'll still need to click a button after selecting the patient, unless you use a script trigger plugin such as zippScript.

The button/script would either go to related records or perform a find.

Example file attached.

list_example.zip

I agree with Tom that you should never use names as a key for a relationship. Table key fields should not be displayed for the user, but held for the integrity of the table connections.

Another route you could go is to use separate windows for the browsing of appointments, like in this sample file.

Hope this helps,

Tim

PatientVisits.zip