total query with last date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zionsaal@gmail.com - 20 Jun 2007 01:49 GMT

I have a table with 5 fields
ID PersonID Date Amount and Type

I want a query with the last record of each person based on date and
I
want the results like this

PersonID LastOfDate Amount Type

How can i do that
thanks

Reply to this Message

Kelsy - 20 Jun 2007 04:27 GMT

On Jun 19, 8:49 pm, zions...@gmail.com wrote:

> I have a table with 5 fields
> ID PersonID Date Amount and Type
[quoted text clipped - 7 lines]
> How can i do that
> thanks

You need 2 queries to accomplish this. First make a Group By query
that pulls out just PersonID and the Maximum Date. Next, make a second
query that uses the first query (containing the max date) and your
original table to get the rest of the data you need.

Reply to this Message

JK - 20 Jun 2007 06:48 GMT

One query will suffice.

SELECT PersonID, TranDate, Amount, Type _
FROM YourTable
WHERE TranDate=DMax("[TranDate]","YourTable" , _
"[PersonID]=" & [PersonID]);

BTW, It is not advisable to use reserved words such as "Type" or "Date" as
field names, I suggest the you change the field names to something like
"TranType", "TranDate" or similar.

Regards/JK

|I have a table with 5 fields
| ID PersonID Date Amount and Type
[quoted text clipped - 7 lines]
| How can i do that
| thanks

Reply to this Message

zionsaal@gmail.com - 22 Jun 2007 17:17 GMT

> One query will suffice.
>
[quoted text clipped - 23 lines]
> | How can i do that
> | thanks

thanks
if a person hes tow trans in a
same date I get tow records for this person I want only one record per
person no meter which record as lung it is in the last date

Reply to this Message

John W. Vinson - 22 Jun 2007 18:08 GMT

>thanks
>if a person hes tow trans in a
>same date I get tow records for this person I want only one record per
>person no meter which record as lung it is in the last date

A Totals query using a Subquery will work, then. First() gets the first record
in disk storage order, basically arbitrary.

SELECT PersonID, Max(TranDate) As LatestDate, First(Amount) As FirstOfAmount,
First(Type) As FirstOfType
FROM YourTable
WHERE TranDate=DMax("[TranDate]","YourTable" ,
"[PersonID]=" & [PersonID]) GROUP BY PersonID;

John W. Vinson [MVP]

Reply to this Message

??? - 25 Jun 2007 02:31 GMT

<zionsaal@gmail.com>
??????:1182529073.127701.316300@m36g2000hse.googlegroups.com...

>> One query will suffice.
>>
[quoted text clipped - 29 lines]
> same date I get tow records for this person I want only one record per
> person no meter which record as lung it is in the last date

Reply to this Message