Query Problem

[Duncs]

I hope someone can help with a query problem I have…

I have the following table structure:

fldTransactionID
fldStatus
fldStatusDate
fldTransactionAmt
fldLastTransactionDate

This table is populated as follows:

11537481 CREATED 10/11/2008 1992.00 11/11/2008
11537481 DISTRIBUTED 21/11/2008 1992.00 20/11/2008
11537481 ON_CARD 30/11/2008 1992.00 01/12/2008
12004143 NETWORKED 19/12/2008 204.00 13/12/2008
12004143 NETWORKED 19/12/2008 204.00 14/12/2008
12004143 NETWORKED 19/12/2008 204.00 15/12/2008
12004143 NETWORKED 19/12/2008 204.00 16/12/2008

What I need to do, is display the last ‘transaction’ for each unique
fldTransactionID. So, for example, using the above data I would
display:

11537481 ON_CARD 30/11/2008 1992.00 01/12/2008
12004143 NETWORKED 19/12/2008 204.00 16/12/2008

Can anyone assist with this?

Many TIA

Duncs

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I hope someone can help with a query problem I have…

I have the following table structure:

fldTransactionID
fldStatus
fldStatusDate
fldTransactionAmt
fldLastTransactionDate

This table is populated as follows:

11537481 CREATED 10/11/2008 1992.00 11/11/2008
11537481 DISTRIBUTED 21/11/2008 1992.00 20/11/2008
11537481 ON_CARD 30/11/2008 1992.00 01/12/2008
12004143 NETWORKED 19/12/2008 204.00 13/12/2008
12004143 NETWORKED 19/12/2008 204.00 14/12/2008
12004143 NETWORKED 19/12/2008 204.00 15/12/2008
12004143 NETWORKED 19/12/2008 204.00 16/12/2008

What I need to do, is display the last ‘transaction’ for each unique
fldTransactionID. So, for example, using the above data I would
display:

11537481 ON_CARD 30/11/2008 1992.00 01/12/2008
12004143 NETWORKED 19/12/2008 204.00 16/12/2008

Can anyone assist with this?

Many TIA

Duncs