Getting most recent date for multiple records

[szag via AccessMonster.com]

Simplified, I have a table with the following fields:

Staff_Name
Category
Hours
Date_Record_Added

I need to be able to select the the most recent ("Date_Record_Added" field)
record for each combination of Staff_Name & Category. I know how to do it if
there was only one combination but with 20 staff and 30 categories there are
obviously many combinations of which I need to have the last record added.

Any ideas?

 

[mike@work]

i think u need to have staff and categories in seperate tables, if i
understand ur question correctly. i reccommend u find some information
concerning normalization.

all u need to do is run a query for each member of staff and category and
select max for the date field. it sounds long winded but its simple and will
work.

 

[John Spencer]

Two query solution
First query gets the max date for each combination of staff and category

SELECT Staff_Name, Category, Max(Date_Record_Added) as LastDate
FROM YourTable
GROUP BY Staff_Name, Category

Second query uses that to return the entire record from your table
SELECT Ta.*
FROM YourTable as Ta INNER JOIN FirstQuery as Q
On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.Category
AND Ta.Date_Record_Added = Q.LastDate

That could also be written as one query as long as your table and field names
consist of only letters, numbers, and underscore characters.
SELECT Ta.*
FROM YourTable as Ta INNER JOIN

(SELECT Staff_Name, Category, Max(Date_Record_Added) as LastDate
FROM YourTable
GROUP BY Staff_Name, Category) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.Category
AND Ta.Date_Record_Added = Q.LastDate


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[szag via AccessMonster.com]

Thanks John.

I am a real novice when it comes to SQL so I think I am doing something wrong.
first I went into queries and chose Query , SQL Specific, Union Query from
the menu. Then I out in the following code to match my fields:

SELECT Ta.*
FROM T_Trans_Data as Ta INNER JOIN

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name,forCategory_ID) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q. forCategory_ID
AND Ta.Add_Date = Q.LastDate

I got a message saying invalid use of ".", "!", "()".

Can you see what I am doing wrong?

Two query solution
First query gets the max date for each combination of staff and category

SELECT Staff_Name, Category, Max(Date_Record_Added) as LastDate
FROM YourTable
GROUP BY Staff_Name, Category

Second query uses that to return the entire record from your table
SELECT Ta.*
FROM YourTable as Ta INNER JOIN FirstQuery as Q
On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.Category
AND Ta.Date_Record_Added = Q.LastDate

That could also be written as one query as long as your table and field names
consist of only letters, numbers, and underscore characters.
SELECT Ta.*
FROM YourTable as Ta INNER JOIN

(SELECT Staff_Name, Category, Max(Date_Record_Added) as LastDate
FROM YourTable
GROUP BY Staff_Name, Category) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.Category
AND Ta.Date_Record_Added = Q.LastDate

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Simplified, I have a table with the following fields:

[quoted text clipped - 9 lines]

Any ideas?

 

[John Spencer]

Open a new query.
Switch to SQL View
Paste in your query string

Also, you have a space in the following line between Q. and ForCategory_ID
AND Ta.Category = Q. forCategory_ID


SELECT Ta.*
FROM T_Trans_Data as Ta INNER JOIN

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.forCategory_ID
AND Ta.Add_Date = Q.LastDate

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John.

I am a real novice when it comes to SQL so I think I am doing something wrong.
first I went into queries and chose Query , SQL Specific, Union Query from
the menu. Then I out in the following code to match my fields:

SELECT Ta.*
FROM T_Trans_Data as Ta INNER JOIN

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name,forCategory_ID) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q. forCategory_ID
AND Ta.Add_Date = Q.LastDate

I got a message saying invalid use of ".", "!", "()".

Can you see what I am doing wrong?

Two query solution
First query gets the max date for each combination of staff and category

SELECT Staff_Name, Category, Max(Date_Record_Added) as LastDate
FROM YourTable
GROUP BY Staff_Name, Category

Second query uses that to return the entire record from your table
SELECT Ta.*
FROM YourTable as Ta INNER JOIN FirstQuery as Q
On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.Category
AND Ta.Date_Record_Added = Q.LastDate

That could also be written as one query as long as your table and field names
consist of only letters, numbers, and underscore characters.
SELECT Ta.*
FROM YourTable as Ta INNER JOIN
(SELECT Staff_Name, Category, Max(Date_Record_Added) as LastDate
FROM YourTable
GROUP BY Staff_Name, Category) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.Category
AND Ta.Date_Record_Added = Q.LastDate

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Simplified, I have a table with the following fields:

[quoted text clipped - 9 lines]

Any ideas?

 

[szag via AccessMonster.com]

John - thanks! works perfect...now I just wish I knew SQL so I knew what I
did.

Open a new query.
Switch to SQL View
Paste in your query string

Also, you have a space in the following line between Q. and ForCategory_ID
AND Ta.Category = Q. forCategory_ID

SELECT Ta.*
FROM T_Trans_Data as Ta INNER JOIN

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.forCategory_ID
AND Ta.Add_Date = Q.LastDate

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John.

[quoted text clipped - 52 lines]

[quoted text clipped - 9 lines]
Any ideas?

 

[John Spencer]

Invest in the book
SQL Queries for Mere Mortals (Hernandez and Viescas)

What we did was use a query as table - Subquery in the from clause.


(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

That got treated as if it were a table.

Then we joined that subquery to your table on name, category, and date so that
only records in your table that matched those items in the subquery would show up.

The authors use a lot of pages to explain that little summary and make it
understandable.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John - thanks! works perfect...now I just wish I knew SQL so I knew what I
did.

Open a new query.
Switch to SQL View
Paste in your query string

Also, you have a space in the following line between Q. and ForCategory_ID
AND Ta.Category = Q. forCategory_ID

SELECT Ta.*
FROM T_Trans_Data as Ta INNER JOIN
(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

On Ta.Staff_Name = Q.Staff_Name
AND Ta.Category = Q.forCategory_ID
AND Ta.Add_Date = Q.LastDate

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Thanks John.

[quoted text clipped - 52 lines]

[quoted text clipped - 9 lines]
Any ideas?

 

[szag via AccessMonster.com]

Really appreciate it John and I will look for that book to help me in
learning SQL queries.

Invest in the book
SQL Queries for Mere Mortals (Hernandez and Viescas)

What we did was use a query as table - Subquery in the from clause.

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

That got treated as if it were a table.

Then we joined that subquery to your table on name, category, and date so that
only records in your table that matched those items in the subquery would show up.

The authors use a lot of pages to explain that little summary and make it
understandable.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John - thanks! works perfect...now I just wish I knew SQL so I knew what I
did.

[quoted text clipped - 26 lines]

[quoted text clipped - 9 lines]
Any ideas?

 

[szag via AccessMonster.com]

John:

this was working well for a while then just today I realized my query's
weren't working right so I went back into the SQL query we created and got
the message:

The MS Jet database cannot find the table or query 'Select Staff_Name,
forcategory_ID, Max(AddDate) as LastDate From T_Trans_Data GroupBy
Staff_Name, for Category_ID. Make sure it exists and its name is spelled
correctly.

I didn't change the name of any of the field or tables. Any idea how it can
be working fine then this happens?

Invest in the book
SQL Queries for Mere Mortals (Hernandez and Viescas)

What we did was use a query as table - Subquery in the from clause.

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

That got treated as if it were a table.

Then we joined that subquery to your table on name, category, and date so that
only records in your table that matched those items in the subquery would show up.

The authors use a lot of pages to explain that little summary and make it
understandable.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John - thanks! works perfect...now I just wish I knew SQL so I knew what I
did.

[quoted text clipped - 26 lines]

[quoted text clipped - 9 lines]
Any ideas?

 

[John Spencer]

I have not one clue on what has happened. I would suggest that you post the
question as a new posting instead of burying it down here.

If you can get to it, try posting the actual SQL of the query along with the
error message. Perhaps someone will have an idea of what is going on.

Also, post your version of Windows and Access.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John:

this was working well for a while then just today I realized my query's
weren't working right so I went back into the SQL query we created and got
the message:

The MS Jet database cannot find the table or query 'Select Staff_Name,
forcategory_ID, Max(AddDate) as LastDate From T_Trans_Data GroupBy
Staff_Name, for Category_ID. Make sure it exists and its name is spelled
correctly.

I didn't change the name of any of the field or tables. Any idea how it can
be working fine then this happens?

Invest in the book
SQL Queries for Mere Mortals (Hernandez and Viescas)

What we did was use a query as table - Subquery in the from clause.

(SELECT Staff_Name, forCategory_ID, Max(Add_Date) as LastDate
FROM T_Trans_Data
GROUP BY Staff_Name, forCategory_ID) as Q

That got treated as if it were a table.

Then we joined that subquery to your table on name, category, and date so that
only records in your table that matched those items in the subquery would show up.

The authors use a lot of pages to explain that little summary and make it
understandable.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John - thanks! works perfect...now I just wish I knew SQL so I knew what I
did.

[quoted text clipped - 26 lines]

[quoted text clipped - 9 lines]
Any ideas?