Select last record meeting conditions

[Lol999]

Hi, I have some sql below which works great in itself, but it is not for want of a better word filtering the data as I require.

For example, say I have the following records below returned by the query:

Drill 1| Yard | 25/3/18
Drill 2 | Van | 18/3/18
Drill 3 | Van | 17/3/18
Drill 4 | Yard | 16/3/18
Ladders 1 | Yard | 22/3/18
Ladders 2 | Site | 21/3/18
Grinder 1 | Yard | 19/3/18

What I would like to do is for the query to look at the records and for each item i.e Drill1, Drill 2 etc and if the most recent date has a location matches a criteria in a combobox, i.e. Yard, then to return that record otherwise to ignore it.

I've looked at examples for MAX and TOP but they don't make a deal of sense.
Could someone look at my sql below please and see if it's feasible?

strSQL1 = "SELECT Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Current_Location.Comments, Tbl_Current_Location.User, Tbl_Current_Location.Date_Loaned " _
& "FROM Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product " _
& "Where Tbl_Current_Location.ID_Location_Type= " & Me.cboLocationCategory & ";"

Many thanks, Lol

 

[Ranman256]

what do you mean LAST?
last record in the list sorted that way?
latest date record?

if date, then you can use MAX, but it takes 2 queries and you only want 2 fields
Q1 = Max(Date), KeyFld
(you want the latest date for the key...Drill?)

Then Q2, join to Q1 and pull ALL records,
select *.table from table,Q1 where Q1.Key = table.key


in your example you want 3 fields?
Q1 = Max(Date), Tool, Place

to get the latest date of 1 tool, in 1 place???

 

[Lol999]

Okay, each item, be it Drill 1, Drill 2 etc will have a list of dates and locations.
What I am trying to find for each item is if the most recent date and it's corresponding location match the location I enter via a combobox.

 

[arnelgp]

you can use sub query:

strSQL1 = "SELECT tbl_Product.Part_No, tbl_Product.Details, (SELECT TOP 1 Comments FROM tbl_Current_Location WHERE tbl_Current_Location.ID_Product = tbl_Product.ID_Product AND tbl_Current_Location.ID_Location_Type=" & Me.cboLocationCategory & " ORDER BY Date_Loaned DESC) As Comments, (SELECT TOP 1 User FROM tbl_Current_Location WHERE tbl_Current_Location.ID_Product = tbl_Product.ID_Product AND tbl_Current_Location.ID_Location_Type=" & Me.cboLocationCategory & " ORDER BY Date_Loaned DESC) As User, (SELECT TOP 1 Date_Loaned FROM tbl_Current_Location WHERE tbl_Current_Location.ID_Product = tbl_Product.ID_Product AND tbl_Current_Location.ID_Location_Type=" & Me.cboLocationCategory & " ORDER BY Date_Loaned DESC) As Date_Loaned FROM tbl_Product;"

 

[pbaldy]

Another way of describing what ranman is suggesting:

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

 

[Lol999]

Thank you Arnel and PB for the solutions. I'll look at them tomorrow and see what I can make of them (i.e. understand)

Many thanks, Lol

 

[Lol999]

Hi, Arnel, sorry but your code returned every item in the table Current_Location.

Pbaldy - I tried your approach with the query and subsequent query but I keep getting asked for a value for qryMaxDate.ID_Product.

Here is the qryMaxDate

SELECT Max (Tbl_Current_Location.Date_Loaned) AS MaxDate, Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Current_Location.Comments, Tbl_Current_Location.User
FROM Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product
GROUP BY Tbl_Current_Location.Comments, Tbl_Current_Location.ID_Product, Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Current_Location.User;

And here is the corresponding string for the query run off qryMaxDate

"SELECT Tbl_Current_Location.* " _
& "FROM Tbl_Current_Location INNER JOIN qryMaxDate " _
& "ON (Tbl_Current_Location.ID_Product= qryMaxDate.ID_Product) " _
& "AND (Tbl_Current_Location.Date_Loaned = qryMaxDate.MaxDate) " _
& "Where Tbl_Current_Location.ID_Location_Type= " & Me.cboLocationCategory & ";"

many thanks, Lol

 

[arnelgp]

It will not return all checj my post again specially SELECT TOP 1, and the ORDER BY.

 

[Lol999]

Arnelgp please accept my apologies I didn't fully read your post above.

The query, or Q1 as you refer to it is as below which pulls all the data I need for the report before finding the desired record and is called qryTempLocationType:

  [FONT=Calibri]SELECT Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Current_Location.Comments, Tbl_Current_Location.User, Tbl_Current_Location.Date_Loaned[/FONT]
  [FONT=&quot]FROM Tbl_ToolCategory INNER JOIN (Tbl_Product INNER JOIN Tbl_Current_Location ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_ToolCategory.ID_Tool_Category = Tbl_Product.ID_Tool_Category;[/FONT]

Unfortunately I have NO idea how to integrate the query, Q2 you kindly provided!

This is SQL WAY beyond my capabilities.

Many thanks, Lol

 

[Lol999]

Okay, I was approaching this from the wrong angle last time. What I need to do is check if for each item their last date of being booked had a location equal to a certain criteria.

I have created a query, qryMaxDate which finds the last booking for each product.
I have then linked this to another query, qryLastLocation and am trying to find the items for which their last booking location meets certain criteria, i.e. location type.

The first query appears to bee working correctly but I am struggling with the second and getting a fair amount of record duplication.
Could someone steer me right please?
Queries below:
qryMaxDate

SELECT Max(Tbl_Current_Location.Date_Loaned) AS MaxOfDate_Loaned, Tbl_Product.Part_No, Tbl_Product.Details
FROM Tbl_Product INNER JOIN (Tbl_Location_Type INNER JOIN Tbl_Current_Location ON Tbl_Location_Type.ID_LocationType = Tbl_Current_Location.ID_Location_Type) ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product
GROUP BY Tbl_Product.Part_No, Tbl_Product.Details;

qryLastLocation

SELECT Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Current_Location.Date_Loaned, Tbl_Current_Location.Comments, Tbl_Current_Location.User, Tbl_ToolCategory.Category, Tbl_Current_Location.ID_Location_Type
FROM Tbl_ToolCategory INNER JOIN (Tbl_Product INNER JOIN (Tbl_Location_Type INNER JOIN (qryMaxDate INNER JOIN Tbl_Current_Location ON qryMaxDate.MaxOfDate_Loaned = Tbl_Current_Location.Date_Loaned) ON Tbl_Location_Type.ID_LocationType = Tbl_Current_Location.ID_Location_Type) ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_ToolCategory.ID_Tool_Category = Tbl_Product.ID_Tool_Category
WHERE (((Tbl_Current_Location.ID_Location_Type)=2));

 

[pbaldy]

Is it appropriate for the Details field to be in the first query? You aren't getting "the last booking for each product", you're getting the last booking for each product/detail combination.

 

[Lol999]

You're right, I've taken that out and now have:

SELECT Max(Tbl_Current_Location.Date_Loaned) AS MaxOfDate_Loaned, Tbl_Product.Part_No
FROM Tbl_Product INNER JOIN (Tbl_Location_Type INNER JOIN Tbl_Current_Location ON Tbl_Location_Type.ID_LocationType = Tbl_Current_Location.ID_Location_Type) ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product
GROUP BY Tbl_Product.Part_No;

 

[Lol999]

Solved it. I was over complicating the first query , qryMaxDate and now have this:
[CODESELECT Max(Tbl_Current_Location.Date_Loaned) AS MaxOfDate_Loaned, Tbl_Current_Location.ID_Product
FROM Tbl_Current_Location
GROUP BY Tbl_Current_Location.ID_Product;
][/CODE]

I was also linking on the incorrect field between qryTempLocation and qryMaxDate, which now gives me:

SELECT Tbl_Product.Part_No, Tbl_Product.Details, Tbl_Current_Location.Date_Loaned, Tbl_Current_Location.Comments, Tbl_Current_Location.User, Tbl_ToolCategory.Category, Tbl_Current_Location.ID_Location_Type
FROM Tbl_ToolCategory INNER JOIN (Tbl_Product INNER JOIN (Tbl_Location_Type INNER JOIN (qryMaxDate INNER JOIN Tbl_Current_Location ON (qryMaxDate.ID_Product = Tbl_Current_Location.ID_Product) AND (qryMaxDate.MaxOfDate_Loaned = Tbl_Current_Location.Date_Loaned)) ON Tbl_Location_Type.ID_LocationType = Tbl_Current_Location.ID_Location_Type) ON Tbl_Product.ID_Product = Tbl_Current_Location.ID_Product) ON Tbl_ToolCategory.ID_Tool_Category = Tbl_Product.ID_Tool_Category
WHERE (((Tbl_Current_Location.ID_Location_Type)=2));

Well pleased, thanks for the help

 

[pbaldy]

Glad you sorted it. I was thinking you didn't need the product table in the first query.

 

[Lol999]

I'm just on a very steep learning curve, seemingly all the time:banghead: