Desperate help needed, confused

[3Mark3]

Hello all...I'm having a hard time piecing together a query I'm trying to get to work.

I have two fields in this multiple field table.

These are the two fields involved with the query:
Transaction Type Code
Date Added Transaction
Loan Number

what I'm trying to do is this: I want to pull the oldest date for every Transaction type code. Below is an example of some of the data:

Loan Number Trans. Type code Date added Trans.
0000686466 UF* 31-oct-05
0000686466 UF* 07-Nov-05
0000686466 UFF 29-Oct-04
0000686466 UFF 15-Nov-04
0000686466 UFU 29-Nov-03
0000686466 UFU 13-Dec-04
0000686466 UFU 07-Jul-05

Please help! Thank you very very much!

 

[lespaul]

If you are using Access 2000 or above this should do what you need:

SELECT [Loan Number], B.[Transaction Type Code], B.[Date Added Transaction] 
FROM TableName A
INNER JOIN (SELECT [Transaction Type Code], Min([Date Added Transaction]) FROM TableName GROUP BY [Transaction Type Code]) B ON A.[Transaction Type Code] = B.[Transaction Type Code]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

SELECT [Transaction Type Code], Min([Date Added Transaction]) As [oldest date]
FROM yourTable
GROUP BY [Transaction Type Code]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3Mark3]

Awesome, thank you both very much for your quick responses!