Eliminating Duplicate Records in a Query

[Chuck Moore]

I need help with a query and related report. The query
selects records from a primary table and related records
from 3 other tables that have relationships with the
primary table. The 3 tables collect multiple records
based on the key field of the primary table. For example,
based on an autShiftID in the primary table, the 3 related
tables store multiple occurrences of Code Blues, Code
Whites and Code Reds that may occur on the same shift.
The 3 related tables are subforms on the main form and
have master/child settings. The data is stored correctly
and the form/subforms work perfectly.

The problem is with the query. If, on one shift, there
are only occurrences in one related table, the query lists
the occurrences properly. If however, there are 5
occurrences in one table and only three in another, for
the same shift, the 3 occurrences are repeated to fill the
5 rows of the first set of occurrences.

Is there any way, in the query or related report, to have
the occurrences appear only the number of times that they
should and not the number of times of the tables with the
greatest number of occurrences?

 

[Gerald Stanley]

Can you post the query SQL.

If I have understood the requirement, it should be
achievable with a Union Query and the results suggest that
you are using a multi-Join Query.

Gerald Stanley MCSD