Duplicate Rows in Query

[jarett]

Struggling to understand some of the post on this issue, I think this is the closest example that I found, Access query expression builder is creating duplicate rows in query, please help.. However I am not 100% sure because some records produce the correct query results and no duplicate rows. My first query is simple with one inner join looking for "PurchaseOrderNo" in two tables (I can post SQL if needed). I am assuming the second query is my problem, can anyone see if I need to add another query to elimanate some of the records? SQL below

SELECT Lapco_PO1.PurchaseOrderDate, Lapco_PO1.PurchaseOrderNo, IIf(IsNull(PO068_MXPPOTierDistribution!QuantityOrdered),([Lapco_PO1]!QuantityOrdered),PO068_MXPPOTierDistribution!QuantityOrdered) AS [Qty Ordered], Lapco_PO1.ItemCode, PO068_MXPPOTierDistribution.LotSerialNo, IM068_MXPUnivProdCode.UpcCode, Lapco_PO1.ShipToName, Lapco_PO1.ShipToAddress1, Lapco_PO1.ShipToAddress2, Lapco_PO1.ShipToAddress3, Lapco_PO1.ShipToCity, Lapco_PO1.ShipToState, Lapco_PO1.ShipToZipCode, Lapco_PO1.ShipToCountryCode, Lapco_PO1.FOB, Lapco_PO1.ShipVia, Lapco_PO1.UDF_USEREMAIL, Lapco_PO1.DateCreated, Lapco_PO1.TimeCreated, Lapco_PO1.Hour, TimeSerial([Lapco_PO1]!Hour,[Lapco_PO1]!Minute,[Lapco_PO1]!Seconds) AS [Order Time], Lapco_PO1.LineKey
FROM (Lapco_PO1 LEFT JOIN PO068_MXPPOTierDistribution ON (Lapco_PO1.ItemCode = PO068_MXPPOTierDistribution.ItemCode) AND (Lapco_PO1.PurchaseOrderNo = PO068_MXPPOTierDistribution.PurchaseOrderNo)) LEFT JOIN IM068_MXPUnivProdCode ON (PO068_MXPPOTierDistribution.ItemCode = IM068_MXPUnivProdCode.ItemCode) AND (PO068_MXPPOTierDistribution.LotSerialNo = IM068_MXPUnivProdCode.LotSerialNo)
ORDER BY Lapco_PO1.LineKey DESC , Lapco_PO1.PurchaseOrderNo DESC;

The highlighted records are examples of some of the records that are duplicated, however in the next example the records only produce one row for the unique "PurchaseOrderNo", I dont know if it is because that particular PO only has one line on it so it doesn't meet all the criteria for access to create more than one result?

 

[Micron]

Try SELECT DISTINCT Lapco_ ...
or
SELECT DISTINCTROW Lapco_...

I see 4 duplicate records there, but that's only based on the fields you show. In the second pic, there are no duplicates so you might not fully understand what a duplicate is, or I'm missing something. Where qty is 7 and ItemCode is the same, the size is different, thus not a dupe.

 

[jarett]

Try SELECT DISTINCT Lapco_ ...
or
SELECT DISTINCTROW Lapco_...

I see 4 duplicate records there, but that's only based on the fields you show. In the second pic, there are no duplicates so you might not fully understand what a duplicate is, or I'm missing something. Where qty is 7 and ItemCode is the same, the size is different, thus not a dupe.

Correct the second pic does not contain any duplicates, but I'm thinking those records are just like the records in the first pic, they meet the same criteria, just not sure why some rows are repeated and others not. I tried the SELECT DISTINCT and it gave me an error citing the ORDER BY clause as a conflict. The SELECT DISTINCTROW ran but produced the same results as without.

I am looking at a another similar DB and query setup that someone did before me, they used some pass through queries for some of the queries, specifically on one table, could this be a work around?

 

[Micron]

I missed that you mentioned the 2nd batch were not dupes; sorry. Pass through queries likely won't solve the issue unless by chance they eliminate the cause of the duplicates. Your issue must be your joins. In design view, double click on the join and read what the resulting dialog shows you in (more or less) plain English. Since you are asking for ALL records from a table and those that match the other table you are likely to get dupes in a lot of cases. If examining and experimenting with the joins doesn't provide the answer, start with 2 tables with your left join and see what you get. That might give you dupes. If not, do the same using the second pair only and see what you get. If you must use outer joins it might be best to represent a pair of tables as a query instead join that in your final query.

 

[xenou]

Generally in order to investigate dupes you don't understand, take one of the dupes and see what the data is in each table related to that record.

The issue with dupes is always because of the way your joins are constructed - or because your original data is not clean to start with - or because of poor design in the DB (heaven forbid).

They can be ignored (too some extent) with SELECT DISTINCT, but they can only be repaired by fixing the joins (or cleaning up the data if duplicates exist in the base tables when they should not).

Note: your error with SELECT DISTINCT is (probably) that you are ordering by a column not in the select clause. In any case, as a final resort you can always go without ordering and do a second select on the first select -- select MyQuery.* from (select * from query1) as MyQuery order by MyQuery.Field1, MyQuery.Field2 -- the main thing is not to let this get in your way as the ordering is just icing on the cake after all the hard work of getting the query data is done.

 

[jarett]

Well not sure the reason it worked but I changed some of the queries to go through the pass through query for two of the tables and it removed the duplicate results. Like I said not sure if it is because of the pass through or maybe because I added another query to the sequence. Plenty of SQL code between the 4 queries, do I need to post the code?

 

[Micron]

A pass through query simply allows you to access data in a remote db without creating table links in Access. Your pass through sql or added query has eliminated the reason for the duplicates. Pretty sure it has nothing to do with the fact that it's a pass through. I don't need to see the code because you've solved it and even if the cause could be spotted it sounds like there's a ton of sql in the solution that would have to be compared to the sql you've already posted. Personally I would pass on the opportunity but who knows, someone else might care to look at it.

 

[xenou]

I would have to agree that a pass through query as such would not change how the query works. It is possible you are getting different results now because data is coming from another data source (in which case your 'local' data has dupes and the data in the other database you are "passing through" to does not).

 

[jarett]

I would have to agree that a pass through query as such would not change how the query works. It is possible you are getting different results now because data is coming from another data source (in which case your 'local' data has dupes and the data in the other database you are "passing through" to does not).

I am pretty sure there are no dupes in the source data, all the data is coming from a Sage 100 system, so not 100% sure but I haven't seen anything that would allow a dupe on the front end.

 

[xenou]

Really you don't look at the front end to see if dupes are possible - that is more of a backend feature. What is the difference between when you use a pass through query and you don't - I would think that is two different datasources, so the main difference would be the data. Unless you have changed your query SQL as well as making it pass-through at the same time.

Not that I would really expect dupes in the source data - but dupes come from how you write your queries, and in some cases you don't see the problem in one dataset but you do in another - all queries require a lot of testing to make sure they work all of the time and not just some of the time.