Solved

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

Posted on 2013-01-29
5
526 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
[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
  • 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 150 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 350 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

733 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