Help Using Finding Distinct Value in query

[Ira]

Hello All,

Hoping you can help with this query problem I am having.

Currently running this query:

SELECT UPSTrackingNumbers.TenderedToDriver, UPSTrackingNumbers.ShipDate, UPSTrackingNumbers.POD, UPSTrackingNumbers.ShippingMethod, UPSTrackingNumbers.VoidIndicator, OrderDetails.ProductCode, OrderDetails.ProductName, Orders.DamagedInTransit, UPSTrackingNumbers.PackageNegotiatedRatesCharge, Orders.Cancelled, Orders.Rep, Orders.AmazonEbayOrderID, Orders.EarliestShipDate, UPSTrackingNumbers.StatusCode, UPSTrackingNumbers.StatusDate, UPSTrackingNumbers.StatusDescription, UPSTrackingNumbers.ShippingService, UPSTrackingNumbers.TrackingNumber, UPSTrackingNumbers.ShippingMethod, UPSTrackingNumbers.Damaged, UPSTrackingNumbers.Delivered, UPSTrackingNumbers.DeliveredDate, UPSTrackingNumbers.TenderedToDriverDate, Orders.DamagedInTransit, Orders.PackedBy

FROM (UPSTrackingNumbers INNER JOIN Orders ON UPSTrackingNumbers.OrderNo = Orders.OrderID) LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID

WHERE (((UPSTrackingNumbers.ShipDate) > DateAdd("d", -30, Date())) And ((UPSTrackingNumbers.POD) Is Null) And ((UPSTrackingNumbers.VoidIndicator) <> "Y") And ((UPSTrackingNumbers.StatusCode) = "OC") And ((UPSTrackingNumbers.ShippingMethod) = "FedEx") And (Orders.Cancelled = 0) And (Date() > Orders.EarliestShipDate))

ORDER BY UPSTrackingNumbers.TenderedToDriver DESC , UPSTrackingNumbers.ShipDate DESC;

Which gives the result:

TenderedToDriver	ShipDate	POD	Expr1003	VoidIndicator	ProductCode	ProductName	Expr1007	PackageNegotiatedRatesCharge	Cancelled	Rep	AmazonEbayOrderID	EarliestShipDate	StatusCode	StatusDate	StatusDescription	ShippingService	TrackingNumber	ShippingMethod	Damaged	Delivered	DeliveredDate	TenderedToDriverDate	DamagedInTransit	PackedBy
No	1/30/2023		FedEx	N	COM-132	Quality Chemical Oven & Grill Cleaner Heavy-Duty / Fast acti	No	$25.77	No	Amazon	111-0698114-7605063	01/30/2023	OC	01/30/2023	Shipment information sent to FedEx	FEDEX_PTP_HOME_DELIVERY	394010384140	FedEx	No	No			No	Amelia
No	1/30/2023		FedEx	N	QCC-132-4	Oven & Grill Cleaner Heavy-Duty/High Power! Nothing Stronger	No	$25.77	No	Amazon	111-0698114-7605063	01/30/2023	OC	01/30/2023	Shipment information sent to FedEx	FEDEX_PTP_HOME_DELIVERY	394010384140	FedEx	No	No			No	Amelia

How can I limit the result to give just one distinct tracking number. If you notice, the tracking number [394010384140] is repeated twice.

Thank you in advance!

[Micron]

You will have to remove the product fields as they are different values, causing 2 records. You cannot expect single records where one or more fields have different data in records that are related to any of the other fields in the result.

[Ira]

You will have to remove the product fields as they are different values, causing 2 records. You cannot expect single records where one or more fields have different data in records that are related to any of the other fields in the result.

Can I create a nested query, so I can still have those fields?

[Micron]

Which of the 2 records would you choose in every case? What's wrong with what you have?

[CJ_London]

If you used a report you can group on the tracking number and associated fields and have the product data in the detail section

[Gicu]

You could make the query a totals query and for product code and name fields change the Total row from Group By to Min, Max, First or Last; another option would be to use one of the available concatenation functions to put all product codes for an order in a single field:http://www.accessmvp.com/thedbguy/co...itle=simplecsv

Cheers,

[orange]

It appears that in your example you have ordered 2 items com-132 and QCC-132-4. These 2 items have the same OrderID 111-0698114-7605063. The supplier is sending these as one package by FED EX and since it is 1 package it has 1 TrackingNumber.

Your query is selecting Items and, since there are 2, you will get a record for each. And each record includes the tracking number, so you get 2.

You could select Tracking number based on unique OrderId(since there is only 1 OrderId) and not include the item/prod info in the selection.

[xps35]

Can I create a nested query, so I can still have those fields?

Please show us the expected result, to known exactly what you want.