By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,359 Members | 1,543 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,359 IT Pros & Developers. It's quick & easy.

Query returns repeating data

P: 4
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.

Attached Images
File Type: jpg Query SQL.jpg (189.4 KB, 138 views)
File Type: jpg query results.jpg (135.7 KB, 137 views)
File Type: jpg Master relationships.jpg (133.8 KB, 138 views)
File Type: jpg Query Builder.jpg (125.5 KB, 140 views)
Dec 12 '19 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,602
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.
Expand|Select|Wrap|Line Numbers
  2.      , B.Data
  3.      , C.Data
  4. FROM   (A
  5.         LEFT JOIN
  6.         B
  7.   ON    A.PK=B.FK)
  8.        LEFT JOIN
  9.        C
  10.   ON   A.PK=C.FK
With the SQL above each possible set of data is produced :
Expand|Select|Wrap|Line Numbers
  1. A.PK    B.Data  C.Data
  2.  X       1       1
  3.  X       1       2
  4.  X       1       3
  5.  X       2       1
  6.  X       2       2
  7.  X       2       3
Six result records. This is what you should be expecting if that's your design.
4 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.