Attendance Register

[alex91]

Hi, i have set up an attendance register system and want to work out the number of sessions each memeber has attended (total ticks in one row), i have managed to calculate the number of members in each session but not the other way round.
Alex

 

[Rabbie]

Your data is not normalised because you have a separate column for each date. It looks like you are thinking in Excel mode and not in Access mode.

Once your data is normalised you will find it much easier to do your calculations.

See these links for more info

http://www.datamodel.org/NormalizationRules.html

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

In general Access tables should be tall and thin and NOT Short and fat.

As you have it just now you will need to add a new column every time there is a new session.

 

[alex91]

Hi, thanks for your quick reply.
I have had a look at those sites but am still struggling to set it up to look like an attendance registers. What fields and relationships would you use?
Thanks
Alex

 

[Rabbie]

At the simplest level I would have 3 tables. One for people, one for sessions and a junction table to model the Many to Many relationship between People and Sessions.

Please post again if you have any queries

 

[alex91]

Thank you again. i have got the relationships set up now and a Date form with the members details in a subform. The only problem is to add a memeber u have type thier member id.
Would it be possible to have a list of memebrs on each date and a yes/no field to record whether they have attended.
Many Thanks
Alex

 

[Banana]

If you do a search for 'class attendance prototype' I posted long time ago that demonstrate how to have a spreadsheet-like entry with normalized structure you may be able to get some ideas of presenting the data.

As for adding the member, IMHO, it's easier to do so on a separate form and refer to attendance roster once the member has been added. But if you want to do this on one form, you would need to code for the click event on empty area or something like that to enable adding a member. The question is whether you want to do the extra work yourself or let Access do it for you.

 

[alex91]

Cheers, have had a look at that database, is very well done but dont think my Access skill is quite that gd.
Any Alternatives?
Thanks Again

 

[Banana]

There may be others, but I can only think of going to Mircosoft's website, which has template section where you can search various templates; pretty sure there were two or three template for class registration there. (Not an attendance tracking per se but you could adapt it to your needs)

HTH.