Helo with removing duplicate values in a query

[David Sabot]

I have a query in access that is producing the following results:

OrderNumber	QTY	Product Price	Product Cost	FirstOfSumOfCost
431474	1	$10.99	$7.50	1.98
431475	1	$38.99	$17.50	3.28
431475	1	$17.99	$9.98	3.28
431475	1	$21.99	$0.00	3.28
431476	1	$6.99	$2.00	1.64

The query is displaying the same value for FirstOfSumOfCost if the OrderNumber repeats. I want it to show only the first instance of that field like I have below:

OrderNumber	QTY	Product Price	Product Cost	FirstOfSumOfCost
431474	1	$10.99	$7.50	1.98
431475	1	$38.99	$17.50	3.28
431475	1	$17.99	$9.98
431475	1	$21.99	$0.00
431476	1	$6.99	$2.00	1.64

Is there some formula or expression I need to make that happen?

[rpeare]

yes you can do this but it's extremely resource heavy and it relies on you ordering your data the same way every time. If you are not sorting your data at all you will not get consistent results. Based on your example there is no order (your query is probably more complex than you're showing) so what I am suggesting would not work but let's say you have the following table:

OrderID	OrderNumber	ItemNumber	Qty	ProductPrice	ProductCost
1	431474	1	1	$10.99	$7.50
2	431475	2	1	$38.99	$17.50
3	431475	3	1	$17.99	$9.98
4	431475	4	1	$21.99	$5.99
5	431476	5	1	$6.99	$2.00

Now you want to calculate and a % profit for each order

this query would give you a total profit for each order:

SELECT tblOrders.OrderID, tblOrders.OrderNumber, tblOrders.ItemNumber, tblOrders.Qty, tblOrders.ProductPrice, tblOrders.ProductCost, DSum("([productprice]*[qty]) -( [productcost] * [qty])","[tblOrders]","[OrderNumber] = " & [ordernumber]) AS TotalProfit
FROM tblOrders;

The results look like:

OrderID	OrderNumber	ItemNumber	Qty	ProductPrice	ProductCost	TotalProfit
1	431474	1	1	$10.99	$7.50	3.49
2	431475	2	1	$38.99	$17.50	45.5
3	431475	3	1	$17.99	$9.98	45.5
4	431475	4	1	$21.99	$5.99	45.5
5	431476	5	1	$6.99	$2.00	4.99

Now to only show the first instance I'm sorting this data BY ITEM NUMBER within each order, which means the item number must be unique on each order.

My query changes to:

SELECT tblOrders.OrderID, tblOrders.OrderNumber, tblOrders.ItemNumber, tblOrders.Qty, tblOrders.ProductPrice, tblOrders.ProductCost, DSum("([productprice]*[qty]) -( [productcost] * [qty])","[tblOrders]","[OrderNumber] = " & [ordernumber]) AS TotalProfit, IIf(DCount("*","[tblOrders]","[ordernumber] = " & [ordernumber] & " AND [itemnumber] < " & [itemnumber])=0,(DSum("([productprice]*[qty]) -( [productcost] * [qty])","[tblOrders]","[OrderNumber] = " & [ordernumber])),Null) AS FinalLabel, DCount("*","[tblOrders]","[ordernumber] = " & [ordernumber] & " AND [itemnumber] < " & [itemnumber]) AS Expr1
FROM tblOrders;

Which gives me the following result:

OrderID	OrderNumber	ItemNumber	Qty	ProductPrice	ProductCost	TotalProfit	FinalLabel	Expr1
1	431474	1	1	$10.99	$7.50	3.49	3.49	0
2	431475	2	1	$38.99	$17.50	45.5	45.5	0
3	431475	3	1	$17.99	$9.98	45.5		1
4	431475	4	1	$21.99	$5.99	45.5		2
5	431476	5	1	$6.99	$2.00	4.99	4.99	0

I've left my calculation fields in the query so it's easier for you to follow the method.

Just a word of warning, this is using a dcount and a dsum function in the same query. D functions are very memory intensive and really shouldn't be used for large datasets, but if you do use them make sure to compact and repair your data regularly.