Using MS Access/Access 2010 Attendance register subform
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
Date of Birth
Building attends (this allows multiple values)
Table two is a locations table with
Table three is a register table with
ClientID - Linked to the ID on client table
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:
ClientBuildingID (PK Autonumber)
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?
No such thing as a "last" question ;) But you should start a new question as allexperts limits followup.
Yes, I would create a table:
DateRangeID (PK Autonumber)
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