Solved

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

Posted on 2013-01-29
3
401 Views
Last Modified: 2013-01-30
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 ;-))
0
Comment
Question by:wlwebb
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38833647
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
 

Author Closing Comment

by:wlwebb
ID: 38833665
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38834878
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now