last transaction in a query?

[zionsaal]

I have a table with 5 fields
ID PersonID EDate Amount and TransType

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


PersonID LastOfEDate Amount TransType

if a person has tow transactions in the max date I want only one of
them

How can i do that?
thanks

 

[zionsaal]

I have a table with 5 fields
ID PersonID EDate Amount and TransType

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

PersonID LastOfEDate Amount TransType

if a person has tow transactions in the max date I want only one of
them

How can i do that?
thanks

http://www.mvps.org/access/queries/qry0020.htm

 

[Steve]

Create a query based on your table. Use the following for the fields in the
order shown here:
PersonID
LastOfEDate:EDate
Amount
TransType
EDate

With your query in design view, click on the Sigma button (looks like a
capiyal E) in the menu at the top of the screen. Unde the EDate field (last
field) change Group By to Max. Also uncheck Show Field. When you run your
query, you will get the last record for each person based on EDate.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Dale Fye]

Steve,

Totally disagree. This will not get him the record that corresponds to the
latest date for each individual. It will give him the most recent date for
each individual, but the Amount and TransType fields may or may not have
anything to do with the most recent record. The easiest two ways to
accomplish this are:

SELECT PersonID, Edate as MostRecentEdate, Amount, TransType
FROM yourTable
WHERE Edate = (SELECT Max(Edate) From yourTable T WHERE T.PersonID =
yourTable.PersonID)

The other way is a little more complicated, but is probably quicker.

SELECT PersonID, Edate as MostRecentEdate, Amount, TransType
FROM yourTable
INNER JOIN (SELECT PersonID, Max(Edate) as MaxDate
FROM yourTable
GROUP BY PersonID, Edate) as PersonMaxDate
ON yourTable.PersonID = PersonMaxDate.PersonID
AND yourTable.Edate = PersonMaxDate.MaxDate

HTH
Dale

 

[John Spencer]

Dale,
Your method should work except the poster wants ONLY ONE record returned per
Person per Date and the poster said that there may be more than one record
per person per date.

Since the poster did not say what criteria she/he wanted to use to make the
decision on which record to show in case of TIES and whether or not the
result needed to be editable, the following is one solution.

SELECT PersonID as Person
, Edate as MostRecentEdate
, First(Amount) as TheAmount
, First(TransType) as TransactionType
FROM yourTable
WHERE Edate = (SELECT Max(Edate)
From yourTable T
WHERE T.PersonID = yourTable.PersonID)
GROUP BY PersonID, EDate

Or the following more complicated method
SELECT YourTable.PersonID
, Edate as MostRecentEdate
, First(Amount) as TheAmount
, First(TransType) as TransactionType
FROM yourTable
INNER JOIN (SELECT PersonID, Max(Edate) as MaxDate
FROM yourTable
GROUP BY PersonID, Edate) as PersonMaxDate
ON yourTable.PersonID = PersonMaxDate.PersonID
AND yourTable.Edate = PersonMaxDate.MaxDate
GROUP BY YourTable.PersonID, YourTable.EDate

If the poster need the query results to be editable then the use of the TOP
predicate may be a solution.

 

[zionsaal]

Dale,
Your method should work except the poster wants ONLY ONE record returned per
Person per Date and the poster said that there may be more than one record
per person per date.

Since the poster did not say what criteria she/he wanted to use to make the
decision on which record to show in case of TIES and whether or not the
result needed to be editable, the following is one solution.

SELECT PersonID as Person
, Edate as MostRecentEdate
, First(Amount) as TheAmount
, First(TransType) as TransactionType
FROM yourTable
WHERE Edate = (SELECT Max(Edate)
From yourTable T
WHERE T.PersonID = yourTable.PersonID)
GROUP BY PersonID, EDate

Or the following more complicated method
SELECT YourTable.PersonID
, Edate as MostRecentEdate
, First(Amount) as TheAmount
, First(TransType) as TransactionType
FROM yourTable
INNER JOIN (SELECT PersonID, Max(Edate) as MaxDate
FROM yourTable
GROUP BY PersonID, Edate) as PersonMaxDate
ON yourTable.PersonID = PersonMaxDate.PersonID
AND yourTable.Edate = PersonMaxDate.MaxDate
GROUP BY YourTable.PersonID, YourTable.EDate

If the poster need the query results to be editable then the use of the TOP
predicate may be a solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

thanks all

 

[Guest]

RTQ (-.5)

Good catch. I missed the single record in the event of matches on
Person/Edate.
I knew there was a reason I like to put an autonumber field in each table.

Dale