Help Query to return limited records

[Cam]

Hello,

I am trying to create a query (total) to limits the records so it returns no
more than one records with the same order#, but have no success so far.
I have fields for part#, order#, operation, other.... where there can be
more than one record of multiple operations in an order#.

Sample data:
Part# Order# Operation Mach other..........
ABC 000001 5
ABC 000012 20
NOC 000020 10
NOC 000020 38
NOC 000020 40
NOC 000035 20

Results:
Part# Order# Operation Mach other..........
ABC 000001 5
ABC 000012 20
NOC 000020 40
NOC 000035 20

NOTE: 000020 has three operation 10, 38 and 40. so it will only show the
last operation of 000020 order#. Thanks

 

[Dale Fye]

Cam,

If the last operation of any particular order # is denoted by the largest
operation #, then you should be able to use:

Select Part#, Order#, Operation, Mach, ...
FROM yourTable
INNER JOIN
(SELECT Order#, Max(Operation) as MaxOp
FROM yourTable
GROUP BY Order#) as Temp
ON yourTable.Order# = Temp.Order#
AND yourTable.Operation = yourTable.Temp.Order#

But this query will not be updateable, so if you need it to be updateable,
you might try:

Select Part#, Order#, Operation, Mach, ...
FROM yourTable
WHERE Operation = DMAX("Operation", "yourTable", "Order# = " &
yourTable.Order#)

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[KARL DEWEY]

First create a query like this --
SELECT [Part#], [Order#], Max([Operation]) AS MaxOfOperation
FROM YourTable
GROUP BY [Part#], [Order#];

Save as Last_Part_Order. Then this query ---

SELECT [Part#], [Order#], [Operation], Mach, Other
FROM YourTable INNER JOIN Last_Part_Order ON YourTable.[Part#] =
Last_Part_Order.[Part#] AND YourTable.[Order#] = Last_Part_Order.[Order#] AND
YourTable.Operation = Last_Part_Order.MaxOfOperation;