?
Solved

vba Query to get the last record of each day in table.

Posted on 2013-01-29
5
Medium Priority
?
546 Views
Last Modified: 2013-01-29
Hello all

I have a table that I need to have a query to get the last record for each day.

For sake of this example, I will keep it simple.

Let us call the table [dta]

Let us assume the fields are.
[ID]
[Info1]
[Info2]
[Info3]
[Date]

Now then I know how to use Dmax("ID","dta","Date") To get the last record.  However I need that Last record for each and every day...????????
0
Comment
Question by:wlwebb
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 38833573
Select max(ID), [Date] from dta group by [date]
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 600 total points
ID: 38833577
Select ID, info1, info2, info3, [date] from dta where id in(Select max(ID) from dta group by [date])
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 1400 total points
ID: 38833585
I'm not clear whether you want:

a) The record with the highest [ID] value for each date (where the [Date] field contains only a date with no time part), OR

b) The record with the highest date/time value in the [Date] field for each date ONLY in the [Date] field (where the [Date] field contains BOTH a date part AND a time part)

I assume that [ID] contains a unique value.

This query should satisfy case (a) above:

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


This query should satisfy case (b):

SELECT * FROM dta 
    WHERE [ID]=(SELECT TOP 1 [ID] FROM dta as X 
         WHERE DateValue(X.[Date])=DateValue(dta.[Date]) 
         ORDER BY [Date] desc);

Open in new window

0
 

Author Comment

by:wlwebb
ID: 38833593
Thank you!
0
 

Author Closing Comment

by:wlwebb
ID: 38833613
GrahamMandeno THANK YOU for thinking past the original limited question!!!!!!! Much appreciated.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

850 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