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]

Andrew

At first I thought Vinny was wrong with the expected results from the queries, but I think if you take a closer look at them, and the earlier posts you will see that they are right,

 

[Bubbis Thedog]

Hello, Andrew and norie! Thanks for continuing to help me. (I was just kidding about the 'glutton for punishment' thing, Andrew; I just know that I'm difficult to deal with at times! hahahaha)

Andrew: Let's look at your snippits:

PK 10 basically says that Bob joined our fine establishment on 3/1/2005. He was assigned ("Added") to an extension (that extension could have been used by another previous employee), a location, a Hunt Group, and a department.

PK 30 basically says that Bob was removed from an extension. He may have been having problems with his prior extension, etc. This record is telling us that Bob didn't change locations, Hunt Groups, or departments --just extensions.

PK 50 basically says that Bob was assigned a new extension (Don't let "new" confuse you; the extension could have been used before. I have a static parent table named EXTENSIONS in my database that is a list of all extensions ever used at our fine establishment.).

Is this clearer? If not, I'll try to re-explain it another way.

You can probably see now why I suspect that this type of querying is not even possible. I need some way of keeping a log of employees' current extensions, locations, Hunt Groups, and departments, and I couldn't think of a better way, while maintaining some normalization.

You all rock. Thanks so much!

 

[Andrew Fergus]

Thanks for the pointers Norie and the explanation Vinny - I now understand.

This can definitely be done but your data structures are making this, not just hard to understand, but really hard to code as well! Ignoring the correctness, or otherwise, of the data structures, you can work out the latest department, location, extention and hunt group using 2 queries for each and then a final (9th) query to re-combine all of the data into a useable format. The 9th query will provide the data required to do the three queries you asked for plus any others you want to do. Clunky but it works.

The SQL to work out the latest department is as follows :

SELECT Vinny_Data.Name, Max(Vinny_Data.Date) AS MaxOfDate
FROM Vinny_Data
WHERE (((Vinny_Data.Action)="Added") AND ((Vinny_Data.Department) Is Not Null))
GROUP BY Vinny_Data.Name;

Note : I used the table name 'Vinny_Data', be sure to use your actual table name. I saved this query as 'Vinny_Added_Dept' and used it as the basis for the next query to include the department description :

SELECT Vinny_Added_Dept.Name, Vinny_Added_Dept.MaxOfDate AS DateDept, Vinny_Data.Department
FROM Vinny_Added_Dept INNER JOIN Vinny_Data ON (Vinny_Added_Dept.MaxOfDate = Vinny_Data.Date) AND (Vinny_Added_Dept.Name = Vinny_Data.Name);

I saved this query as 'Vinny_Current_Dept'.

Repeat this process for extension, location and hunt group and save as unique queries. You will see the quey names I used in the next SQL. You can then combine all four of the 'current' queries into one as follows :

SELECT Vinny_Current_Dept.Name, Vinny_Current_Dept.DateDept, Vinny_Current_Dept.Department, Vinny_Current_Ext.DateExt, Vinny_Current_Ext.Extension, Vinny_Current_HG.DateHG, Vinny_Current_HG.Hunt_Group, Vinny_Current_Loc.DateLoc, Vinny_Current_Loc.Location
FROM ((Vinny_Current_Dept INNER JOIN Vinny_Current_Ext ON Vinny_Current_Dept.Name = Vinny_Current_Ext.Name) INNER JOIN Vinny_Current_HG ON Vinny_Current_Ext.Name = Vinny_Current_HG.Name) INNER JOIN Vinny_Current_Loc ON Vinny_Current_HG.Name = Vinny_Current_Loc.Name;

This last query will give the current data for each person and you can then prepare your 3 queries per your earlier post.

HTH, Andrew.

 

[Bubbis Thedog]

Crikey, Andrew! You did it! I honestly didn't think that could done. I suspected that the Max of each parameter would need to be found, but couldn't figure out how to set it up. I was afraid that I'd need to completely re-design the database. Thanks a lot for going through the trouble (though I suspect you dig the challenge :wink: ), Andrew.

Speaking of the database design, it's structure is literally as I've posted:

One child table (the log table, or MAIN):

_______________________
PK           |  Number
Name         |  Text
Ext          |  Number
Location     |  Text
Action       |  Text
Date         |  Date
Hunt_Group   |  Number
Department   |  Text

...and five parent static tables (I'll list only one structure because they're all the same essentially):

_____________________________

Loc_ID       |  Number
Location     |  Text

There is a one-to-many relationship between each of the five parent tables and the child table. Referential integrity is enabled. I was just wanting to know if there was a more efficient way to set up a log like this?

Andrew, thanks again for your time and patience, friend. I'm learning so much from you about this stuff, it ain't even funny...

 

[Andrew Fergus]

Hi Vinny
It's easy once we understand what is going on. BTW the last query was designed in such a way that you can use it for many different things / enquiries. Although I don't know how well it will perform when you have a lot more data in your table. In response to this :

I was afraid that I'd need to completely re-design the database.

You may yet have to re-design it my friend, depending on your next question!
Good luck
Andrew.

 

[Bubbis Thedog]

You rock, Andrew. Thanks so much for the help. I'll continue to use your methods and stick with my current setup. You da bomb, man!

Thanks.

 

[Norie]

I've been thinking about this.

Where is the unique ID for the person? It should be being used as a foreign key in the table.

 

[Bubbis Thedog]

Hi, norie. Actually, the Name, Location, Extension, Hunt Group, Action, and Department field are the foreign keys of the MAIN table --well, essentially at least. I'll explain what I did for, let's say, the Name field (I got all of the following from the Northwind DB example):

Under the General tab in Design view, I have the caption set as "Name."

Under the Lookup tab in Design view, I have

SELECT EMP_ID, EMP_FIRST & " " & EMP_LAST AS [Name]
FROM EMPLOYEES 
ORDER BY EMP_FIRST & " " & EMP_LAST;

entered as the Row Source. Then I set Bound Column to 1, Column Count to 2, and Column Width to 0".

So Name is just an alias. I did this for all six of the aforementioned fields. The reason I did this was because I designed a form for entering information into the MAIN table, and wanted drop down lists. Once again, I got nearly all of the methods from the sample Northwind DB.

Of course, it's turning out that, as you can see by the complexity of Andrew's solution, I could perhaps have designed the whole thing better. But how? hahahaha

Thanks for the inquiry, norie!