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

 

[Albert D. Kallal]

You must have a "main" table with a single list of PersonID.

Lets assume it is called tblPeople, PK = "ID"

So, use that table to provide a unquite list of PersonID.

The query can be like:

select FirstName, Lastname, WorkPhone,
PersonID, EDate, Amount, TransType

from tblPeople
inner join tblTransactions
on tblPeople.id = tblTransactions.PersonID
Where tblTransactions.ID =
(select top 1 ID from tblTranactions
where PersonID = tblPeople.id order by EDATE DESC, ID DESC)

IS IS VERY important to note that the order by of

EDATE DESC, ID DESC

YOU MUST include the ID DESC in case there can be more then one transaction
for that date. So, I am assume that for all of your tables, you do include a
default primary key column of "id".

Also, note that the above solution is nice, since you can easily just drop
in additional parent, or child records from either table in he query
builder.

Also, if you want to include ALL people in the repot/query...even if they do
NOT have
a transaction date, change the "inner join" to a "left join", and add a

or (tblTransactions.id is null)

to the above condition.

So, build the query in the query builder, drop in both tables. Draw the join
line. and then simply grab/drop in all the fields you need from both tables.

And, then drop in the transaction.id column, and type in eh above condition
into he "where" part of the query builder:

(select top 1 ID from tblTranactions
where PersonID = tblPeople.id order by EDATE DESC, ID DESC)

So, you actually build this whole thing from the query builder, and the ONLY
manual entry part is the above condition for the transaction.id field
collum. The rest is all drag and drop...

 

[krissco]

Join the table with itself using a sub query. Enter the following into
the SQL view (replace "{Table}" with the name of your table):

select t.PersonID, t.EDate as LastOfEDate, t.Amount, t.TransType
from {Table} as t, (Select t2.PersonID, max(t2.EDate) as maxDate from
{Table} as t2 group by t2.PersonID) as tMax
where t.PersonID = t2.PersonID
and t.EDate = t2.maxDate

-Kris

 

[zionsaal]

Join the table with itself using a sub query. Enter the following into
the SQL view (replace "{Table}" with the name of your table):

select t.PersonID, t.EDate as LastOfEDate, t.Amount, t.TransType
from {Table} as t, (Select t2.PersonID, max(t2.EDate) as maxDate from
{Table} as t2 group by t2.PersonID) as tMax
where t.PersonID = t2.PersonID
and t.EDate = t2.maxDate

-Kris

thanks all