469,272 Members | 1,467 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

Losing data in a multi-table query

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?
Aug 29 '08 #1
3 2475
32,171 Expert Mod 16PB
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.
Aug 29 '08 #2
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?

Sep 9 '08 #3
32,171 Expert Mod 16PB
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.
Sep 10 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Eric | last post: by
32 posts views Thread by Neil Ginsberg | last post: by
2 posts views Thread by Frank Bishop | last post: by
9 posts views Thread by Adrian Parker | last post: by
7 posts views Thread by Mike | last post: by
7 posts views Thread by DaTurk | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.