Query returns repeating data

[Jackerman99]

I have a Query that is comprised of 4 tables. The 'main' table is 'Estimate table', the second is 'Estimate Items Subform Table', the third is 'Estimate Details Table' and the fourth is 'Change Order Table'.

The 'Estimate Details Table' houses the components that make up the items in the Item subform table. The change order table keeps track of change orders and all of this is linked to the main Estimate Table.

When I run the query the results are correct for the Items and components but the Change order results are duplicated for every line item of estimate item details. I have the relationships set up as 1 to many for change orders, Items and Details back to the Estimate Table.

My forms run fine but when I run this query which drives a master report I get this repeated data. I have uploaded screen shots that might help explain. Can't seem to pin down the reason. Any and all help is greatly appreciated.
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10146d157615806 9/query-sql.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10147d157615806 9/query-results.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10148d157615806 9/master-relationships.j pg[/IMGNOTHUMB]
[IMGNOTHUMB]https://bytes.com/attachments/attachment/10149d157615806 9/query-builder.jpg[/IMGNOTHUMB]

[NeoPa]

Hi Jackerman.

Even if your picture of the SQL is very little use in determining what you're trying to do, it does at least illustrate very well that your SQL is very long and complicated. Enough to put most off venturing anywhere near it I suspect.

When asking questions it will serve you to take the time to cut out most of the complication and leave the question dealing with the pertinent details.

As I say, I couldn't possibly commit the time it would take to decipher your SQL to just this one question, but from your description in general I may have an explanation.

How are your different tables linked in your query? A simplified structure will explain how this can produce the results you're seeing.

Assume you have 3 tables - A, B & C. A is the main table and B & C both have data that has FKs across to A. Start with a single record in A with a PK value of X. B also has two records with X as the FK & C has three. To help with the illustration B & C both have Fields called Data with numbers from 1 to 2 in B and from 1 to 3 in C.

SELECT A.PK
     , B.Data
     , C.Data
FROM   (A
        LEFT JOIN
        B
  ON    A.PK=B.FK)
       LEFT JOIN
       C
  ON   A.PK=C.FK

With the SQL above each possible set of data is produced :

[B]A.PK    B.Data  C.Data[/B]
 X       1       1
 X       1       2
 X       1       3
 X       2       1
 X       2       2
 X       2       3

Six result records. This is what you should be expecting if that's your design.