Retrieving Most Recent Records...

[Bubbis Thedog]

Howdy, I'd like to retrieve records matching the following criteria:

1) Location = "Here"
2) Action = "Added"
3) Date_of_Action = MAX(Date_of_Maximum) for each name in the Full_Name field

Here's an example of the data:

Full_Name     Extension     Location     Action     Date_of_Action
Bob             1             Here       Added        3/1/2005
Jane            2             Here       Added        3/2/2005
Bob             1                        Removed      3/8/2005
Sandy           3             Else       Added        3/9/2005
Bob             4                        Added        3/10/2005

I'd like my query to return

Full_Name     Extension     Action     Date_of_Action
Jane            2           Added        3/2/2005
Bob             4           Added        3/10/2005

Does this require two queries?

THANKS FOR ANY HELP, FRIENDS!

 

[Norie]

Does this require two queries?

Not as far as I can see.

Just use a totals query with the specified criteria against Location and Action.

And set Date_of_Action to Max.

 

[Bubbis Thedog]

norie, I can't quite get it. I've tried

SELECT Full_Name, Extension, Location, Action, Date_of_Action
FROM MAIN
WHERE (Location="Here" AND Action="Added"
   AND Date_of_Action = (SELECT Max(Date_of_Action)
                         FROM MAIN
                         WHERE Location="Here" AND Action="Added");

but still only get the most recent record out of all records. What am I doing wrong here?

Thanks again for your help, norie.

 

[Norie]

Why are you using a subquery for the Max date?

I tried this with data the you posted .

SELECT MAIN.Full_Name, MAIN.Extension, MAIN.Action, Max(MAIN.Date_of_Action) AS MaxOfDate_of_Action
FROM MAIN
WHERE (((MAIN.Location)="Here"))
GROUP BY MAIN.Full_Name, MAIN.Extension, MAIN.Action
HAVING (((MAIN.Action)="Added"));

And it returned what you wanted.

 

[Bubbis Thedog]

I was using a subquery because I was looking at it the wrong way. I suspected that HAVING was the key early on, but neglected to work with it.

Thanks so much, norie, for your help once again. I'm trying!

 

[Aladin Akyurek]

Wouldn't

SELECT MAIN.Full_Name, Max(MAIN.Date_of_Action) AS MaxOfDate_of_Action
FROM MAIN
WHERE (((MAIN.Location)="Here") AND ((MAIN.Action)="Added"))
GROUP BY MAIN.Full_Name;

suffice?

 

[Norie]

Aladin

That's right but you would need to change the default Group By to Where and it wouldn't actually display Location and Action.

 

[Bubbis Thedog]

Thanks for the added insight, norie and Aladin.

 

[Bubbis Thedog]

Aladin, thanks for your help. I tested the statement that you suggested. It works fine until I add MAIN.Extension to the SELECT clause. I get an error telling me that

'You tried to execute a query that does not include the specified expression 'Extension' as part of an aggregate function.'

I really need to see the extension that belongs to the employee (MAIN.Full_Name) on the most recent date. I can't seem to figure out the fix. Can you assist me further, please?


Norie, thanks for your help, and sorry to keep nagging about this... I can't group by Extension because that will return a Full_Name more than once --one for each extension. You see, this is a log of employees and what extensions they belong to. If they change extensions, I need to log 1) when they changed and 2) what their new extension is. So, I think the only grouping will be by Full_Name. Am I wrong here?

So, when I remove MAIN.Extension from the grouping, I get the above error as well. Any suggestions?

Thanks to both of you.

 

[Norie]

Remove the Main.Extension from the SELECT and GROUP BY.