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]

All good info, but is this being overlooked as the likely reason?

...not sure if it is because of the pass through or maybe because I added another query to the sequence

 

[jarett]

All good info, but is this being overlooked as the likely reason?

As I am just a beginner in all this, the example from the previous person in my position that created the DB I am using as a guide had two queries that were created that linked to a PTQ (same table on both queries). This is what I added, previous I had one query linking one query to one table then the table to a third table (all in same query). I switched it to one query linking a query to a PTQ then the result of that query put into another query linked to the same PTQ. If that makes any sense I think that might have eliminated some of the over lapping relationships.

 

[xenou]

I can't fault your reasoning or your effort although in this particular case rearranging the queries by adding one is not by itself good or bad - it might make things better but it might not. When I look at your query there is nothing wrong with the syntax or the join structure - but whether it produces duplicates or not is entirely dependent on the structure of the tables that are joined to and the data in those tables. So that is why I am pointing you towards investigating your data sources - you have to know your data in order to write good queries.