I'm creating a database based off of a very poorly designed spreadsheet.
The database should be fairly straightforward; however, I haven't used Access since 2003 so I am a bit rusty.
I have 5 tables currently.
tblStaff first name (short text)
last name (short text)
StaffID (Autonumber PK)
tblPositions ID (Autonumber PK)
position (short text) // This has stuff like Nurse, Teacher, Substitute, etc.
tblItemList
item (short text primary key) //This is the name of the actual certification or vaccine; i.e. CPR, OSHA, MMR etc.
type (short text) //Just two items here - medical or certification
YearsGoodFor (number) //how many years is this cert/vacc good for?
tblItemDetails
StaffIDFK (long number) //foreign key I used to create a relationship with tblStaff
Item (short text) //this is just a lookup field that uses tblItemList items
DateItemReceived (date/time) //this is the date the cert/vacc was received
ItemID (Autonumber PK) //I don't really know if this is required or not
I have 1 query:
qryItemDetails
Queries from tblItemList and tblItemDetails and has 3 calculated fields that show the status, expiration date, and days remaining
I have 4 forms (2 forms and 2 subforms):
frmStaff //this shows first name, last name, position, and in a subform it shows the query
frmEnterNewItem //this shows the first name, last name, and in a subform is tblItemDetails
I can't figure out how to have each staff member only have each cert/vacc once.
In other words, a staff member can have CPR listed as one of their certs multiple times. Please see (and forgive the rough draft appearance of) the screenshots for a better idea.
ANy help would be much appreciated. Thank you.