Losing data in a multi-table query

[IAuditor]

Access 2003, XP - I have a multi-table query that is losing data. 1 table is a lookup (Hierarchy), and the other 3 are virtual duplicates (all with a one-to-many relationship with the lookup) except for some field names. The Hierarchy field is identically named in all 3 tables. The query starts with the Hierarchy number, then descriptions, etc. Next are 2 long-integer fields that are used to calculate a Difference and %ofDifference fields. Up to here, everthing is fine. But, when I bring in the second data table, the resulting dataset looses 3 records. If the 3rd table is brought in, it looses another 12.

I did find that the disappearing record's hierarchy number was in one or two tables, but not in another. In other words, it appears that the hierarchy number had to be present in all 3 tables in order for the record to show. Why should this be true?

[NeoPa]

I would guess from the fact that you're asking this, that your method of JOINing the tables is to use an INNER JOIN. In the QBE (design) grid this would involve simply dropping the field from one table on to another.

In SQL (Select View / SQL) this will show as an INNER JOIN.

INNER JOINs stipulate that the data must match AND BE PRESENT on both sides of the JOIN. This means if any of the tables don't have matching records then none of them will show (for that record).

What I suggest you need is a LEFT JOIN. This can be achieved in the QBE by double-clicking on the line illustrating the link and selecting option 2.

[IAuditor]

Yes indeed, it is a joining problem. But, it didn't turn out to be as simple as changing the type. If a row does not contain data in table 1, it will not pick up the data for that row from either table 2 or table 3. I will simply see a blank row. The problem is, I want to see that row populated if there is data is ANY of the tables.

Can you help with this?

Thanks

[NeoPa]

What you need is an full OUTER JOIN. Unfortunately not supported in Jet SQL (what Access uses).

A work-around, is to add the tables in together with a WHERE clause that only allows those that are linked together to show. Without knowing your table layout details I can't go much further.