query doubles up records

  • Thread starter Thread starter Slez via AccessMonster.com
  • Start date 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!
 
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!
Click to expand...

Post the SQL of your query and we'll give it a look-see
 
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
..
 
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
Click to expand...
[quoted text clipped - 27 lines]
Post the SQL of your query and we'll give it a look-see
Click to expand...
 
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
Click to expand...
[quoted text clipped - 26 lines]
Any thoughts on where to start with troubleshooting?
Thanks!
Click to expand...
Click to expand...
 
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.
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
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.
Click to expand...
[quoted text clipped - 5 lines]
Click to expand...
 
Back
Top