total query with last date

[zionsaal]

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

 

[Kelsy]

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

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.

 

[JK]

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
|
| 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
|

 

[zionsaal]

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
|
| 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
|

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

 

[John W. Vinson]

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]

 

[???]

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
|
| 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
|

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