Most recent transaction from a company

[H. van Veenen]

Anyone,

I stumbled upon a problem:
How do I select the most recent transaction from a given company if there
are more transactions to choose from.

E.g.

Company A has 2 transactions a1 and b2
a1 = 01/01/2002 (with some other details)
b2 = 01/01/2003 (with some other details)

If I use "SELECT comp.name, trans.transdate FROM comp LEFT JOIN trans on
comp.id = trans.comp_id" it returns both transactions !!

I have tried (also) to collect the transactions in a seperate query and used
this query as 'table', but no difference

With regards

H. van Veenen

 

[[MVP] S. Clark]

I do this in multiple queries. First query finds the last transaction date
per customerid. Link this query against the regular data, linked by
customerid and transaction date.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Dale Fye]

I usually do it with a subquery, something like shown below.

SELECT T.*
FROM yourTable T
INNER JOIN (SELECT Comp_ID, MAX(TransDate) as RecentTrans
FROM yourTable
Group BY Comp_ID) as T1
ON T.Comp_ID = T1.Comp_ID
AND T.TransDate = T1.RecentTrans

--
HTH

Dale Fye


Anyone,

I stumbled upon a problem:
How do I select the most recent transaction from a given company if
there
are more transactions to choose from.

E.g.

Company A has 2 transactions a1 and b2
a1 = 01/01/2002 (with some other details)
b2 = 01/01/2003 (with some other details)

If I use "SELECT comp.name, trans.transdate FROM comp LEFT JOIN trans
on
comp.id = trans.comp_id" it returns both transactions !!

I have tried (also) to collect the transactions in a seperate query
and used
this query as 'table', but no difference

With regards

H. van Veenen