By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

Most Recent Dated Record, perhaps I am not seeing it correctly.

P: n/a
Experts,
I have the following scenario:

Table1: RecordID RecordDate ...

I want in a query to show me within this RecordID, only the latest
RecordDate and info. I know I can use the Max command but am not
familiar with the concept behind it, how to use it. i.e. if I pick
Max(RecordDate) in the query column, it still shows all records. If I
combine it with RecordID, same... what should I have done?

Thanks in advance,
Perry

Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
In the QueryBuilder window, pull down the Fields you want to see. Click on
Record Date and set Sort in Descending order. Then right-click in the upper
area, but not over a table or a join lines, and from Query Properties Select
Top 1.

Larry Linson
Microsoft Access MVP
<pe******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Experts,
I have the following scenario:

Table1: RecordID RecordDate ...

I want in a query to show me within this RecordID, only the latest
RecordDate and info. I know I can use the Max command but am not
familiar with the concept behind it, how to use it. i.e. if I pick
Max(RecordDate) in the query column, it still shows all records. If I
combine it with RecordID, same... what should I have done?

Thanks in advance,
Perry

Nov 13 '05 #2

P: n/a
Would this show only 1 record? What I want is within the same Record#
(in the same table), there are multiple dates, I want only to show
latest date record. Therefore, I might have:

ID Date
Rc1: 101 09/25/05 (excluded Rc1 9/1/05)
Rc2: 105 10/22/05 (excluded Rc2 09/22/05, 08/14/04...)
Rc3: 111 04/25/04...

I hope this is clearer.

Perry

Nov 13 '05 #3

P: n/a
Larry said:
In the QueryBuilder window, pull down the Fields >>you want to see. Click on
Record Date and set Sort in Descending order. >>Then right-click in the upper
area, but not over a table or a join lines, and from >> Query Properties Select
Top 1.

Perrry said:
Would this show only 1 record? What I want is >> within the same Record#


Perry,
It should only show 1 record, because you're selecting the 'Top 1'
records... meaning only the 1st record will be shown. This will work
because you're sorting in DESC ordering by the date (most recent date
will be the 1st record in the sort)

The Max function works better in a Group By or Totals query

Nov 13 '05 #4

P: n/a
Larry said:
In the QueryBuilder window, pull down the Fields >>you want to see. Click on
Record Date and set Sort in Descending order. >>Then right-click in the upper
area, but not over a table or a join lines, and from >> Query Properties Select
Top 1.

Perrry said:
Would this show only 1 record? What I want is >> within the same Record#


Perry,
It should only show 1 record, because you're selecting the 'Top 1'
records... meaning only the 1st record will be shown. This will work
because you're sorting in DESC ordering by the date (most recent date
will be the 1st record in the sort)

The Max function works better in a Group By or Totals query

Mark
ac*********@aol.com

Nov 13 '05 #5

P: n/a

pe******@yahoo.com wrote:
Would this show only 1 record? What I want is within the same Record#
(in the same table), there are multiple dates, I want only to show
latest date record. Therefore, I might have:

ID Date
Rc1: 101 09/25/05 (excluded Rc1 9/1/05)
Rc2: 105 10/22/05 (excluded Rc2 09/22/05, 08/14/04...)
Rc3: 111 04/25/04...

I hope this is clearer.

Perry


If all the dates you want to compare are in the same record see:

http://groups-beta.google.com/group/...d211fb18?hl=en

James A. Fortune

Nov 13 '05 #6

P: n/a
<pe******@yahoo.com> wrote
Would this show only 1 record? What
I want is within the same Record# (in
the same table), there are multiple dates,
I want only to show latest date record.
Therefore, I might have:

ID Date
Rc1: 101 09/25/05 (excluded Rc1 9/1/05)
Rc2: 105 10/22/05 (excluded Rc2 09/22/05, 08/14/04...)
Rc3: 111 04/25/04...


Sorry, I did not understand you wanted the most recent date for each "record
type". You will need to use a Totals Query, grouping on Record#, and use the
Max on the Date field (which shouldn't be named "Date" because Date is a
reserved word).

Larry Linson
Microsoft Access MVP
Nov 13 '05 #7

P: n/a
Larry Linson wrote:
<pe******@yahoo.com> wrote
> Would this show only 1 record? What
> I want is within the same Record# (in
> the same table), there are multiple dates,
> I want only to show latest date record.
> Therefore, I might have:
>
> ID Date
> Rc1: 101 09/25/05 (excluded Rc1 9/1/05)
> Rc2: 105 10/22/05 (excluded Rc2 09/22/05, 08/14/04...)
> Rc3: 111 04/25/04...


Sorry, I did not understand you wanted the most recent date for each "record
type". You will need to use a Totals Query, grouping on Record#, and use the
Max on the Date field (which shouldn't be named "Date" because Date is a
reserved word).

Larry Linson
Microsoft Access MVP


I think Larry's got it. I also got confused by RecordID referring to a
group of records. RecordID looked like the primary key of your table
rather than a foreign key to another table. Ignore what I said.

James A. Fortune

Nov 13 '05 #8

P: n/a
Larry and James,
I sincerely thank you and appoligize for my poor explanation.
Your suggestions work to a certain extend, mostly becuase of my lack of
details here. I am beginning to think this may need a VB code instead.
Table1
Field1 Field2 Field3 Field4 ...
RC1: MedRec1 SOCDate1 CertDate1 Memo1

Example:

RC1: MedRec SOCDate CertDate Memo
100 01/01/05 01/01/05 FirstCert Period in
2005 for MR100
100 01/01/05 03/01/05 SecondCert Period
in 2005 for MR100
100 10/01/04 10/01/04 FirstCert Period in
2004 for MR100
100 10/01/05 12/01/05 SecondCert Period
in 2004 for MR100
103 02/01/05 02/01/05 FirstCert Period in
2005 for MR103
Output (perhaps via inner joint Query or VB?), shows only:

RC1: MedRec SOCDate CertDate Memo
100 01/01/05 01/01/05 FirstCert Period in
2005
100 01/01/05 03/01/05 SecondCert Period
in 2005
103 02/01/05 02/01/05 FirstCert Period in
2005 for MR103

P.S.: I will need to be able to add records in the form after the
output as well, therefore, filtering may not work.

If I had known this DB is going to transform into this, I would have
split this into 2 tables to begin with. Then, we won't have any
problem. I hate to have to go this route.

Perry

Nov 13 '05 #9

P: n/a
pe******@yahoo.com wrote:
Larry and James,
I sincerely thank you and appoligize for my poor explanation.
Your suggestions work to a certain extend, mostly becuase of my lack of
details here. I am beginning to think this may need a VB code instead.
Table1
Field1 Field2 Field3 Field4 ...
RC1: MedRec1 SOCDate1 CertDate1 Memo1

Example:

RC1: MedRec SOCDate CertDate Memo
100 01/01/05 01/01/05 FirstCert Period in
2005 for MR100
100 01/01/05 03/01/05 SecondCert Period
in 2005 for MR100
100 10/01/04 10/01/04 FirstCert Period in
2004 for MR100
100 10/01/05 12/01/05 SecondCert Period
in 2004 for MR100
103 02/01/05 02/01/05 FirstCert Period in
2005 for MR103
Output (perhaps via inner joint Query or VB?), shows only:

RC1: MedRec SOCDate CertDate Memo
100 01/01/05 01/01/05 FirstCert Period in
2005
100 01/01/05 03/01/05 SecondCert Period
in 2005
103 02/01/05 02/01/05 FirstCert Period in
2005 for MR103

P.S.: I will need to be able to add records in the form after the
output as well, therefore, filtering may not work.

If I had known this DB is going to transform into this, I would have
split this into 2 tables to begin with. Then, we won't have any
problem. I hate to have to go this route.

Perry


Perry,

I still don't understand how you're getting your output. Sorry. Is
any grouping done on the Memo or CertDate fields? I guess seeing two
100's for MedRec in your output threw me off track since you appeared
to want the latest of some date for a particular MedRec value. Maybe
explain in words how to get the output you want along with the example.
Additionally, make sure I understand where RC1, RC2 etc. are coming
from. If I understand how to get your output I may be able to create
the editable query you want or suggest VBA alternatives.

James A. Fortune

Nov 13 '05 #10

P: n/a
I agree with jim. You need to explain what you have and what you want in
_words_, precisely and concisely, rather than giving examples and expecting
us to figure out those details.

I don't have any background as a Database Psychic.

On the other hand, "something tells me" that it is unlikely that you need to
resort to VBA to get what you want. On the other other hand, perhaps it was
just a wayward breeze?

There are other very good suggestions on effective use of newsgroups at the
FAQ, http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP

<ji********@compumarc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
pe******@yahoo.com wrote:
Larry and James,
I sincerely thank you and appoligize for my poor explanation.
Your suggestions work to a certain extend, mostly becuase of my lack of
details here. I am beginning to think this may need a VB code instead.
Table1
Field1 Field2 Field3 Field4 ...
RC1: MedRec1 SOCDate1 CertDate1 Memo1

Example:

RC1: MedRec SOCDate CertDate Memo
100 01/01/05 01/01/05 FirstCert Period in
2005 for MR100
100 01/01/05 03/01/05 SecondCert Period
in 2005 for MR100
100 10/01/04 10/01/04 FirstCert Period in
2004 for MR100
100 10/01/05 12/01/05 SecondCert Period
in 2004 for MR100
103 02/01/05 02/01/05 FirstCert Period in
2005 for MR103
Output (perhaps via inner joint Query or VB?), shows only:

RC1: MedRec SOCDate CertDate Memo
100 01/01/05 01/01/05 FirstCert Period in
2005
100 01/01/05 03/01/05 SecondCert Period
in 2005
103 02/01/05 02/01/05 FirstCert Period in
2005 for MR103

P.S.: I will need to be able to add records in the form after the
output as well, therefore, filtering may not work.

If I had known this DB is going to transform into this, I would have
split this into 2 tables to begin with. Then, we won't have any
problem. I hate to have to go this route.

Perry


Perry,

I still don't understand how you're getting your output. Sorry. Is
any grouping done on the Memo or CertDate fields? I guess seeing two
100's for MedRec in your output threw me off track since you appeared
to want the latest of some date for a particular MedRec value. Maybe
explain in words how to get the output you want along with the example.
Additionally, make sure I understand where RC1, RC2 etc. are coming
from. If I understand how to get your output I may be able to create
the editable query you want or suggest VBA alternatives.

James A. Fortune

Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.