query doubles up records

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!

--
Message posted via http://www.accessmonster.com

On Feb 8, 8:06 am, "Slez via AccessMonster.com" u23064@uwe wrote:
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!

--
Message posted viahttp://www.accessmonster.com

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
..

"Slez via AccessMonster.com" u23064@uwe wrote in message
news:6d7fc16450ecd@uwe...
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!

--
Message posted via http://www.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 Lepack wrote:
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]
--
Message posted viahttp://www.accessmonster.com

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

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

John Spencer wrote:
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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

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

Slez wrote:
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]
Any thoughts on where to start with troubleshooting?
Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

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!

Slez wrote:
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]
Any thoughts on where to start with troubleshooting?
Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1