Group-by queries in Access

[Driep]

Hi,

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 many 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 completely incorrect.

E.g.
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

 

[scriverb]

Run this SQL to verify exactly how many records you have. You may have only done this but let's make sure.

SELECT TTLJOB, TTLDATE
FROM ITS_TTLTIM_SA
WHERE TTLGEBPROG = "P" and TTLJOB = 10003;

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Driep]

Hi,

I did run this SQL

SELECT TTLJOB, TTLDATE
FROM ITS_TTLTIM_SA
WHERE TTLGEBPROG = "P" and TTLJOB = 10003;

and I've got only one record for that job number in the table.

PPP

 

[scriverb]

I have created a mock duplicate table and run your query and it runs perfectly. One record per TTLJOB with the maximum date for the job number.

The difference is that I am using the inputmask and format for mm/dd/yyyy where you are using dd/mm/yyyy. I have seen numerous posts here at TT that strange results are found when this data entry is used.

Let's try this:
SELECT TTLJOB, Max(Format(TTLDATE, "mm/dd/yyyy&quot AS MaxDATE
FROM ITS_TTLTIM_SA
WHERE TTLGEBPROG = "P"
GROUP BY TTLJOB;

Post back with this results.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Driep]

Perfect ! Thanks

 

[scriverb]

Great. Glad that I could assist you. Good luck with your project.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???