vba Query to get the last record of each day in table with a WHERE Foreign Key also Exits in Where Statement

Hello All!
This is a followup question to my question on getting each last record for every date (Short Date)

The original question was:
Previous Question to get just the Last Record of Each Day

Now then if I modify the facts to be:

Table [dta]

and Fields
[ID] - primary key autonumber
[Fk1] - Foreign Key
[Info1]
[Info2]
[Info3]
[Info4]
[Void] - Y/N default 0
[Date]

Now then I want to get the last record for every date where the Fk1 =3 and the [Void]=0

The answer to the prior question was:
SELECT * FROM dta 
    WHERE [ID]=(SELECT TOP 1 [ID] FROM dta as X 
         WHERE X.[Date]=dta.[Date] 
         ORDER BY [ID] desc);

Open in new window


SO IN MY INEPT ABILITY I TRIED MODIFYING TO:
SELECT * FROM dta 
WHERE [FK1]=3 AND [Void]=0
    WHERE [ID]=(SELECT TOP 1 [ID] FROM dta as X 
         WHERE X.[Date]=dta.[Date] 
         ORDER BY [ID] desc);

Open in new window


It did NOT like me tampering with that code ;-))
wlwebbAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Try:

SELECT t1.*
FROM dta t1 INNER JOIN
    (SELECT Max(t2.ID) AS MaxID, t2.[Date]
    FROM dta t2
    WHERE t2.Fk1 = 3 And t2.Void = 0
    GROUP BY t2.[Date]) AS x ON t1.ID = x.MaxID
ORDER BY t1.[Date]

Open in new window

0
 
wlwebbAuthor Commented:
Well................. that works but all of that has me lost!!!!!  I've tried and tried to understand writing those but they completely escape me :-(
0
 
Patrick MatthewsCommented:
It works by creating a derived table that holds the maximum ID value for each date and the other filter criteria you specified:

    (SELECT Max(t2.ID) AS MaxID, t2.[Date]
    FROM dta t2
    WHERE t2.Fk1 = 3 And t2.Void = 0
    GROUP BY t2.[Date]) AS x

Open in new window


By joining that derived table to the original table, you get only those records whose ID values match the highest ID value for each date.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.