Re: Getting Cartesian Product in query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

"Duane Hookom" wrote:

> Did you create any joins between the tables? Maybe you should share your SQL
> view and tell us a little about your table structures.
>
> --
> Duane Hookom
> MS Access MVP
> --
>
> "arockets" <arockets@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:BAEEE334-459E-43E0-BD48-C8A3B4075D5F@xxxxxxxxxxxxxxxx
> > 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?
>
>
>
.

Relevant Pages

  • Re: creating a report from a recordset
    ... "Duane Hookom" wrote: ... >> run when I remove one table from the inner join, so I know it is the ... >> displayed in a msgbox as the variable strSQL: ...
    (microsoft.public.access.reports)
  • Re: Exclude Duplicates in Report Counts
    ... "Duane Hookom" wrote: ... I think "LEFT" should be replaced by "INNER". ... >> GROUP BY Location.[Assigned Consultant], ... >> I was not sure how to add the query if I get an error above? ...
    (microsoft.public.access.reports)
  • Re: MATHS: Combinations of N by K
    ... Duane Hookom wrote: ... >FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2 ... That will generate a lot of duplicates such as 1,2,3 and ... Prev by Date: ...
    (microsoft.public.access.formscoding)
  • RE: Joined table: Returns duplicated record
    ... Joins should generally include a unique field or fields on one side of the ... If you don't have unique values, you may have duplicates. ... Microsoft Access MVP ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • RE: Record Validation?? for newbie
    ... So I should break the schedule up into smaller tables? ... "Duane Hookom" wrote: ... duplicates' suggests an un-normalized table structure. ... There are about 45 different work locations within ...
    (microsoft.public.access.tablesdbdesign)