UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Filter A Query By Most Recent Date, Access 2013    
 
   
Mastrepolo
post Dec 12 2017, 08:37 PM
Post#1



Posts: 10
Joined: 10-October 17



Hello,

I have a query with three fields: Release #, Step, Date. The release # has multiple steps in it and each step has a date associated to it. I want to only show the most recent date on the query not all the steps before. An example query looks like this:

What I see:

Release# Step Date
P51991+01 1: WG Prescreen 1/4/2016
P51991+01 0: Feature Owner 1/4/2016
P51991+01 1: WG Prescreen 1/20/2016
P51991+01 0: Feature Owner 1/25/2016
P51991+01 1: WG Prescreen 2/2/2016
P51991+01 0: Feature Owner 2/9/2016
P52822+01 WDG Hold - Other 3/10/2016
P52822+01 3: WDG Checker 3/14/2016
P52822+01 3b: WDG Designer 3/14/2016
P52822+01 3: WDG Checker 3/14/2016
P52822+01 3b: WDG Designer 3/15/2016
P52822+01 4: Feature Owner 3/15/2016
P52822+01 WDG Hold - Other 3/16/2016

What I am trying to do:
Release# Step Date
P51991+01 0: Feature Owner 2/9/2016
P52822+01 WDG Hold - Other 3/16/2016

Sorry for bad formatting. I was trying to do it through criteria but that doesn't seem top work. Any help would be appreciated thank you.

Polo
This post has been edited by Mastrepolo: Dec 12 2017, 08:44 PM
Go to the top of the page
 
theDBguy
post Dec 12 2017, 08:47 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,488
Joined: 19-June 07
From: SunnySandyEggo


Hi Polo,

Without a primary key field, you may get duplicate records but try something like:

SELECT T1.* FROM TableName T1
INNER JOIN (SELECT T2.[Release], Max(T2.[Date]) As LastDate
FROM TableName T2
GROUP BY T2.[Release]) SQ
ON T1.[Release]=SQ.[Release]
AND T1.[Date]=SQ.[LastDate]

(untested)
Hope it helps...
Go to the top of the page
 
WildBird
post Dec 13 2017, 01:10 AM
Post#3


UtterAccess VIP
Posts: 3,418
Joined: 19-August 03
From: Auckland, Little Australia


Could also use "top 1" and sort by date? If you use Max() and there are multiples of the dates (if it is date only, not date and time more chance of it) then it might return multiple records.
Go to the top of the page
 
Mastrepolo
post Dec 15 2017, 02:40 PM
Post#4



Posts: 10
Joined: 10-October 17



Hey DB,

Sorry about the late reply, I have a ID field as the primary key in this query that starts at 1. Attached is what I see right now. I tried your SQL but I think I inputted it wrong. This is the SQL I currently have that gives me the picture that is attached.

SELECT Release.Release, [Packet Tracking].Step, [Packet Tracking].Date, [Packet Tracking].ID
FROM [Packet Tracking] INNER JOIN Release ON [Packet Tracking].Release = Release.Release;

What was T1 and T2 on your SQL code? I am a bit stuck so any help is appreciated. Thank you.
Attached File(s)
Attached File  Duplicates.PNG ( 44.69K )Number of downloads: 0
 
Go to the top of the page
 
Mastrepolo
post Dec 15 2017, 04:40 PM
Post#5



Posts: 10
Joined: 10-October 17



Thank you for your suggestion but this still returned the same information.
Go to the top of the page
 
RJD
post Dec 15 2017, 06:51 PM
Post#6


UtterAccess VIP
Posts: 8,925
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but could you post the table you are working with (in a db, zipped)? Some of us like to test things since you are having some trouble with it. And having the table saves us having to create a table and enter the data to test (in this case a bit laborious).

This is all do-able, and with some tweaking it should give the results you want.

Thanks,
Joe
Go to the top of the page
 
theDBguy
post Dec 17 2017, 09:12 PM
Post#7


Access Wiki and Forums Moderator
Posts: 73,488
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Sorry for the confusion... Try replacing "TableName" in the SQL I gave you with the name of your current query.

Hope it helps...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 10:43 AM