[AccessD] Cant's Get the Last Row in a Query with Group By

Rojas, Joe joe.rojas at symmetrynb.com
Wed May 21 09:50:29 CDT 2008


Hello,

I'm trying to use the Last aggregate function when using the GROUP BY
clause in a query but I'm not getting the desired results.
My query has three fields, JobNum, PartNum, and TAC (total actual cost).
The primary key in the underling tables is JobNum. This table has many
jobs for each partnum.

I'm trying to group by PartNum and I'm using Last to return the last
JobNum and TAC.
The problem is that the last record is not the record that I want. It
seems to be the first record. I tried using First instead of Last and
the results are the same.
I decided to change the FROM clause to a SELECT statement that used
ORDER BY but no luck.

If my table contained:
Job Num	PartNum	TAC
011		123		$500
022		123		$700
033		123		$1000

My expectation is that the results would be:
033		123		$1000

This implies a secondary sort on JobNum which is where I'm stuck.

Here is what I have so far.

SELECT Last(T1.JobNum) AS LastOfJobNum, T1.PartNum, Last(T1.TAC) AS
LastOfTAC
FROM [SELECT PASSJobHead.JobNum, PASSJobHead.PartNum, PUB_JobAsmbl.TAC
FROM PASSJobHead INNER JOIN PUB_JobAsmbl ON (PASSJobHead.Company =
PUB_JobAsmbl.Company) AND (PASSJobHead.JobNum = PUB_JobAsmbl.JobNum)
ORDER BY PASSJobHead.PartNum, PASSJobHead.JobNum]. AS T1
GROUP BY T1.PartNum;

Any ideas?

Joe Rojas
Information Technology Manager
Symmetry Medical New Bedford
P: 508.998.4575
C:508.838.4717
F:508.995.9597
joe.rojas at symmetrynb.com




More information about the AccessD mailing list