Complex Query Woe

[blyxx86]

Hey there, I'm running into a slight problem today...

I have a few things to show...

I'm running into duplicate values being shown in my query, but I cannot use a "Select DISTINCT" as it removes the records...

It's a complex query, so I'm including 3 parts.. an image of the joins, an example of the duplicate values and the SQL.

SELECT tblRequest.CustomerTracking, tblRequest.ReqDate, tblLocations.LocationCode, tblInvoice.TrackingNumber, [Prefix] & tblModel_1.ModelName AS ShippedModel, tblInvoiceDetail.Serial, [StdQuantity]*tblInvoiceDetail.Quantity AS ShipQty, tblInvoice.InvoiceNote, tblRequestType.RequestType, tblInvoice.InvoiceDate
FROM (((tblModelDetail INNER JOIN tblModel AS tblModel_1 ON tblModelDetail.IncludesModelID = tblModel_1.ModelID) INNER JOIN (tblModel INNER JOIN tblProduct ON tblModel.ModelID = tblProduct.ModelID) ON tblModelDetail.ModelID = tblModel.ModelID) INNER JOIN tblProduct AS tblProduct_1 ON (tblProduct_1.CustomerID = tblProduct.CustomerID) AND (tblModel_1.ModelID = tblProduct_1.ModelID)) INNER JOIN ((tblLocations RIGHT JOIN (tblInvoice INNER JOIN tblCustomer ON tblInvoice.CustomerID = tblCustomer.CustomerID) ON tblLocations.LocationID = tblInvoice.LocationID) INNER JOIN (tblInvoiceDetail LEFT JOIN (tblRequestType RIGHT JOIN (qry_Qtys RIGHT JOIN (tblRequest LEFT JOIN tblRequestDetail ON tblRequest.RequestID = tblRequestDetail.RequestID) ON qry_Qtys.RequestID = tblRequestDetail.RequestID) ON tblRequestType.RequestTypeID = tblRequest.RequestTypeID) ON tblInvoiceDetail.RequestID = tblRequest.RequestID) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID) ON tblProduct.ProductID = tblInvoiceDetail.ProductID;
 

Shown here are the duplicate values... However only one ROS-LS2208, ROS-255270120 and ROS-207164501 should be shown. I know the problem lies within that "loop" of tblmodel/tblmodeldetail/tblproduct. However that loop needs to be there for reporting purposes... It shows the parts of "KITS" as being shipped individually. I know.. it's confusing, but perhaps there is another way to accomplish this? Any thoughts?

Maybe you can help me figure out a way to restructure the query?

[ADezii]

In my humble opinion, as long as you have the 'Circular Relationships' existing between tblProduct, tblProduct_1, tblModel_1, tblModelDetail, and tblModel, you will be encountering a host of problems. What I am seeing is analogous to Circular References in programming jargon and I feel as though you will need a major restructuring of the Joins. I would suggest, however, that you wait and see what other Moderators/Experts suggest before you initiate any kind of changes. Just for curiosity, why the duplication on tblModel and tblProduct as far as the Relationships go?

[blyxx86]

I use the circular reference to find the quantities of units shipped from "KITS"... a KIT includes those additional units. It is for the inventory management side. If the inventory was kept by full KIT models. Also, when shipments are made, but only partial orders are shipped then it keeps track of it for me.

So, in order to have the models listed properly I need to have the ModelDetails look up the models again and then the product table needs to match the customer again otherwise I have similar models being shown for all the customers.

I understand the circular lookup isn't the best scenario, but I believe it is necessary for what I am doing. Unless someone else has another way of accomplishing what I am doing...

[blyxx86]

I may have got something working...

SELECT DISTINCT tblRequest.CustomerTracking, tblRequest.ReqDate, tblLocations.LocationCode, tblInvoice.TrackingNumber, tblInvoiceDetail.Serial, [StdQuantity]*tblInvoiceDetail.Quantity AS ShipQty, tblInvoice.InvoiceNote, tblRequestType.RequestType, tblInvoice.InvoiceDate, qry_Qtys.InvoicedQty, qry_Qtys.RequestedQty, [Prefix] & tblModel_1.ModelName AS Model, tblModel.ModelName, tblInvoiceDetail.InvoiceDetailID, tblProduct.ProductID, tblModel.ModelID, tblModelDetail.ModelDetailID, tblModel_1.ModelID, tblProduct_1.ProductID, tblInvoice.InvoiceID, qry_Qtys.RequestID
FROM (tblLocations RIGHT JOIN (tblInvoice INNER JOIN tblCustomer ON tblInvoice.CustomerID = tblCustomer.CustomerID) ON tblLocations.LocationID = tblInvoice.LocationID) INNER JOIN (tblProduct AS tblProduct_1 INNER JOIN (tblModel AS tblModel_1 INNER JOIN (((tblModel INNER JOIN tblModelDetail ON tblModel.ModelID = tblModelDetail.ModelID) INNER JOIN tblProduct ON tblModel.ModelID = tblProduct.ModelID) INNER JOIN (tblInvoiceDetail LEFT JOIN (tblRequestType RIGHT JOIN (qry_Qtys RIGHT JOIN (tblRequest LEFT JOIN tblRequestDetail ON tblRequest.RequestID = tblRequestDetail.RequestID) ON qry_Qtys.RequestID = tblRequestDetail.RequestID) ON tblRequestType.RequestTypeID = tblRequest.RequestTypeID) ON tblInvoiceDetail.RequestID = tblRequest.RequestID) ON tblProduct.ProductID = tblInvoiceDetail.ProductID) ON tblModel_1.ModelID = tblModelDetail.IncludesModelID) ON (tblModel_1.ModelID = tblProduct_1.ModelID) AND (tblProduct_1.CustomerID = tblProduct.CustomerID)) ON tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID;
 

New field was included (InvoiceDetailID)... It let me use SELECT DISTINCT and still show the records...

While what I did worked, I'm sure there is a better way to accomplish it... But please let me know what you think...

I ran into a small problem when a QTY 0 was being used... However that wouldn't make sense in any real world situation (Qty 0 shipped??? Why not just not include it on the shipping records...) It was always shown as backordered, but for some reason a duplicate record was thrown in when the qty was 0. Weird. Changing it to 1 or deleting it fixed that. I also added a validation rule to make all input numbers >0.