Results 1 to 9 of 9
  1. #1
    rknyc is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    7

    Need help with a database of staff and vaccinations and certifications for a school

    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.

    Click image for larger version. 

Name:	SS1.jpg 
Views:	18 
Size:	104.5 KB 
ID:	28676Click image for larger version. 

Name:	SS2.jpg 
Views:	18 
Size:	103.7 KB 
ID:	28677Click image for larger version. 

Name:	SS3.jpg 
Views:	17 
Size:	96.5 KB 
ID:	28678Click image for larger version. 

Name:	SS4.jpg 
Views:	18 
Size:	78.3 KB 
ID:	28679

    ANy help would be much appreciated. Thank you.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In 5 lines or less of simple English (just as you would tell an 8 year old), tell readers WHAT exactly are you trying to do?

    Why do you think you need 2 item list tables? I'm interested in your thought process.

    The database should be fairly straightforward
    , so if you were having this built by someone else, what would you tell them when they ask "What is your specific requirement?"

  3. #3
    rknyc is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    7
    I want the database to have a list of staff members, what their certifications are, the date it was received, and the date it expires. One staff member can have numerous certifications, each certification can belong to numerous staff members, but one staff member cannot have two of the same certs.

    My end goal is to be able to generate reports of staff members who have expired certifications and need some form of written notice that I can automatically generate with the database.

    There's only 1 Item List table. I didn't realize Access had autocreated ItemList_1. I've since deleted it.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff. Where does Position fit in this scenario?
    Do you have a list of possible Certifications, and the duration/effective period of each Certification?

  5. #5
    rknyc is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    7
    Position is just the title of their position. Nothing else. I don't think it needs an ID field but I am not sure. ItemList lists the certifications and the number of years it is good for. ItemDetails links it all together (I think). I've attached more screenshots.

    Click image for larger version. 

Name:	SS5.jpg 
Views:	13 
Size:	110.4 KB 
ID:	28680Click image for larger version. 

Name:	SS6.jpg 
Views:	13 
Size:	103.1 KB 
ID:	28681Click image for larger version. 

Name:	SS7.jpg 
Views:	14 
Size:	104.7 KB 
ID:	28682

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    rknyc is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    7
    I can't tell if you're being rhetorical. Here's my thought process on the neat diagram you posted:

    Red keys = PK, Green key = FK, Blue key = Composite key

    tblStaffMember has MemberID which is unique. It also has PositionID which refers back to Position to grab the appropriate title.
    tblCertification has CertificationID which is unique along with details of the cert.
    tblStaffHasCertification takes MemberID from tblStaffMember (one-to-many), and CertificationID from tblCertification (one-to-many) and lists everything about everything.

    I am going to put it together your way and rename all the stuff a little later on and see what result I get. I will post my results.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm just trying to be helpful. My question on thought process was to "hear" what you were thinking to get the "design" you showed in post #1.
    I can assure you that you do not need Acces to get to the point of identifying tables and relationships nor even testing your set up to ensure you can get the info you need to support your requirements. A pencil and paper, some test data and some "what if" scenarios played against the model will save you hours of frustration.

    Good luck. Glad you found the draft model of value.

  9. #9
    rknyc is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    7
    Brilliant sir. You've pointed me in the right direction. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-20-2016, 07:06 PM
  2. Replies: 5
    Last Post: 05-17-2014, 08:49 AM
  3. Replies: 7
    Last Post: 08-30-2013, 03:43 PM
  4. School Transportation Database
    By SMOORELCC1 in forum Database Design
    Replies: 1
    Last Post: 01-18-2013, 12:43 AM
  5. Access 2007 Certifications --Getting
    By cman in forum General Chat
    Replies: 1
    Last Post: 07-01-2012, 12:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums