query question

[tizwaz]

I'm in the process of creating a database for security passes. Each pass may be issued several times during its lifetime to different people. sometimes they are lost or stolen and can't be re-issued. I want to be able to query to show which passes are available to re-issue.
I'm thinking of having table with one record for each time the pass is issued. I would be able to query for all those with a date returned, however this wouldn't taken account of the fact that the same pass number may have since already been re-issued. Is there a way of only showing records which have been returned but not re-issued using the structure I have planned or should I have a rethink?

eg pass 1 issued to Joe Bloggs 10/6/07 returned 15/7/08
If there are no further entries for pass 1 then this was available to reissue

however if there is a later entry for pass 1 showing issued to tom Smith on 1/8/08 and no return date then this isn't available to reissue

 

[PaulBricker]

You might try setting a flag (boolean) when the pass is issued and reset it when it's returned. Then filter by that flag.
For example, in the after update event for the date issued, you could set the flag to -1 and in the after update event for the return date, set the flag back to 0. Set the default value for the flag to 0

We use flags for similar things and they work well.

Paul

 

[lespaul]

I would have a structure like:
[tt]
tblPass
PassID
Status (Issued, Available, NA)
StatusDate
{any other info about the pass - an ID number? other identifiers?}

tblIssuedPasses
IssueID
PassID (FK to tblPass)
UserID (FK to tblUsers)
InServiceDate
OutServiceDate

[/tt]

Leslie

Have you met Hardy Heron?

 

[tizwaz]

Thanks for your help everyone - I'll use these suggestions