By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,678 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,678 IT Pros & Developers. It's quick & easy.

Losing data in a multi-table query

P: 2
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
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,754
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

P: 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

Expert Mod 15k+
P: 31,754
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.