Problem with duplicate record in query results

[BJF]

Hello,

The query I have made is to generate an Entered Sales Order Log for a specific time frame (user sets time frame, then runs query for a report)

The query is comprised of 5 tables in order to pull the information that I want in the report.

The problem I'm experiencing is that one of the records in the recordset that I am querying for this week is being shown twice in my report.

The reason for this is because one of the tables in the report (a shipping/tracking table) has 2 records for that order.

I confirmed this by deleting one of the Shipping/tracking records from that order, and re-ran the report and then the order shows up only once in my report.

I'm not sure how to instruct the query to only show one record for each order, no matter how many subform records are within another related table for that order.

Can i put something in the criteria for my [SO] field which is my sales order field?

Thanks for any suggestions

BJF

 

[jdraw]

Show us the query SQL.
And a jpg of your relationships.

 

[BJF]

Here is the sql:

SELECT tblSalesOrders.SO, tblSalesOrders.DateEntered, tblSalesOrders.ProductNum, tblSalesOrders.OrderDate, tblSalesOrders.ServiceID, tblSalesOrders.ScheduledShipDate, tblSalesOrders.RequestedShipDate, tblSalesOrdersDetails.ProductNum, tblSalesOrdersDetails.WidthInches, tblSalesOrdersDetails.WidthMM, tblSalesOrdersDetails.QtyYardsOrdered, tblStandardCost.CustNum, tblStandardCost.Gauge, tblStandardCost.ColorId, [ExtensionForBackOrder]+[Tax] AS AmountEntered, tblClients.Acct, tblClients.ExtensionID, tblClients.Company, tblClients.BillToCustomer, tblTrackingInfo.DateOfShipment, tblSalesOrders.BackOrderOf, tblSalesOrders.OriginalSalesOrderNumber, tblSalesOrdersDetails.ExtensionForBackOrder, tblTrackingInfo.DateOfShipment, tblSalesOrders.TotalCostForEnteredReport
FROM tblStandardCost INNER JOIN (((tblClients INNER JOIN tblSalesOrders ON tblClients.Acct = tblSalesOrders.Acct) INNER JOIN tblSalesOrdersDetails ON tblSalesOrders.SO = tblSalesOrdersDetails.SO) LEFT JOIN tblTrackingInfo ON tblSalesOrders.SO = tblTrackingInfo.SO) ON tblStandardCost.ProductNum = tblSalesOrdersDetails.ProductNum
WHERE (((tblSalesOrders.DateEntered) Between [forms]![frmSalesOrders].[QStart] And [forms]![frmSalesOrders].[QEnd]))
ORDER BY tblSalesOrders.SO DESC;


here is a pic of table relationships:

 

[David R]

Have you looked into WHY tblTrackingInfo has two entries for that Sales Order? If it was a return and reship, you may need to add a boolean field to disregard returns from the query.

 

[BJF]

Hi David,

tblTrackingInfo has two entries for that order because the order was shipped by two different carriers each with their own amount. tblTrackingInfo is used as a subform in my Sales order form allowing for as many entries as needed, although it is rarely, almost never more than one record. This was the first time an order had two records for shipping, and the first time I noticed what my report would do in this case.

I'm looking to tell the query to not allow duplicate sales order numbers . The field is tblSalesOrders.[SO]

How do I do this?

 

[David R]

When there are two SOs, what do you want it to show? Latest ship date? Only for those portions that have already shipped?

Are you familiar with Total queries? That's probably what you're going to be using, so familiarizing yourself with that process will help and may let you solve the rest yourself.