Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 8 votes, 5.00 average. Display Modes
Old 08-23-2011, 11:01 AM   #1
sharpnova
Newly Registered User
 
Join Date: Jun 2011
Posts: 69
Thanks: 8
Thanked 0 Times in 0 Posts
sharpnova is on a distinguished road
How do I return record with earliest date?

I've googled this and found several explanations on how to do this. But none of them seem to deal with the case where there are multiple fields.

Example:

I have a table with a dozen columns. One of them is a date field. One of them is a transaction ID.

I want to return a record for each transaction ID with the earliest date field.

Logically, I would think I would do "group by" on the transaction ID then "min" on the date field.

This doesn't work, and I imagine it's because when I turn on the group by all of the fields have "group by" applied to them.

I really don't understand how the group by feature works in access design view.

I want the first record for each transaction ID. first meaning first date in the date field.

sharpnova is offline   Reply With Quote
Old 08-23-2011, 11:35 AM   #2
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,464
Thanks: 37
Thanked 77 Times in 73 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: How do I return record with earliest date?

you have to select only those field you want to group. if you want more records in the end result, use the group by query to select the other fields.
Guus2005 is offline   Reply With Quote
Old 08-23-2011, 12:05 PM   #3
sharpnova
Newly Registered User
 
Join Date: Jun 2011
Posts: 69
Thanks: 8
Thanked 0 Times in 0 Posts
sharpnova is on a distinguished road
Re: How do I return record with earliest date?

I'm sorry that made no sense to me at all.

I have a bunch of fields. Two of which are transactionID and transactionDate

I want to return one record for every unique transactionID and that record be selected by being the one with the earliest transactionDate

In Access query design view, I turned on the group by functionality.

It immediately applied Group By to ALL fields. I then selected Min for the date field and the results I got were garbage since I had many with the same transactionID.

I then went into SQL view and tried to just put GROUP BY transactionID at the end and in the first part of the select statement, i changed transactionDate to Min(transactionDate) AS minOfTransactionDate

These changes to the sql equated to making all the other fields have a group by option of "expression" which stops the query from running at all. It gives some error about the function not existing or whatever.

sharpnova is offline   Reply With Quote
Old 08-23-2011, 12:43 PM   #4
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,689
Thanks: 39
Thanked 535 Times in 517 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: How do I return record with earliest date?

This requires two. Queries, what is happening to you is that the query is grouping on all fields and therefore the differences in those fields, so
Query1 just select transactionid and date fields group by transactionid and min on date field.
This gives the min date for each transactionid so now quer u2 takes in the table and query1 joined on transactionid and date fields and you can select any other data that you require.
Note that you just run query2 the system automatically runs query1

Brian
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 08-23-2011, 12:48 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,714
Thanks: 8
Thanked 3,632 Times in 3,575 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: How do I return record with earliest date?

Here's a visual if it helps:

http://www.baldyweb.com/LastValue.htm
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 08-23-2011, 12:54 PM   #6
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,689
Thanks: 39
Thanked 535 Times in 517 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: How do I return record with earliest date?

They say a pictures worth a thousand words and sure makes it clear, infact I think a bald guy with penchant for lollipops sang a song about that , not related I suppose

Brian
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 08-23-2011, 02:47 PM   #7
sharpnova
Newly Registered User
 
Join Date: Jun 2011
Posts: 69
Thanks: 8
Thanked 0 Times in 0 Posts
sharpnova is on a distinguished road
Re: How do I return record with earliest date?

Thanks for the help. It's clear now.

sharpnova is offline   Reply With Quote
Old 08-23-2011, 11:31 PM   #8
Guus2005
AWF VIP
 
Guus2005's Avatar
 
Join Date: Jun 2007
Location: The Netherlands
Posts: 2,464
Thanks: 37
Thanked 77 Times in 73 Posts
Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about Guus2005 has a spectacular aura about
Re: How do I return record with earliest date?

Quote:
Originally Posted by pbaldy View Post
Here's a visual if it helps:

http://www.baldyweb.com/LastValue.htm
Yes, that's what i meant.

create a query to get the records you want. because of the group by clause you can't get all the fields that you want to see.
so, that's why two queries are needed to produce the results you want to see.

Yes, you can put it all in one query but that query will be less readable and less maintainable.

enjoy!

Guus2005 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Display earliest date from query on form jt196 Forms 6 04-06-2011 05:18 AM
Calculating the earliest date using an update query Rod Queries 2 08-06-2010 03:27 AM
Return Earliest Date OxDavis Queries 10 09-09-2005 08:14 AM
Earliest Date wakiwi Reports 1 03-14-2005 04:05 AM
How to show earliest start date on form russi Forms 3 03-05-2004 06:50 AM




All times are GMT -8. The time now is 09:41 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World