Getting Cartesian Product in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a novice Access user, in 2003. I have a table that is linked to 2 other
separate tables with 1 to many relationships (3 tables total). When I run a
query with all three tables, I'm getting the cartesian product of all the
tables. How do I eliminate all the duplicates?
 
Did you create any joins between the tables? Maybe you should share your SQL
view and tell us a little about your table structures.
 
SELECT Tbl_ShiftActivity.ShiftActivityID, Tbl_ShiftActivity.Date,
Tbl_ShiftActivity.Shift, Tbl_ShiftObservations.ShiftObservationID,
Tbl_ShiftObservations.ShiftActivityID,
Tbl_ShiftObservations.OperationObserved, Tbl_ShiftObservations.Start,
Tbl_ShiftObservations.End, Tbl_ShiftObservations.Observation,
Tbl_ShiftAgents.Agent
FROM (Tbl_ShiftActivity LEFT JOIN Tbl_ShiftAgents ON
Tbl_ShiftActivity.ShiftActivityID = Tbl_ShiftAgents.ShiftActivityID) INNER
JOIN Tbl_ShiftObservations ON Tbl_ShiftActivity.ShiftActivityID =
Tbl_ShiftObservations.ShiftActivityID
GROUP BY Tbl_ShiftActivity.ShiftActivityID, Tbl_ShiftActivity.Date,
Tbl_ShiftActivity.Shift, Tbl_ShiftObservations.ShiftObservationID,
Tbl_ShiftObservations.ShiftActivityID,
Tbl_ShiftObservations.OperationObserved, Tbl_ShiftObservations.Start,
Tbl_ShiftObservations.End, Tbl_ShiftObservations.Observation,
Tbl_ShiftAgents.Agent;

Yes the joins are there (one to many). The fields that end with "ID" are
PK's. Tbl_ShiftActivity one to many Tbl_ShiftObservations.
Tbl_ShiftActivity one to many Tbl_shiftAgent. "ShiftActivityID" is the child
in Tbl_shiftActivity and Tbl_shiftAgent. "ShiftActivityID" is the parent in
Tbl_ShiftActivity. I hope I explained this correctly.
 
I would seriously question your need to create a recordset of all three
tables. This type of relationship is normally displayed to the user in a
main form based on tbl_ShiftActivity and two subforms based on
tbl_ShiftObservations and tbl_ShiftAgents.

Combining all of this data into a single query should never be necessary.
 
I'm running this query for a report that needs to be generated that shows the
information from these tables. The form that uses these tables is directly
from the tables, not a query. Unless I can create a report from multiple
tables.
 
Back
Top