Help With Dates in Access 1

[alphil2001]

Hi All,

hope you can help, I have a single table and form or records which log the date and number of transactions. What i would like to know is it is possible to select the date of a transaction and for access to show the last date this transaction happened.

IE date 13 july 05 (col 1) transaction is 37(col 2) when was the last date transaction 37 happened. Is this possible through lookup or creating other fields or controls.

I would also like to look over a number of columns of records to find the last date of this transaction and not just one column.

I have 12 columns in my table, the first is the date, then from column 2 to 12 records the number of different transactions, I would like to look at the date field(column) and then access to look through the other 11 columns and return the last date the same transaction happened.

Hope I have explained it ok?

Thanks for your help.

ALPHIL

 

[PHV]

First create a normalization query named, say, qryTransactions:
SELECT [col 1] As theDate, [col 2] As theTransaction FROM [yourTable] WHERE [col 2] Is Not Null
UNION SELECT [col 1], [col 3] FROM [yourTable] WHERE [col 3] Is Not Null
UNION SELECT [col 1], [col 4] FROM [yourTable] WHERE [col 4] Is Not Null
...
UNION SELECT [col 1], [col 12] FROM [yourTable] WHERE [col 12] Is Not Null

Now your last date query:
SELECT theTransaction, Max(theDate) AS LastDate
FROM qryTransactions
GROUP BY theTransaction;

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

 

[alphil2001]

Thanks very much PHV, I will try it and let you know,

Your help is very much appreciated, your a star!!!

Alphil