Querying cascading tables

[Brownie1987]

Hi,

I'm struggling with getting a query to function looking across multiple tables to create a report.

The context is that I am trying to build a database for patient records, so that we can both produce a daily, usable list and audit various aspects.

The tables in use are: Patients, Admissions, Operations and Complications. They are related as follows:



Every admission relates to a single patient. Each operation relates to a single admission for a single patient. Each Complication relates to a single operation, on a single admission for a single patient.

Each is linked by a primary key (Medical Record Number in Patients, AdmissionID in Admissions etc) which is one-to-many related to a FK field in the subsequent tables. (Complications has FK fields for MRN, Admission and Operation etc).

(Ideally, I eventually also want a problems table which was valid for each admission per patient; but for the time being am using a short text field in Admissions)

I've built a query which ran successfully just around patients and admissions. However, as I wanted to build a report which showed a list of currently admitted patients and included their operations and complications, I needed to build a query which pulled all of this info.

When I run the query, however, it returns no records.

Can anyone suggest a solution, please?

[pbaldy]

Hard to say without seeing the query, but I'd guess you need to edit the join(s) between tables. Right or double click on the join line to do that.

[Brownie1987]

Thank you.

To be clear, what do I need to do to make that work? Currently, they are set up as one-to-many with enforced referential integrity and cascaded updates.

I had assumed the problem has to do with the fact that there might be multiple operations per admission per patient and the simple tabular return which the query gives might not have been able to handle that?

[pbaldy]

So you didn't try it? A query can handle multiple records; what it won't by default is if there are no records in the many table. Changing the join will fix that.

[Brownie1987]

I'm very novice so I'm not sure what I need to do! I think that's the exact problem, having had a bit more of a play with things.

Can you tell me precisely what I need to do to fix that, please?

Thanks

[pbaldy]

Sorry, I thought I did. With the query in design view:

Right or double click on the join line to do that.

The correct choice should be obvious at that point.

[Brownie1987]

I was trying to mess around with the relationships. Couldn’t work out what you meant!

Thanks, that’s fixed it.

[pbaldy]

Happy to help, and welcome to the site by the way!