I am trying to create an equipment sign-out database in Microsoft Access, but I'm having trouble making it so that if an item is signed out, that same item cannot be signed out by another individual between the original person's sign-out and sign-in dates.

I have my tables set up like this:

Table: Staff (id, FirstName, LastName)

Table: Laptop (id, Color, Name)

Table: Projector (id, Name)

Table: Sign_out (id, Staff, Laptop, Projector, Sign-out, Sign-in)

In the Sign-out table, the Staff, Laptop, and Projector fields are look-ups from the respective other tables.

I though making Staff, Laptop, Projector, and Sign-out dates all primary keys would acheive this, but it's not working that way.

Any help is GREATLY appreciated!

 

add me to your messenger wheres_my_doggs@hotmail.com I can help you out .

 

sad.gif I don't have MSN, unfortunately.

 

What is the Sql statement your using and what fields are you trying to pull?

 

I haven't isnerted any SQL manually up to this point.

I've just created the table in design view, and trying to set it up so that it doesn't allow two people to sign the same thing out at once, so that later I can create a form based on it.

 

So the Staff, Laptop and Projector are actually ID's of the other tables? If thats so just use this statement. It should list all the projector names available to be signed out. That is if when they sign out you set the sign-in field to null. Sorry I use Firebird and not used to access so not sure if the look-up fields are stored as ID's or not, plus you might need some [] around fields for it to work in access. Hope that helps. fing02.gif

select Name from projector
where id not in (select projector from sign_out where (sign-out is not null and current_date >= sign-out and sign-in is null))

 

maybe if the sum of a field can't equal zero, if you set it up kinda like that, hell i don't knwo what i am talking about. i took advanced data baseing in college but it was a while ago. and i'm running on 16+ hours of awake time. take that and werk it in yer crank

 

Thank's for the help everyone, I eventually got it figured out fing02.gif