Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-31-2019, 04:28 AM   #1
georg7
Newly Registered User
 
Join Date: Sep 2019
Posts: 19
Thanks: 10
Thanked 0 Times in 0 Posts
georg7 is on a distinguished road
Latest Date per product

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
Attached Images
File Type: jpg aaa.JPG (19.8 KB, 12 views)

georg7 is offline   Reply With Quote
Old 10-31-2019, 04:31 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,951
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Latest Date per product

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
georg7 (10-31-2019)
Old 10-31-2019, 04:34 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,330
Thanks: 40
Thanked 3,669 Times in 3,537 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Latest Date per product

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
georg7 (10-31-2019)
Old 10-31-2019, 04:34 AM   #4
georg7
Newly Registered User
 
Join Date: Sep 2019
Posts: 19
Thanks: 10
Thanked 0 Times in 0 Posts
georg7 is on a distinguished road
Re: Latest Date per product

It works perfect! Thank you for your fast answer!

Georg
georg7 is offline   Reply With Quote
Old 10-31-2019, 04:47 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,951
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Latest Date per product

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-31-2019, 05:22 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,223
Thanks: 92
Thanked 2,023 Times in 1,971 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Latest Date per product

Further to the responses received so far, what exactly identifies a product?
Your
Quote:
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is online now   Reply With Quote
Old 10-31-2019, 05:25 AM   #7
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,391
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Latest Date per product

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


plog is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] TSQL - case - Latest date for 5 Date variables - Identifgy what variable had latest Rx_ SQL Server 6 01-13-2015 08:40 AM
Deteremine latest date, with NULL's being the latest gwunta1907 Queries 3 06-21-2012 04:20 AM
Latest date tezread Queries 3 06-16-2012 03:40 AM
Same product, different date Kalthar Queries 1 08-26-2010 08:25 AM
Find Latest Date before Current Date sharonbl Modules & VBA 1 12-11-2000 07:03 AM




All times are GMT -8. The time now is 08:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World