Using Access to Keep Track of Attendance 2

[clem]

I have been asked by my church to create a database that will keep track of attendance for children's church. I will create a table with personal information about each child such as name, address, birthdate, etc. but what would be the best way to track each child's attendance for each of the 52 Sundays in a year?

 

[culshaja]

What you need is a table that stores the data against the childs ID/Primary key.
Use an autonumber for the childs primary key would make this simpler.
Create a second table, and have one column for the date, and one column for the primary key in the child table. Make this pair unique through a unique index. You might want to make a synthetic key using and autonumber if you want to link to this table.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[clem]

Thanks so much for your reply. If I understand correctly, I should create the first table using an autonumber to be the child's ID and also the primary key and then enter all the personal information such as name, addres, etc. Then I should create a 2nd table and have only the child ID (from the first table) and the date of attendance, such as 12-25-00. I can relate these two tables with the use of the ID field from both tables.
Is that all there is to it?

You mentioned a synthetic key. Being a novice, using Access on a very basic level, I do not know what a synthetic key is. Please explain.

Again, thanks so much for your reply. Is there any alternative ways to do this database. What about a table with the 52 Sundays of the year and a yes/no field for each child? Being too much a detailed person, I am just asking,

 

[culshaja]

Yes it is that simple.

You could use a row with 52 yes/no fields but it takes up guaranteed space in a database and it isn't properly normalised. If the child doewsn't attend then you do not use space in the database for the second table. By splitting the data this way it is much easier to report on using date ranges and also to create pivot queries.

A synthetic key is one which is unique - such as an autonumber - but in no way relates to the data, it is a synthetically created way of uniquely identifying a row - that is what an autonumber is. James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[clem]

Thanks so very much. I really appreicate your time in helping me.