Latest Date Query (1 Viewer)

SiGill

Registered User.
Local time
Today, 05:06
Joined
Dec 22, 2008
Messages
72
How can I get the latest date and the relevant columns in an access query.
My Data is in a similar format to this.

Number Supplier Country ExpiryDate A B C D E F G
4049929726987 W RSA SA 19-Jul-19 No No No No No No No
4049929726987 W RSA SA 19-Mar-19 Yes Yes Yes Yes Yes No Yes
4049929726987 W RSA SA 12-Mar-19 Yes Yes Yes Yes Yes No Yes
4049929726987 W RSA SA 05-Mar-19 Yes Yes Yes Yes Yes No Yes
4049929726987 W RSA SA 26-Feb-19 Yes Yes Yes Yes Yes No Yes
4049929726987 W RSA SA 19-Feb-19 Yes Yes Yes Yes Yes No Yes
4049929726987 W RSA SA 12-Feb-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 19-Jul-19 No No No No No No No
4049925626325 W RSA SA 19-Mar-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 12-Mar-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 05-Mar-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 26-Feb-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 19-Feb-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 12-Feb-19 Yes Yes Yes Yes Yes No Yes

But when I run my query it gives me this as a result.
Number Supplier Country ExpiryDate A B C D E F G
4049929726987 W RSA SA 19-Jul-19 No No No No No No No
4049929726987 W RSA SA 19-Mar-19 Yes Yes Yes Yes Yes No Yes
4049925626325 W RSA SA 19-Jul-19 No No No No No No No
4049925626325 W RSA SA 19-Mar-19 Yes Yes Yes Yes Yes No Yes

But I only want the 19 July rows. How can I go about this, I have tried putting the expiry date to Max and the A, B, C, etc columns to Last but that didn't work because it was giving the last references for a different row.

Any help would be appreciated
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:06
Joined
Jan 23, 2006
Messages
15,364
Show us the query SQL you re using.

Please clarify the requirement:

1--How can I get the latest date
2--But I only want the 19 July rows
 

SiGill

Registered User.
Local time
Today, 05:06
Joined
Dec 22, 2008
Messages
72
In the first column there are numbers, 4049929726987 and 4049925626325, I want to get the latest entry for those numbers and the corresponding, A, B, C, etc columns

SQL in my Access Query is

SELECT Number, Supplier, Country, Max(ExpiryDate), A, B, C, D, E, F, G
FROM tbl_SupplierNumber
GROUP BY Number, Supplier, A, B, C, D, E, F, G
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:06
Joined
Jan 23, 2006
Messages
15,364
Try
Code:
select * from tbl_SupplierNumber where
expirydate =  (SELECT  Max(ExpiryDate)
FROM tbl_SupplierNumber  )
 

Users who are viewing this thread

Top Bottom