desperately seeking latest date sort

C

coralreef

hi there;

new access 2007 user; created a report from another appl. imported to access
would like to sort the data which is employee pay file i would like to sort
to pull the latest date for every employee some with multiple entries to view
the latest date.

id dept div salary fstname lastname trans date
01 2 1 ....... joe been 050106
01 2 1 ....... joe been 060107
03 4 5 ....... jill was 060107


please help
 
J

John W. Vinson

hi there;

new access 2007 user; created a report from another appl. imported to access
would like to sort the data which is employee pay file i would like to sort
to pull the latest date for every employee some with multiple entries to view
the latest date.

id dept div salary fstname lastname trans date
01 2 1 ....... joe been 050106
01 2 1 ....... joe been 060107
03 4 5 ....... jill was 060107


please help
Click to expand...

What is the datatype of your "trans date" field - it's certainly not a
Date/Time value! Is it a Number or a Text field? Also, what is the meaning of
the numbers: is 050106 May 1 2006, or January 5, 2006, or January 6 2005?

You'll need to cast this non-date date into a Date field for Access to readily
sort and search it.
 
K

Ken Sheridan

I'm guessing the trans date column is in mmddyy format. If so, and your
regional short date format is set to mm/dd/yyyy or mm/dd/yy then this should
return the rows with the latest date per employee:

SELECT *
FROM YourTable AS T1
WHERE CDate(Format([trans date],"00/00/00")) =
(SELECT MAX(CDate(Format([trans date],"00/00/00")))
FROM YourTable AS T2
WHERE T2.id = T1.ID);

The following would return all rows sorted by employee name and then by
descending order of date:

SELECT *
FROM YourTable AS T1
ORDER BY lastname, fstname, id,
CDate(Format([trans date],"00/00/00")) DESC;

Note that its necessary to include the id after the names in the ORDER BY
clause to separate out two employees of the same name.

The above would also work with a European dd/mm/yyyy regional short date
format if the trans date was in ddmmyy format, i.e. one must reflect the
other.

Ken Sheridan
Stafford, England
 
C

coralreef

thanks ken;

this look/sounds like something that can make sense; now i just need to sort
it all out in my head and on screen; i don't have the good fortune of being
an Access 2007 guru like the rest of you; just trying to work it out...

Ken said:
I'm guessing the trans date column is in mmddyy format. If so, and your
regional short date format is set to mm/dd/yyyy or mm/dd/yy then this should
return the rows with the latest date per employee:

SELECT *
FROM YourTable AS T1
WHERE CDate(Format([trans date],"00/00/00")) =
(SELECT MAX(CDate(Format([trans date],"00/00/00")))
FROM YourTable AS T2
WHERE T2.id = T1.ID);

The following would return all rows sorted by employee name and then by
descending order of date:

SELECT *
FROM YourTable AS T1
ORDER BY lastname, fstname, id,
CDate(Format([trans date],"00/00/00")) DESC;

Note that its necessary to include the id after the names in the ORDER BY
clause to separate out two employees of the same name.

The above would also work with a European dd/mm/yyyy regional short date
format if the trans date was in ddmmyy format, i.e. one must reflect the
other.

Ken Sheridan
Stafford, England
hi there;
Click to expand...
[quoted text clipped - 9 lines]
please help
Click to expand...
Click to expand...
 
Top