query to track files last status

[charlesg]

Hi folks,
This is my first posting to this forum and I hope someone will be able to help me out with this.
I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit.
The schema of the table is as follows
Filetrans_id Pk int Auto
Files_id int
Filetransdate date
Filestatus_id int

A filestatus can either be 1(IN) or 2 (out)
I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa.
Any help will be greatly appreciated.
Regards
Charles

 

[macca the hacke]

Open a new query in design view and bring in table FILETRANS.
Double click on all the fields so they are shown in the bottom of the query. Under the field Filestatus_id int (I assume this is the field that stores the 1s and 2s) in the criteria box, type in 2. Now run query.

 

[charlesg]

tracking files last recorded status

thanks for your response.The issue is that it is the files whose last recorded status is out that i want the to capture.
For example in the table a file can have several status that is constantly changing has it is released and returned from the unit.What i want the query to do is capture only those files whose last recorded status is out(1).
I tried to use the max,but it only returns one value.
regards,
charles

 

[macca the hacke]

OK. Write one query that shows you date of last recorded status by file ref # (ie group by file ref #, and max of date).
In a second query bring in original table and the query above. Make sure join is by file ref # and date from query 1 to original table. You should then be able to filter for only those with a status of out (1).

 

[charlesg]

tracking files last status.

Thanks for your response.
My problem is how to write such a query.CanU give me an idea?
When i tried this query:select file_id,trandate from filetrans
where filetrans_id=(select max(filetrans_id from filetrans), the query only returns a single result.I need the query to obtain all files whose last status =2
and i don't know how to write it properly.Please kindly help.
Regards,
charles