total query with last date

  • Thread starter Thread starter zionsaal
  • Start date Start date
Z

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
 
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
Click to expand...

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.
 
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
|
 
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
|
Click to expand...

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
 
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
Click to expand...

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
|
Click to expand...

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
Click to expand...
 
Back
Top