Create a check in check out database that allows us to track employees that are checking in and out

[Mentat]

Greetings, I am trying to put together a database that will allow me to track a LIST of users that i define. Ideally they would scan their badge, scan the RF SCANNER and hit check out. Then at the end of the shift they would then scan their badge scan the RF SCANNER and check in. They should not be able to check out a scanner if they already have one out.


Can someone give me a head start on something like this? I can use either ACCESS or SQL. again the requirements are:

scan a badge
scan a rf scaner
check out

then

scan badge
scan rf scanner
check in

cant check out scanner if they have one already out.

[ranman256]

TUser table:
Name, Badge, AtWork (Boolean)

tLog table,
ID , Clock, Status

TEquipment table
EqID, ItemName, Badge

user scans badge,where currently atWork = true,
since user is in, then He is clocking out,so,
Run append query to add to tLog table: Bdg123, 11/2/2016 6 am , OUT
then run update query to change
[AtWork]=not [AtWork]

Same with Equipment, if checked in ,then check it out, badge=null, so assign badge to the equip...
but if user already has one out ,alert.

vResult= Dlookup("badge","tEquipment","[Badge]='" & me.txtBadge & "'"
if isNull(vResult) then. 'User has no eq out
   vResult= Dlookup("EqID","tEquipment","[EqID]='" & me.txtEqID & "'"
   if isNull(vResult) then Docmd.openQuery "quCheckOutEq"
else
  MsgBox "you already have equip checked"
end if

[Mentat]

Thank you, its a start, some of it is a little over my head. its been a few years since working in DBs, but i know what tables to create and a basic idea of where to go. Can you provide anymore details? how does the Boolean field work? I do not think i have every messed with that. I currently have 3 tables setup. equipment/log/users.

Would i pre-input my users there and do the same for the equipment they are wanting to check out? I am assuming the log is where the accentual checking in/out is detailed in case want to run reports against it?

Thanks!

[Mentat]

I am also having an issue making the AtWork field in the table Boolean. Keep in mind I havent done much with DBs in years. Its slowly coming back though. thanks

[ssanfu]

Would i pre-input my users there and do the same for the equipment they are wanting to check out? I am assuming the log is where the accentual checking in/out is detailed in case want to run reports against it?

Exactly.
Maybe start with:
A table - tblUsers: maybe UserID_PK, FName, LName, BadgeNo
A table - tblEquipt: EquiptID_PK, EquiptName, EquiptNumber (scan code)
Maybe a table - tblEquiptStatus: Status_PK, Status (values would be "Check In", "Check Out")
A junction table - tblUserEquipt: UserEquiptID_PK, UserID_FK, EquiptID_FK, Status_FK, StatusDate


Draw these out on paper/whiteboard/cardboard to see if they meet your requirements.