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