Using several tables in a query and getting no results

[tanyalee123]

When I try to build a query that has a relationship "down the line" I start experiencing problems. For instance, say I have my main table PATIENTS that has a relationship with a table PROGRAM. Then the PROGRAM table has a relationship with several other tables, APPOINTMENTS, BABY_INFO, BREASTFEEDING_CLASSES.



So trying to create a nice report that shows everything that's going on with the patient, I don't have a problem using the PATIENTS table and the PROGRAM table, but when I start adding on the tables that have a relationship with the PROGRAM table to the query, my query comes up empty. If I remove them, it runs ok and I get my results.

I know this is a very general question without much specifics, but I'm wondering if anyone out there has experienced something similar, or has some tips or advice. Thank you!

Tanya

[JoeM]

If you do just standard "INNER JOINS", the only records what will be returned are records that have matches in ALL of the tables involved. When dealing with multiple tables, this may not be what you want, because quite often, there are not matching records in every single table.

So what really matters what kind of joins that you use, and which tables are joined to which other tables. What I usually try to do is find my "main" table that drives what records I want to return records from, regardless of whether or not they have matching records in the other tables. Then, perform LEFT JOINS from this table to your other tables (a LEFT JOIN will return ALL the records from your "Source" table, and any matches found in the other table). Then, you can add your other tables and other JOINS (which might be INNER or LEFT joins, depending on the nature of the data, which tables you are linking to, and what you want to return). See these two links for more on Access joins:
http://office.microsoft.com/en-us/ac...001032251.aspx
http://www.techonthenet.com/access/queries/joins2.php

If you need more specific help, as it pertains to your particular problem, you will need to tell us how all your tables are related, the nature of the data between the tables (one-to-one, one-to-many, many-to-many relationships), and what you want returned from each one.