Checking a table for changes

[Rando]

Hi,

I have a table that gets updated on a weekly basis with the names of active users. I'm trying to make a query that tells me which users are missing from the current week compared to the entire table/all previous weeks. I only want the user to show up once, and it has to be the newest data for that user.

If I do a Select *, this is what the table would look like

Name     Effective_Date		Position	
User1	    09/18/10		A
User2	    09/18/10		A
User3	    09/18/10		A
User4	    09/18/10		A
User5	    09/18/10		A
User6	    09/18/10		A
User1	    09/25/10		B
User2	    09/25/10		B
User3	    09/25/10		B
User4	    09/25/10		B
User5	    09/25/10		B
User6	    09/25/10		B
User1	    10/02/10		C
User2	    10/02/10		C
User4	    10/02/10		C
User5	    10/02/10		C
User6	    10/02/10		C
User1	    10/09/10		D
User2	    10/09/10		D
User4	    10/09/10		D
User6	    10/09/10		D

Expected Query Results

Name	    Last_Date		Last_Position
User3	    09/25/10		B
User5	    10/02/10		C

How would I write a query that gives me this data? I've read about creating a query that compares the single table as if it were two tables, but I couldn't get that working.

[ajetrumpet]

maybe try something like this:

select name, max(effective_date) as [Last_date]

from ...

group by name

if you are doing an aggregation like that, you cannot pull both the date and the position because more than one record for each user would be returned. you may have to used 2 layers of stacked queries to get that done. But...you could try the following and see what it returns:

select name, max(effective_date) as [Last_date], max(position) as [Last_position]

from ...

group by name

[weekend00]

All data save in table TableAll, current week data save in table TableCurrent,
Query returns the result:

SELECT TableAll.Name, Max(TableAll.Effective_date) AS MaxOfEffective_date, Max(TableAll.Position) AS MaxOfPosition, TableCurrent.Name
FROM TableAll LEFT JOIN TableCurrent ON TableAll.Name = TableCurrent.Name
GROUP BY TableAll.Name, TableCurrent.Name
HAVING (((TableCurrent.Name) Is Null));

Note: this query assume that the latest record has max date and max position, if not, the result may be confused.