You are here:

Using MS Access/Access 2010 Attendance register subform

Advertisement


Question
QUESTION: Hi,

I wonder if you can help I am trying to do an attendance register for a a number of buildings.

I have got 3 tables.

Table one is a clients table with
(PK)ID
Name
Date of Birth
Sex
Building attends (this allows multiple values)

Table two is a locations table with
(PK)ID
Building

Table three is a register table with
(PK)ID
ClientID - Linked to the ID on client table
Attended
Time arrived
Time finished.

What i am trying to do is create amaster form that just asks the user for the Building they are working in, once they select a building a subform is then populated with the list of clients so the end user can tick that they have attended and type in the time of arrival and departure.

Are you able to give me any advice on the best way for this. I can easily do this from a person perspective so that the main form is a client and have a subform for their attendance but this is my plan b.

ANSWER: First I see a couple of problems with your structure. Don't accept the default ID as name for your PKs. This will confuse you later. Name them tablenameID, ClientsID, LocationID, RegisterID. Also don't use spaces in Object names. Use DateOfBirth or Date_Of_Birth. People names shouldn't be a single field. You should break them into components. I generally use 5 fields for people names; Salutation (Mr, Ms, Etc.) First, Middle, Last, Suffix (Jr, Sr, etc.).

I would also, not use a multi-value field for Building attends and definitely not in the client table.

I would have a form with unbound controls in the header for the user to enter/select their userID, and the building. Then bound controls, a combobox to select the client, filtered by the clients in that building, and controls to enter the time. Also hidden controls to pick up the user and building.



---------- FOLLOW-UP ----------

QUESTION: Cheers for the advice, the only issue i'm coming across is the clients attend more than one building for services, so which was the reason i used the multi value option within the client table but i didn't realise how much of a pain it creates.

ANSWER: That shouldn't be a problem if you use a Junction table. You have a many to many relationship. One client can attend multiple buildings and one building can service multiple clients. So you need a junction table:

tjxClientBuilding
ClientBuildingID (PK Autonumber)
ClientID (FK)
BuildingID (FK)

So there will be a record in this table for each combination of client and building. Some clients may only have one record, others multiple records.

This will then allow you to filter for the clients in a specific building.



---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Apologies one last question, I am trying to create a report based on the register table.  the issue i have is we have specific date ranges for activities so for example period 1 will be 04/04/2015 - 01/05/2015 Period 2 02/05/2015 - 29/05/2015 and so on for 13 periods.  I would like to have the user select the period number and then a report is generated for that date range.  I've been on google but there is anything that really matches what i was after.  Would you recommend another table?

Answer
No such thing as a "last" question ;) But you should start a new question as allexperts limits followup.

Yes, I would create a table:

tluDateRanges
DateRangeID (PK Autonumber)
StartingDate
EndingDate

Then add a FK for date range to your table. In your queries, join the Date Range Table on DateRangeID and set the criteria to:

BETWEEN StartingDate AND EndingDate  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.