Tracking participant attendance

[ahseenam]

Hey everyone:

I am requesting some help on an Access database I'm building for a community arts program to track participation in one of our special projects. I'm hoping someone can help!

I have three tables with fields as follows:

**Table: Participants**
Participants enroll in our program and we collect name, address, phone, etc and whether they are enrolled at site X, Y, or Z and for program Year 1, 2, or 3. Participants can not enroll for more than one year or more than one site. They then attend one of two workshops each week for 7 months.
ParticipantID
FirstName
LastName
Address
Phone
SiteNameLookup
YearLookup

**Table: DailyWorkshops**
Daily workshops are set up to show the workshop date, whether it was an A or B session (two sessions per week), who staffed that day's event, what artists attended, and what activities went on. This table is made into a form.
WkshpID
WkshpDate
AorB
SiteStaff
Artists
Activities

**Table: ParticipantAttendance**
This is where participant attendance will ultimately be stored... so after a few sessions you'll have many records for particpantID & WkshpID. Note there are no new fields here... they reference the Participants and DailyWorkshops tables.
ParticipantID (Participants.ParticipantID)
FirstName (Participants.FirstName)
LastName (Participants.LastName)
WkshpID (DailyWorkshops.WkshpID)
Level *(here I have a lookup to fill in HALF/PARTIAL/FULL, not just yes/no)*



Now I have a form based on DailyWorkshops. This form has a table embedded it. I want that table to automatically populate the participants and let me just check off Half/Partial/Full. Then I want to save that form, have that data append ParticipantAttendance and be able to move to a new form entry to set up a new daily workshop and check off attendance again... and so on.

So....
On the DailyWorkshop form, I might see something like this -- with just the Levels for me to fill in and everything else automatically populated when I create that day's workshop. I think this is probably just a temporary table.

WkshpID, ParticipantID, FirstName, LastName, Level
S12, 3-13, Bart, Simpson, Half
S12, 3-14, Barack, Obama, Full
S12, 3-15, Eric, Cartman, None
S12, 3-16, Bugs, Bunny, Full
S12, 3-17, Ned, Flanders, None


Then after a few days of doing this I may see my ParticipantAttendnce table grow to look like:

WkshpID, ParticipantID, FirstName, LastName, Level
S12, 3-13, Bart, Simpson, Half
S12, 3-14, Barack, Obama, Full
S12, 3-15, Eric, Cartman, None
S12, 3-16, Bugs, Bunny, Full
S12, 3-17, Ned, Flanders, None
S13, 3-13, Bart, Simpson, Half
S13, 3-14, Barack, Obama, None
S13, 3-15, Eric, Cartman, Full
S13, 3-16, Bugs, Bunny, Full
S13, 3-17, Ned, Flanders, Full
S14, 3-13, Bart, Simpson, Half
S14, 3-14, Barack, Obama, Full
S14, 3-15, Eric, Cartman, None
S14, 3-16, Bugs, Bunny, Half
S14, 3-17, Ned, Flanders, None
S15, 3-13, Bart, Simpson, Half
S15, 3-14, Barack, Obama, Full
S15, 3-15, Eric, Cartman, Full
S15, 3-16, Bugs, Bunny, Full
S15, 3-17, Ned, Flanders, Full


Sample database populated with fake data attached. Ignore the whole section on interviews... I've got that part working as I want it.

 

[bparkinson]

You should drop firstname and lastname from the ParticipantAttendence table. You've properly linked it with the ParticipantID. Not the question you asked, though.

 

[chrisguk]

I think first of all before you engage with building forms you should normalise your table structure better. We will find the management of it easier in the long run plus the performance will be better.

You can find some good examples here:

http://www.about-access-databases.com/database-normalisation.html