Query - Unnecessary Duplication of Records

[boardingbo]

I'm using Access 2003 and have ran into an issue with one of my queries. The query pulls from two different queries and when it does so, it will squar the number of records that are duplicated. For example, if I have 12 records, it will report 144 of those records. What is really hard to understand is that once the query is ran, I can sort the list, and it will return to me the correct number of records (ie 12, instead of 144). If I build a sort into the query, the problem still prosist. If you have any ideas that could help, please let me know.

Thanks ahead of time.

boardingbo

[ChipR]

If you have a field that is the primary key in one table and the foreign key in the other, you want to do a JOIN on that field rather than just SELECT FROM both tables. You can create a join in the query design view by dragging the key field from one query to the matching key field of the other query.

If you are already using a JOIN, then we will probably need to see the SQL for your query.

[NeoPa]

This sounds very much like a JOIN issue (See SQL JOINs). Tables linked without any JOINs will create a Cartesian Product, as you seem to be describing.

As Chip says though (to paraphrase at least), you give us very little information to work with.

[Stewart Ross]

Clearly a join issue as Chip and NeoPa have said. You mention that you get 12 records returned when you "sort" the data - I would suggest that you have actually performed a Group By query (a totals query, indicated by the sum symbol ∑ on the toolbar) as the grouping will automatically eliminate duplicate rows. Sorting has nothing to do with it, as you yourself found when you have set up a sorted version of your original query I guess.

Anyhow, as Chip and NeoPa have both advised, you have a missing join between your two queries. You need to resolve that, because Cartesian products can generate large numbers of rows behind the scenes which grouping only eliminates AFTER the large number of rows has been produced -very, very inefficient and time-consuming.

-Stewart