Group-by queries in Access - please help

[Driep]

I am working with table (linked to ORACLE via ODBC) that has a 'jobnumber' field with many date records (each date for the job can have myn time records)

As you can guess this is a time recording system. I want to find the record with the maximum date for a specific job in this table. I am using the following SQL statment:

SELECT TTLJOB, Max(TTLDATE) AS MaxDATE
FROM ITS_TTLTIM_SA
WHERE TTLGEBPROG = "P"
GROUP BY TTLJOB;

When I run this SQL for a specific job I get one record with the correct data.

As soon as I run it for all records in the table I get multiple (max) records for a job that has only one record. And the dates in the result set for the job is complete incorrect.

Job 10003 has only one record in the table with a date of 18-JAN-1994.

With the SQL above, I get the following results
TTLJOB MaxDATE
10003 02/06/2003
10003 03/06/2003
10003 04/06/2003
10003 10/06/2003
10003 28/05/2003
10003 25/06/2003
10003 20/06/2003
10003 28/05/2003
10003 04/06/2003
10003 03/06/2003

WHY ? Any suggestions ?

I'll appreciate your response

Phlip

 

[tedsmith]

Why would you use grouping if you only want to see one record?
If the job number only has one record then you cant get a maximum on one record.

Id try it in MSAccess Queries first and paste the code to VB6 if that is what you are using.

Also sometimes you have to use USA date formats with access date queries if you dont live in USA

If you have a number of jobs with the same job number, this should give you the maximum
SELECT TTLJOB, Max(Format(TTLDATE,"m/d/yyyy&quot) AS MaxDATE
FROM ITS_TTLTIM_SA
WHERE TTLJOB=10003

or something like that