Selecting only the latest date

[pkin]

Hi all,

I've been trying to work out how I can SELECT only the latest date from a table. I've created an Audit Trail Table in Access and want to report on a form, where the item currently is:

I have two tables:

Items = ItemNo+ItemDesc

Audit= DateTime+ItemNo+Loc+User

and I want to see on a Form and a Report what each item is and where it currently is.

Hope this is clear, and hope to hear from you soon


pkin

 

[Nebuchednezzar]

How about this...

select * from your_table where your_table.datefield In
(select max(datefield) from your_table where itemno = "your_item_no"

 

[pkin]

I don't think this will work. What I'm trying to get is: -

Item Description Loc User

1 Item 1 1 Mr X
2 Item 2 1 Mr Y
3 Item 3 5 Mr A

etc .....

Each Item will have a number of Rows in the Audit Table, as the purpose of the Audit table is to record the movements of each item: -

Item Date/Time Item Loc User
1 07/03/01 12:00 1 1 Mr X
1 07/03/01 12:01 1 2 Mr Y
1 07/03/01 12:15 1 5 Miss A
1 07/03/01 15:34 1 9 Stock

etc ...

All I need to see on a Form or a Report is the latest date record form the Audit Table with the Item Description form the Item Field.

Any more suggestions?

 

[Nebuchednezzar]

If you're looking it up for a specific item each time, you can run...
SELECT AUDIT.TRANS_DATE, INVY.DESCRIPTION
FROM AUDIT INNER JOIN INVY ON AUDIT.ITEM = INVY.ITEM
WHERE AUDIT.TRANS_DATE IN
(SELECT MAX(TRANS_DATE) FROM AUDIT WHERE INVY.ITEM = "1"

--in the above, you could use a variable in place of "1".

if you want a list of the max date for all items, run...

SELECT MAX(AUDIT.TRANS_DATE), INVY.DESCRIPTION
FROM AUDIT INNER JOIN INVY ON AUDIT.ITEM = INVY.ITEM
GROUP BY INVY.DESCRIPTION;