query doubles up records

  • Thread starter Slez via AccessMonster.com
  • Start date
S

Slez via AccessMonster.com

What could cause a query to return a duplicate set of records? I can view
the records in the table, and for example there may be 6 records associated
to the criteria. Let's say there are records:
A
B
C
D
E
F

When the query runs, it returns:
A
B
C
D
E
F
A
B
C
D
E
F

Ultimately, the reports that use this query as the record source display
double the records of what they'r supposed to, but it's because of the query.
Any thoughts on where to start with troubleshooting?
Thanks!
 
J

Jason Lepack

What could cause a query to return a duplicate set of records? I can view
the records in the table, and for example there may be 6 records associated
to the criteria. Let's say there are records:
A
B
C
D
E
F

When the query runs, it returns:
A
B
C
D
E
F
A
B
C
D
E
F

Ultimately, the reports that use this query as the record source display
double the records of what they'r supposed to, but it's because of the query.
Any thoughts on where to start with troubleshooting?
Thanks!

Post the SQL of your query and we'll give it a look-see
 
J

John Spencer

Do you have more than one table in the query? That is usually the source of
seeing duplicates.

You can open the query in design view and set the query's unique values
property to Yes. That will eliminate duplicate rows by showing only one of
them.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Slez via AccessMonster.com

Upon further review/testing, I have found that it takes the number of records
in tblChangeItem and multiplies the records by that amount. For example, if
there are 2 records in tblChangeItem, it returns twice the records in
tblChangeItemDetail...if there 3 records in tblChangeItem, it returns triple
the records in tblChangeItemDetail.

Here is the SQL. Thanks for taking the time to check this out!

SELECT Project.ProjectID, Project.ProjectName, Project.JobNumber,
tblChangeRequest.ChangeRequestID, tblChangeRequest.CRDate, tblChangeRequest.
Estimator, Project.SalesTax, Project.TaxExempt, tblChangeRequest.
SalesTaxAmount, tblChangeRequest.UseTaxAmount, tblChangeItem.RoomNumber & " -
" & tblChangeItem.ItemNumber AS ItemLabel, [Quantity]*([UnitPrice]+[QuoteCost]
) AS LineTotalCost, [LineTotalCost]*[Markup] AS SellPrice, [Quantity]*(
[QuoteCost]+[MaterialCost]) AS LineMaterialCost, [Quantity]*(
[MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor,
tblChangeItemDetail.Quantity, tblChangeItemDetail.ProductDescription,
tblChangeItemDetail.Markup, tblChangeItemDetail.QuoteCost, Product.
ProductCode, Product.CBDCode, Product.MachineLaborHours, Product.
BuildingLaborHours, Product.MaterialCost, Product.UOM, Product.UnitCost,
Labor.CBDCodeID, Product.LibraryReference, tblChangeRequest.ChangeDescription,
tblChangeItem.ItemDescription

FROM Labor INNER JOIN ((Product INNER JOIN ((Project INNER JOIN tblChangeItem
ON Project.ProjectID = tblChangeItem.ProjectID) INNER JOIN
tblChangeItemDetail ON (tblChangeItem.ChangeRequestID = tblChangeItemDetail.
ChangeRequestID) AND (tblChangeItem.ProjectID = tblChangeItemDetail.ProjectID)
AND (Project.ProjectID = tblChangeItemDetail.ProjectID)) ON Product.
ProductDescription = tblChangeItemDetail.ProductDescription) INNER JOIN
tblChangeRequest ON (Project.ProjectID = tblChangeRequest.ProjectID) AND
(tblChangeItem.ProjectID = tblChangeRequest.ProjectID) AND (tblChangeItem.
ChangeRequestID = tblChangeRequest.ChangeRequestID)) ON Labor.CBDCode =
Product.CBDCode

ORDER BY Project.ProjectID, tblChangeItem.RoomNumber & " - " & tblChangeItem.
ItemNumber;


Jason said:
What could cause a query to return a duplicate set of records? I can view
the records in the table, and for example there may be 6 records associated
[quoted text clipped - 27 lines]
Post the SQL of your query and we'll give it a look-see
 
S

Slez via AccessMonster.com

I tried setting Unique Values as well as Unique Records to "Yes", but it did
not change the results.
Slez

John said:
Do you have more than one table in the query? That is usually the source of
seeing duplicates.

You can open the query in design view and set the query's unique values
property to Yes. That will eliminate duplicate rows by showing only one of
them.
What could cause a query to return a duplicate set of records? I can view
the records in the table, and for example there may be 6 records
[quoted text clipped - 26 lines]
Any thoughts on where to start with troubleshooting?
Thanks!
 
S

Slez via AccessMonster.com

Hold the phone guys! I sometimes wonder about myself. My problem lies in
the table, not the query. I do not have a primary key in one of the tables
and have not set the relationship properly.

Sorry for the bother...I should have caught this on my own sooner!
Slez
I tried setting Unique Values as well as Unique Records to "Yes", but it did
not change the results.
Slez
Do you have more than one table in the query? That is usually the source of
seeing duplicates.
[quoted text clipped - 8 lines]
 
S

Slez via AccessMonster.com

I need to retract my last post. I corrected the primary key and relationship
issue but the query still returns the duplicate records. Please check out my
SQL as discussed earlier. Thanks!
Hold the phone guys! I sometimes wonder about myself. My problem lies in
the table, not the query. I do not have a primary key in one of the tables
and have not set the relationship properly.

Sorry for the bother...I should have caught this on my own sooner!
Slez
I tried setting Unique Values as well as Unique Records to "Yes", but it did
not change the results.
[quoted text clipped - 5 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top