Most recent in a one-many relationship... (1 Viewer)

[jstutz]

Ok, this is probably very easy, but my brain has taken the day off and I've reached a brick wall. Here's the setup...

We have an in-house library that employees can check items in and out of. As items are checked in or out, a database tracks the "movement" of the items. There are three relevant tables:
- Master (this table provides information about a library item; key field Master_ID),
- Movement (tracks when the items were moved, if they went IN or OUT and who received them if they went out; key field Move_ID)
- MoveDetail (this table links to Movement via Move_ID and tracks which items where moved using Master_ID; key field Move_Det_ID).

What I'm trying to do is create a query that will display a list of all items in the library and the most RECENT movement record associated with that item so I can determine if it's in or out of the library. I've got the MoveDetail table linked to the Master table no problem, but my query shows me ALL movements for the item and not just the most recent. I just can't figure it out and I'm sure it's something REALLY easy.

Here's an example, I have a book (Master #2) that was checked out on 10/01/01, back in on 10/02/01 and then out again on 10/15/01. Currently my query displays all 3 movement records for this book, like:

2 OUT 10/01/01
2 IN 10/02/01
2 OUT 10/15/01

I would like the query to return just the most recent record for Master #2, like:

2 OUT 10/15/01

Any thoughts? TIA...

js

 

[ott]

Use the max function on the date of movement and it will give you the latest date of movement for a given item. The SQL statement for the query would look something like this:

SELECT Table1.Book, Max(Table1.MoveDate) AS MaxOfMoveDate
FROM Table1 WHERE Table1.Book = "Master 2";

 

[jstutz]

Thanks... worked like a dream. I knew it was something easy!

js