Creating a Query to find the MAX DATE for a Given set of Records

[SealM]

I'm still working on my first database project, and have learned so much during this process. Now I'm stuck at what seems to me, a simple query, but how to do this escapes me.

I have created a database (with SSnafu's help, that is!) that tracks assets (aircards, in this case), when they're checked out and back in, and to whom.

Here are the things I want the query to do:
1. Given an inventory number of an aircard (aircards.inventory_number),
2. Find the records for aircard actions (action_list.action_list) that begin with "check*" (this covers records that are "checked out to" and "checked in to"
3. AND returns the ONE record with the max date (aircard_actions.action_date) from #2.

Overall, what I want is the userid (employees.userid) that was the most recent person a card was checked in/out to, so I can display it in a read-only box on the form with the other aircard data. This way when going through the aircards, you can see instantly who it belongs to at the present time.

The query I've made will drill down somewhat, but I cannot seem to get it to show ONLY the latest date. And for this instance, I've hard-coded the criteria of "m100" as the inventory number so I'm only looking at 1 card. That would be removed later. Here's what it looks like:



Or, the code:

 SELECT Aircards.inventory_number, action_list.action_list, Aircard_Actions.action_date, Employees.userid
FROM Employees INNER JOIN (action_list INNER JOIN (Aircards INNER JOIN Aircard_Actions ON Aircards.Aircard_PK = Aircard_Actions.AircardId_FK) ON action_list.ActionID_PK = Aircard_Actions.action_FK) ON Employees.EmployeeId_PK = Aircard_Actions.UserID_FK
GROUP BY Aircards.inventory_number, action_list.action_list, Aircard_Actions.action_date, Employees.userid
HAVING (((Aircards.inventory_number)="m100") AND ((action_list.action_list) Like ("che*")));

And this gives me exactly what I want it to, but I only want the MAX date, 1 record, and I'm getting all the records. I've tried using MAX(date) everywhere, but I always get an aggregate error or operand error. This is what I get presently with this code:




So, can anyone tell me how to get the ONE record from the above picture and where or what I would use "max(date)" or if there's something else I should know about. I would appreciate it greatly!
Regards,


Mike

[pbaldy]

Is this what you want?

http://www.baldyweb.com/LastValue.htm

[June7]

You want the latest record for each aircard? Try TOP subquery. Review: http://allenbrowne.com/subquery-01.html

[SealM]

Thanks for the replies. I see that a subquery is definitely the way to go at this point. I also tried seperating this into two queries, but I get unpredictable results there, and I don't know where to even begin troubleshooting that area. I'm sure a subquery can accomplish this task, but I'm at a loss for how to accomplish it. I have made a few successful subqueries in SQL from scratch now on this data, but they still don't return a SINGLE value of the TOP 1 record for date by inventory number. I'm attaching the database itself, if someone wants to take a look at the QUERY1 and tell/show how to do the following:

1. Return the inventory numbers where the action_list is either 'check out to' or 'check back in' (this I've done in query1 already)
2. show ONLY the record from this list with the LATEST date, be in 'check out to' or check back in', for each inventory number.
3. Show the userid's for these records as well, as this is the field I need. The whole point of this query is to show the most recent 'check' action for each card, enabling me to show who has ownership of it.

If you run the query1 in this database, you'll see what I mean....
Thanks in advance to anyone who wants to try this one!
Mike
aircard1.zip

[pbaldy]

Do these work?

qryPaulBase:

SELECT Aircard_Actions.AircardId_FK, Max(Aircard_Actions.action_date) AS MaxOfaction_date
FROM Aircard_Actions
WHERE Aircard_Actions.action_FK In(2,3)
GROUP BY Aircard_Actions.AircardId_FK

qryPaul:

SELECT Aircard_Actions.AircardActionID_PK, Aircard_Actions.AircardId_FK, Aircard_Actions.action_FK, Aircard_Actions.UserID_FK, Aircard_Actions.comment, Aircard_Actions.action_date
FROM Aircard_Actions INNER JOIN qryPaulBase ON (Aircard_Actions.action_date = qryPaulBase.MaxOfaction_date) AND (Aircard_Actions.AircardId_FK = qryPaulBase.AircardId_FK);

[June7]

Query2: finds the max action_date for each aircard, limited to check out and check in actions:
SELECT AircardId_FK, Max(action_date) AS MaxOfaction_date
FROM Aircard_Actions
WHERE action_FK=2 Or action_FK=3
GROUP BY AircardId_FK;

Query1: returns record that has the max action_date, include whatever fields you want from the joined tables:
SELECT Aircards.inventory_number, action_list.action_list, Aircard_Actions.action_date, Employees.userid
FROM Query2 INNER JOIN (Employees INNER JOIN (Aircards INNER JOIN (action_list INNER JOIN Aircard_Actions ON action_list.ActionID_PK = Aircard_Actions.action_FK) ON Aircards.Aircard_PK = Aircard_Actions.AircardId_FK) ON Employees.EmployeeId_PK = Aircard_Actions.UserID_FK) ON Query2.inventory_number = Aircards.inventory_number
WHERE (((Aircard_Actions.action_date)=[MaxOfaction_date]));

Single nested query:
SELECT Aircards.inventory_number, action_list.action_list, Aircard_Actions.action_date, Employees.userid FROM (SELECT Aircards.inventory_number, Max(Aircard_Actions.action_date) AS MaxOfaction_date FROM Aircards INNER JOIN Aircard_Actions ON Aircards.Aircard_PK = Aircard_Actions.AircardId_FK WHERE (((Aircard_Actions.action_FK)=2 Or (Aircard_Actions.action_FK)=3)) GROUP BY Aircards.inventory_number) As Query2 INNER JOIN (Employees INNER JOIN (Aircards INNER JOIN (action_list INNER JOIN Aircard_Actions ON action_list.ActionID_PK = Aircard_Actions.action_FK) ON Aircards.Aircard_PK = Aircard_Actions.AircardId_FK) ON Employees.EmployeeId_PK = Aircard_Actions.UserID_FK) ON Query2.inventory_number = Aircards.inventory_number WHERE (((Aircard_Actions.action_date)=[MaxOfaction_date]));

[SealM]

Yes. They do exactly what I wanted. The idea that you did that in probably 1-2 minutes FLOORS me. I have much to learn. =)

Thank you!!

[pbaldy]

It's basically what was on my website with the additional criteria on the action. It can certainly be a subquery, but I think most people find it more cumbersome to deal with it that way.