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!

 

[Aladin Akyurek]

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.

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

 

[Bubbis Thedog]

I really appreciate your alls' assistance, but both solutions do not work. If I add a new record for an employee that was initially entered, say, a week ago, with the exact same structure as in the original table, but only change the extension and date to today, the query still retrieves both records for the employee for whom the changes were made (the one from a week ago and the one for today).

Thanks for your alls' patience thus far; I really appreciate the help.

 

[Andrew Fergus]

Hi Vinny

The 'Group by' on the name and extension are going to cause problems. Given they have unique values, the query is going to include both records in any 'totals' query. AFAIK the only way to do this with one query is to assume that the extension increases in value each time a record is added such that the SQL for 1 query would look like this :

SELECT MAIN.Full_Name, Max(MAIN.Extension) AS MaxOfExtension, MAIN.Location, MAIN.Action, Max(MAIN.Date_Of_Action) AS MaxOfDate_Of_Action
FROM MAIN
GROUP BY MAIN.Full_Name, MAIN.Location, MAIN.Action
HAVING (((MAIN.Location)="Here") AND ((MAIN.Action)="Added"));

However, (there is always the 'however') that was probably a bad assumption regarding the extension number, so the simple solution would be to use 2 queries, as follows :

Query 1 :
SELECT MAIN.Full_Name, MAIN.Location, MAIN.Action, Max(MAIN.Date_Of_Action) AS MaxOfDate_Of_Action
FROM MAIN
GROUP BY MAIN.Full_Name, MAIN.Location, MAIN.Action
HAVING (((MAIN.Location)="Here") AND ((MAIN.Action)="Added"));

For the purposes of the second query below, I saved the first query as 'Query_Bubbis1'.

Query 2 :
SELECT Query_Bubbis1.Full_Name, MAIN.Extension, Query_Bubbis1.Location, Query_Bubbis1.Action, Query_Bubbis1.MaxOfDate_Of_Action
FROM Query_Bubbis1 LEFT JOIN MAIN ON (Query_Bubbis1.Action = MAIN.Action) AND (Query_Bubbis1.MaxOfDate_Of_Action = MAIN.Date_Of_Action) AND (Query_Bubbis1.Location = MAIN.Location) AND (Query_Bubbis1.Full_Name = MAIN.Full_Name);

HTH, Andrew.

 

[Bubbis Thedog]

AHA! Then 2 queries were needed! That's what I suspected from the get-go... not that what I think means all that much or is veritable whatsoever. hahahahaha

Thanks once again, Andrew, for your time! I'll test this tomorrow at work and let you know how it turns out. And thanks, norie and Aladin, for helping me out as well.

 

[Aladin Akyurek]

AHA! Then 2 queries were needed! That's what I suspected from the get-go... not that what I think means all that much or is veritable whatsoever. hahahahaha

Thanks once again, Andrew, for your time! I'll test this tomorrow at work and let you know how it turns out. And thanks, norie and Aladin, for helping me out as well.

It's true that this type of questions always requires a subquery. I couldn't figure out what would be the key of MAIN. Care to make that explicit?

 

[Bubbis Thedog]

Hi, Aladin.

There is a primary key for each record called MAIN.MAIN_ID. But I tried to incorporate that --i thought-- every which way possible using norie's and your statement structures, could never get it to work. I apologize for not making clear that there was a unique key; I hope I didn't make things harder than they needed to be.

Thanks for the inquiry.

 

[Bubbis Thedog]

Andrew, your solution worked perfectly for what I requested. Thanks a million for spending the time to help me. However, I should've given you all of my MAIN table to show you exactly what I wanted to happen. Here goes; I'll understand if you or no one else bothers with this. I've included a primary key this time. Here is table MAIN:

PK    Name     Extension     Location     Action     Date     Hunt_Group     Department
10    Bob      1             Here       Added        3/1/2005     100          PLCSR
20    Jane     2             Here       Added        3/2/2005     200          CLCSR
30    Bob      1                        Removed      3/8/2005
40    Sandy    3             Else       Added        3/9/2005     100          PLCSR
50    Bob      4                        Added        3/10/2005
60    Sandy                             Removed      3/14/2005    100         PLCSR
70    Sandy                             Added        3/15/2005    200          CLCSR

QUERY ONE:

The most current extension of all employees matching these criteria: Here, Added, Hunt Group 100, Department PLCSR. The query should return

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

QUERY TWO:

The most current extension of all employees matching these criteria: Else, Added, Hunt Group 100, Department PLCSR. The query should return

Full_Name     Extension     Date_of_Action
Sandy           3            3/9/2005

QUERY THREE:

The most current hunt group of all employees matching these criteria: Else, Added, Hunt Group 200, Department CLCSR. The query should return

Full_Name     Date_of_Action
Sandy           3/15/2005

I hope that these queries are possible; I really have tried to get them right, but have failed. They seem very tricky if they're at all possible. I should note that each field in the MAIN table, except for PK and Date_of_Action, is a child of a static parent table. For instance, the EXTENSIONS table has one column with the primary keys, and another with the unique extensions. The reason for this is to ensure that only a predefined extension, hunt group, employee, etc. is entered into a field in MAIN.

Whoever can get these queries to work is a stud, IMO. I'm sorry to be a continual nag about this; this will be my last question concerning this thread. I'll completely understand if no one responds, because I know that this is time-consuming.

Thanks so much in advance.

 

[Andrew Fergus]

Hi Vinny
Just to clarify a couple of points - which will require an answer, and another post, from you

Query 1 - if this is to return line 'Bob, 4, 3/10/2005' then shouldn't the sample data you provided have a location of 'here' for the line PK50, as well as a 'hunt_group' value of 100 and a department of 'PLCSR'? Unless I am reading your question incorrectly, I don't think your sample data matches the desired query result.

Query 3 - isn't the line PK 70 missing a location of 'Else' to qualify as meeting the criteria of this query?

How is a person uniquely identified? For example, how do we know that Bob on line PK10 is, or isn't, the same Bob on the line PK 50?

Also, in your sample data, will a person with an action of 'removed' have the same extension number from the previous 'added' record for the same name? This is the case with PK 30 but not PK 60. Should every record have an extension, or are there occasionally some blank records (see the sample data PK60 and PK70). Lastly, if a person is added, removed and then added again, will the new extension number always be greater than the previous number? Or could it be anything?

If you could clarify these points then we can provide the the correct solutions without having to make assumptions.

Andrew

 

[Bubbis Thedog]

Thanks for the response, Andrew! You're a glutton for punishment, I see! hahahahahaha

Believe it or not, friend, there are no errors in my post. In query 1, I'd like to retrieve the most recent extension of all employees currently residing in Here, that are currently in Hunt Group 100, etc. The reason is that each record in MAIN can have either the Extension field populated (not necessarily in sequence), the Hunt Group field populated, the Location field populated, the Department field populated, or could even have two or three or all four of these fields populated. The PK, Employee, Action, and Date columns must always be populated. Clear as mud?

Have I screwed up the DB design again? Is there another way to keep a log like I'm wanting? I need be able to tell when an employee changes extensions, hunt groups, locations, or departments. The extensions, hunt groups, etc., I'd like predefined in tables (this is actually how I have it now, with these parent tables serving as the parents to the child MAIN table.) in order to preserve data integrity in the log table (currently MAIN). The Action field indicates whether the item in question has been added or removed. The most recent added date will tell me when the action took place. This is how I'm doing it now, but I'll take any suggestions obviously!

(Sorry it took so long to reply, I've been working.)

Thanks as always!

 

[Andrew Fergus]

Hi Vinny
Ha! I'm not a glutton for punishment (yet) - I just want to see people get the result they seek!

In the sample data you have the following lines for Bob :

snip, I'm not sure your nice formatting will hold, but here goes:
PK Name Ext. Location Action Date Hunt_Group Department
10 Bob 1 Here Added 3/1/2005 100 PLCSR
30 Bob 1 Removed 3/8/2005
50 Bob 4 Added 3/10/2005

And your query 1, has the following criteria :

The most current extension of all employees matching these criteria: Here, Added, Hunt Group 100, Department PLCSR

So, how is it that this line should be returned in the query?

Full_Name Extension Date_of_Action
Bob 4 3/10/2005

It does not have a location of 'here' nor a hunt_group of '100' and it doesn't have a department of 'PLCSR'. Am I not reading this correctly, or have I missed something?

Andrew