Consolidating records by date. 1

[aexley]

I have a table that has data in the following format:

ID No. Date Entered Comment
1 12/02/02 "sdfls dskljf aslkj..."
1 15/03/02 "wioyeri uik kasj ash.."
1 16/05/02 ",mzxcn z,mxc zxdczc..."
2 15/03/02 "ashdk kjasd kjhdf..."
2 23/05/02 "oieur lkjsfe ie kd..."
3 27/06/02 "lkjsfe oieu eiwo ..."

What I need is the latest comment that each ID No has associated with it by date so that I get:

ID No. Date Entered Comment
1 16/05/02 ",mzxcn z,mxc zxdczc..."
2 23/05/02 "oieur lkjsfe ie kd..."
3 27/06/02 "lkjsfe oieu eiwo ..."

This is driving me round the twist. If anyone can help I'd be grateful.

MTIA

aexley

 

[nabiS2K]

I would create a query with a group by that has the ID number as a Group By field and the Date as a Max field (leave out the comment in this query). Then, I would create another query containing the ID, Date, and Comments joined on ID and Date to the first query.

This should work. God Bless
Mike ;-)

 

[Quehay]

Nice one Mike! This is necessary because in the aggregate query the MAX(date) would give you a line for every comment if you GROUP BY comment (the max date for a given comment date is that comment date).

You'll get the latest comment for a given ID in the subquery. Then using this subquery (may be added to the GUI just like a table) and joining on ID and DATE will give you the actually comment that goes with ID / MAX (date).

(I hope this explanation wasn't totally gratuitous.)

BUT: If you have two comments on the same day, will Access distinguish between DATE & TIME? (I need to look that up.)

 

[aexley]

Well thank you gentlemen. That looks like just the thing. I'll give it a go.

As for the 'two Comments on one day' it does sometimes happen in my database. Can I assume that the above method will use both records? That isn't a problem as I would probably need both anyway but I do have the record time stamped as well. Will this be a problem for the above method?

Many thanks for your help

aexley

 

[Quehay]

aexley:

It shouldn't be (a problem). Access stores date/time values as a double with the decimal portion returning the time value.

This article is definitely worth reading:

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210276