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
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

730 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