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

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

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

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


<perryche@yahoo.com> wrote in message
news:1119722544.893405.240790@o13g2000cwo.googlegr oups.com...[color=blue]
> 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
>[/color]

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

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

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

Larry said:[color=blue][color=green]
>>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.[/color][/color]


Perrry said:
[color=blue][color=green]
>> Would this show only 1 record? What I want is >> within the same Record#[/color][/color]

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

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

Larry said:[color=blue][color=green]
>>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.[/color][/color]


Perrry said:
[color=blue][color=green]
>> Would this show only 1 record? What I want is >> within the same Record#[/color][/color]

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
accesspro4u@aol.com

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

perryche@yahoo.com wrote:[color=blue]
> 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[/color]

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

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

<perryche@yahoo.com> wrote
[color=blue]
> 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...[/color]

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

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

Larry Linson wrote:[color=blue]
> <perryche@yahoo.com> wrote
>[color=green]
> > 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...[/color]
>
> 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[/color]

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

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

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

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

perryche@yahoo.com wrote:[color=blue]
> 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[/color]

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

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

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

<jimfortune@compumarc.com> wrote in message
news:1120006299.674204.188770@o13g2000cwo.googlegr oups.com...[color=blue]
> perryche@yahoo.com wrote:[color=green]
> > 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[/color]
>
> 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
>[/color]