?
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
?
412 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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 describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …
Suggested Courses

801 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