Latest Date per product (1 Viewer)

georg7

Registered User.
Local time
Today, 08:50
Joined
Sep 3, 2019
Messages
84
HI guys,
I want a query where I can see the product and the latest date. Is this possible? Can someone help ?


Best regards
Georg
 

Attachments

  • aaa.JPG
    aaa.JPG
    19.8 KB · Views: 58

isladogs

MVP / VIP
Local time
Today, 15:50
Joined
Jan 14, 2017
Messages
18,186
Create a query and add Feld1 and Feld2.
Click the totals button on the ribbon to make it an aggregate query.
Both fields will now show Group By in the Totals row.
Change that to Max for your Feld2.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:50
Joined
Feb 19, 2013
Messages
16,553
1. create an aggregate query - call it say qryLatest

Code:
SELECT FELD1, Max(Feld2) as Latest
FROM Tabelle1
GROUP BY FELD1

2. Create a second query

Code:
SELECT Tabelle1.*
FROM Tabelle1 INNER JOIN qryLatest ON Tabelle1.FELD1=qryLatest.FELD1 AND Tabelle1.FELD2=qryLatest.Latest
 

georg7

Registered User.
Local time
Today, 08:50
Joined
Sep 3, 2019
Messages
84
It works perfect! Thank you for your fast answer!

Georg
 

isladogs

MVP / VIP
Local time
Today, 15:50
Joined
Jan 14, 2017
Messages
18,186
Note that You only need the second query suggested by CJL if you want to include the ID field.
If not, just use the aggregate query.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:50
Joined
Jan 23, 2006
Messages
15,364
Further to the responses received so far, what exactly identifies a product?
Your
the product and the latest date
is not quite enough info.
Often helpful if you include a sample of your desired output.
Good luck with your project.
 

plog

Banishment Pending
Local time
Today, 10:50
Joined
May 11, 2011
Messages
11,611
I'm not sure it did work. Is Field2 an actual Date/Time field? or is it a text field with just a string that represents a date?

What happens when you use this sample data:

ID, Field1, Field2
1, Pen, 10.01.2018
2, Pen, 01.01.2019
 

Users who are viewing this thread

Top Bottom