Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
416 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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 93

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

604 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