Results 1 to 5 of 5
  1. #1
    serenechaos is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    13

    Linking a trainee name table with training days and logging absences

    Hi everyone,



    Complete newbie here with only a few hours of Access training videos under my belt.

    I have one table with three fields: an ID field, day number field (days 1-30 of a course) and a date field which displays the calendar day corresponding to the training day).


    I have another table with trainee names and an ID field for each trainee.


    For each trainee, I would like to log absence (there are six training sessions per day that each trainee must attend - they attend more regularly than they're absent, so i thought it would make sense to focus on the absences).


    I'm trying to get my head around the relationships and setting up other tables - it seems that in whichever way I link my tables, Access doesn't understand what I want!


    I'm thinking I need to add the six training sessions as fields in the training days table (or not? somewhere else? a separate table to the training days one?) Then what's the best way to link up the two (or more?) tables then so that I can log each trainee's presence/absence for each of the (30 days * 6 sessions)? Or is it not a question of linking the tables, simply of setting up some sort of query that feeds to a third table? Also, is it possible/advisable to set this up 'behind the scenes' first, then later adding on a user form that does the same?

    I've managed to set up one to many relationships nicely in another table (trainee groups) which puts trainees in different training groups, but with all the tables as they are at the moment, my attempts at fiddling with the relationships only result in the trainee IDs matching day IDs (trainee 1 = day 1, trainee 2 = day 2 etc.) - this is obviously not right! What am I missing to be able to log attendance for each of the 180 sessions for each trainee? Once I get that right, I'd also like to view attendance per group (e.g. if a group has 10 trainees, what was their attendance like for the week?)

    Any advice would be appreciated - I'm sure for any seasoned Accessor it would be clear where my knowledge gap is - any pointing in the right direction would be really helpful!

    Many thanks!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can't do this correctly using 2 normalised tables
    Suggest you use 3 tables with a structure like this:

    tblCourses

    CourseID - autonumber PK
    TotalDays - number
    StartDate - date/time
    EndDate - date/time

    tblTrainees
    TraineeeID - autonumber PK
    LastName - text
    FirstName - text

    tblCourseTrainee - junction table
    CourseTraineeID - autonumber PK
    CourseID - number FK
    TraineeID - number FK
    CalDate - date
    Present - yes/no

    Create a relationship between the tables linking the PK fields of the first two to the FK fields of the third table
    Test out the structure with dummy data.
    Only create the form once its working the way you want
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    serenechaos is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    13
    Ah! Many thanks! Ok so here's what I've done - and it seems to be working - I can click on the expansion icon in various places in the database to see the related data now, the same as what I had for the trainees/rooms before - so that's complete!

    Click image for larger version. 

Name:	Clipboard01.jpg 
Views:	13 
Size:	79.5 KB 
ID:	32660Click image for larger version. 

Name:	Clipboard02.jpg 
Views:	13 
Size:	85.8 KB 
ID:	32661

    Another quick question. Is it normal to work with everything as numbers as in the junction table - why not names? surely that'll be easier to read/understand - I imagine setting up queries and displaying other fields later on would solve that problem though, but that's a post for another thread Any other comments on the relationships/anything else here on the screenshots?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by serenechaos View Post
    Ah! Many thanks! Ok so here's what I've done - and it seems to be working - I can click on the expansion icon in various places in the database to see the related data now, the same as what I had for the trainees/rooms before - so that's complete!

    Another quick question. Is it normal to work with everything as numbers as in the junction table - why not names? surely that'll be easier to read/understand - I imagine setting up queries and displaying other fields later on would solve that problem though, but that's a post for another thread Any other comments on the relationships/anything else here on the screenshots?
    1. The +sign opens a Subdatasheet but these are of limited use. Normally use queries to view data from more than one table

    2 You could use names instead but as previously stated you would normally use queries rather than looking at the junction table itself

    3. I would personally use Present rather than Absent. Registers normally explicitly mark someone as being present

    4. Tables with only one field apart from the PK field are often unnecessary. Do you really need the Rooms and Period tables?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    serenechaos is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    13
    Hi,

    Thanks for those tips!

    3. most trainees are usually present, and so we keep a log of the hours of absence they have to be able to reschedule the training of that time - what I'm actually studying to do now is to append any absent trainees to a separate table with their names, the date and period as three of the fields.

    4. You're right - the period table just lists 6 periods from 1 to 6 and we don't need any other info about the periods. The room table's one other field, however, has a code which identifies its building, floor and passage, e.g. BG-351. Aha! So I could actually just make a field in the groups table - instead of having a RoomID that links to the Rooms table, just have a RoomCode field within the groups table.

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

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  2. How to record time logs after logging in and logging out
    By annayanagi014 in forum Programming
    Replies: 1
    Last Post: 02-18-2015, 07:52 AM
  3. Replies: 1
    Last Post: 06-18-2014, 12:19 AM
  4. Replies: 1
    Last Post: 11-01-2013, 04:05 PM
  5. Report from a data logging table
    By chrismec in forum Reports
    Replies: 1
    Last Post: 02-29-2012, 01:03 PM

Tags for this Thread

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