Pick last date

  • Thread starter Thread starter mohd21uk via AccessMonster.com
  • Start date Start date
M

mohd21uk via AccessMonster.com

I have a table where I have a list of users and dates. I have created unique
codes which are used to identify a project. These codes are used by these
users to add time to. I have a field called code and a field called date. I
would like to pick up the last date that time has been added to the code. I
have provided an example below to illustrate this:

Code Date
GB2-E0002.02 12/06/2006
GB2-E0002.02 12/07/2006
GB2-E0002.02 15/06/2006
GB2-E0002.02 18/06/2006
GB2-E0002.02 19/06/2006
GB2-E0002.02 20/06/2006
GB2-E0003.02 01/07/2006
GB2-E0003.02 02/07/2006
GB2-E0003.02 03/07/2006
GB2-E0003.02 05/08/2006

I would like to query to provide me with :

GB2-E0002.02 12/07/2006
GB2-E0003.02 05/08/2006


Many Thanks
 
First off, NEVER name a field Date. It is a reserved keyword.
Where is the time? If you also hava a field, call it projTime, then try this
query:

SELECT TOP 2 Projects.Code, Projects.projDate, Projects.projTime
FROM Projects
WHERE (((Projects.projTime)<>"Nul"))
ORDER BY Projects.projDate DESC;
 
mohd21uk via AccessMonster.com said:
I have a table where I have a list of users and dates. I have created
unique
codes which are used to identify a project. These codes are used by these
users to add time to. I have a field called code and a field called date.
I
would like to pick up the last date that time has been added to the code.
I
have provided an example below to illustrate this:

Code Date
GB2-E0002.02 12/06/2006
GB2-E0002.02 12/07/2006
GB2-E0002.02 15/06/2006
GB2-E0002.02 18/06/2006
GB2-E0002.02 19/06/2006
GB2-E0002.02 20/06/2006
GB2-E0003.02 01/07/2006
GB2-E0003.02 02/07/2006
GB2-E0003.02 03/07/2006
GB2-E0003.02 05/08/2006

I would like to query to provide me with :

GB2-E0002.02 12/07/2006
GB2-E0003.02 05/08/2006
Click to expand...

First of all, don't use "Date" as a field name - it is a reserved word in
Access and you'll almost certainly come a cropper with it sooner or later.
Giving it a prefix would suffice (fldDate for example).

Create a new query and include the two fields. Click the "Totals" button
and choose "Group by" for the code field and "Max" for the date field. I
haven't actually tested this method but I think it will work.

Regards,
Keith.
www.keithwilby.com
 
That "Nul" in the SQL should be Null
First off, NEVER name a field Date. It is a reserved keyword.
Where is the time? If you also hava a field, call it projTime, then try this
query:

SELECT TOP 2 Projects.Code, Projects.projDate, Projects.projTime
FROM Projects
I have a table where I have a list of users and dates. I have created unique
codes which are used to identify a project. These codes are used by these
Click to expand...
[quoted text clipped - 20 lines]
Many Thanks
Click to expand...
Click to expand...
 
Back
Top