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

Jennifer Gross jengross at gte.net
Wed May 21 12:18:11 CDT 2008


Hi Joe,

I have gotten a lot of help with aggregate queries through Dev Ashish's site
- http://www.mvps.org/access/queries/qry0020.htm 

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rojas, Joe
Sent: Wednesday, May 21, 2008 6:50 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Cant's Get the Last Row in a Query with Group By

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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list