Need advice building a workforce and attendance database (1 Viewer)

mrr010

New member
Local time
Today, 23:37
Joined
Sep 15, 2020
Messages
5
Hi there, first of all I have no prior experience in building a database. The reason that I am thinking of building one is that excel files that my company is using are tedious, difficult to read and manage, and unable to meet our demands.

My work:
We closely keep track of a few hundreds to a thousand of temporary staff and contract staff, typically their title/rank, department, team, team manager, location of work, rotation/secondment, contract dates, qualification dates, daily attendance (in hours) and etc. Not only do we list out their current status of the above information, but also the history of most records i.e. complete history of work location, when did they change team and for how long did they work in that team, and etc. In addition, we are also keeping track of the history of some vacancies i.e. who took this vacancy, how long did he work for, who took his position after he moved, is the vacancy available/unavailable and for what reason, and etc.

Some of my duties are to report monthly on staff performance/attendance, calculating average attendance (in hours) per week, making sure they do not cross a threshold, and to monitor the current workforce allocation. As you may imagine, we are continually expanding our excel tables in both rows direction and columns direction in order to achieve the above purposes.

In the current practice, I receive copies of attendance sheet, which is designed by finance, every month. I then use a VBA macro to capture and to format the daily attendance data from those sheets. The generated output is in this format:

Employee no. | Name | Rank | Dates | Dates | ....
10001 | XXX | Temp XXX | 7.50 (in hours) | 0.00 (in hours) | .....
10002 | YYY | Temp XXX | 7.00 (in hours) | 8.50 (in hours) | ....

Dates are typically from the 26th of last month to the 25th of this month, but that will depends on finance dept.
I will then copy each row of data into our master excel file for analysis

For now, I would like to design an Access database for trial to do the attendance reporting job but I am not sure about the structure of tables, how they should be related and how to develop proper queries/report.

I would like to be able to:
1. easily import the data from my monthly VBA generated attendance sheet.
2. calculate a weekly attendance (in total hours) for every staff in the list
3. report their weekly attendance for a given range (eg. from 30/8 - 3/10, last 5 weeks)
4. alarm me if any staff exceed a predefined threshold (some threshold will depends on staff rank while some depends on staff preferences)

Appreciate any advise on building this Access database:
[Table Name]
=[Field]

Staff_List
=Employee no. (Primary key)
=Name
=Rank
=Contract Dates
=etc (similar to my master excel files)

Attendance_Records
=Employee no. (Foreign key)
=Date
=Attendance (in hours)

Report/Queries to show 5 weekly attendance:
=Employee no.
=Name
=Attendance(in hours) for the week 30/08/2020 - 05/09/2020
=Attendance(in hours) for the week 06/09/2020 - 12/09/2020
=Attendance(in hours) for the week 13/09/2020 - 19/09/2020
=Attendance(in hours) for the week 20/09/2020 - 26/09/2020

Example:
10001 | XXX | 17.00 | 21.00 | 7.50 | 17.00
10002 | YYY | 00.00 | 7.00 | 7.50 | 00.00
 
Last edited:

vhung

Member
Local time
Today, 07:37
Joined
Jul 8, 2020
Messages
159
Attendance_Records
=Employee no. (Foreign key)
=Date
=Attendance (in hours)
add 2 fields
>time of log in and time of log out
>in order to determined "Attendance (in hours)"
 

mrr010

New member
Local time
Today, 23:37
Joined
Sep 15, 2020
Messages
5
add 2 fields
>time of log in and time of log out
>in order to determined "Attendance (in hours)"
I do not have access to log in and log out time. The attendance is manually written into the finance worksheet.
The "Attendance (in hours)" should be directly imported from the VBA generated output. Thanks.

For instance, this record from my VBA output:
10001 | XXX | Temp XXX | 7.50 (in hours) | 0.00 (in hours) | .....
should easily import into this table which store all staff attendance records:
Attendance_Records
=Employee no. (Foreign key)
=Date
=Attendance (in hours)

I imagine this table will fill with such records:
Attendance_Records
10001 | 26/08/2020 | 7.50
10001 | 27/08/2020 | 0.00
....
10002 | 26/08/2020 | 7.00
10002 | 27/08/2020 | 8.50
...
10001 | 26/09/2020 | 0.00
10001 | 27/09/2020 | 7.00
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:37
Joined
Oct 29, 2018
Messages
13,225
Hi. Welcome to AWF!

What you don't want to do is simply copy your Excel structure into Access. That will not help you work better. If you're not familiar with the term "database normalization," then I would suggest you read up on it first.
 

vhung

Member
Local time
Today, 07:37
Joined
Jul 8, 2020
Messages
159
I do not have access to log in and log out time. The attendance is manually written into the finance worksheet.
The "Attendance (in hours)" should be directly imported from the VBA generated output. Thanks.
look at your num2
2. calculate a weekly attendance (in total hours) for every staff in the list
> your "Attendance (in hours)" if this refers to one day
may that be on your report sumtotal "(in total hours)"
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 09:37
Joined
Feb 28, 2001
Messages
18,364
First and foremost, if you are in the "trying to build" stage, you need to do just a little reading to keep yourself from going off the deep end. Read some articles on Normalization. If you limit yourself to this forum, you can just search for the word "Normalization" and get a bunch of hits. If you choose to search the internet, you want to search for "Database Normalization" because in the general web, there are several kinds of normalization. If you do a web search, first read articles originating from .EDU sites because the .COM sites often have something they want to sell you. Once you feel more comfortable with the concepts, though, you can hit the .COM sites and find good articles.

You are building a model of your business. You will have to build tables for the entities of your business and will then need to decide how they relate to each other. Therefore, your first goal will be to identify your business rules and data flow so that you can draw a diagram of how things work. If you cannot make such a diagram and have it make sense, you are not ready to do anything in Access yet.

You have to understand that Access is a tool of the mind. It helps you build things just like a power drill helps a carpenter build things. But it is the carpenter who does that building - from blueprints or drawings or some other plans. A carpenter without plans cannot build much. Similarly, without some kind of plan of where you are trying to go with your project, you won't be able to do much. Access will let you build something that will do everything you named in your initial description. But you have to decide where you are going, what you want to see, ... everything about what you want. Because YOU are the subject-matter expert. Access is just a power-tool for database building.
 

mrr010

New member
Local time
Today, 23:37
Joined
Sep 15, 2020
Messages
5
Hi. Welcome to AWF!

What you don't want to do is simply copy your Excel structure into Access. That will not help you work better. If you're not familiar with the term "database normalization," then I would suggest you read up on it first.
Hi, thanks for your reply.
Yes, I dont want my Access structured like Excel that is exactly why I am building a new database. I am thinking of ways to populate my VBA generated data into the appropriate fields/tables while importing but I am not familiar with the ways/tools available.
 

mrr010

New member
Local time
Today, 23:37
Joined
Sep 15, 2020
Messages
5
look at your num2
2. calculate a weekly attendance (in total hours) for every staff in the list
> your "Attendance (in hours)" if this refers to one day
may that be on your report sumtotal "(in total hours)"
"Attendance (in hours)" is a day to day data. One staff, one day, one record. If the design of "Attendance_Records" is fine, then for 1,000 staff, the table will have 365,000 records per year. Therefore I will need a method to calculate a weekly sum (adding 7 records) for each staff, should I add a calculated field in another table to store the weekly sum? Is it done by query with some sort of Datepart expressions? What are the ways/tools or keywords that I can try to play with? Thanks a lot.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:37
Joined
Feb 19, 2002
Messages
30,120
Although 365,000 rows in a single table is more than your typical first Access database, it is well within the capabilities of Access especially since this "transaction" table will be very narrow. i.e. it will have a lot of rows but few columns. Also, each person will most likely work only 5 days per week and so the count will be closer to 260,000.
TimeID (autonumber, PK)
PersonID (foreign key to the table that holds the people)
WorkDT (date for the hours)
WorkHours (hours worked)

Given the size of this table, I would suggest building the app using good client/server techniques so that in 2-3 years, you can convert the data to SQL Server. If you only need the detail for a couple of years, you can archive to a weekly table that is identical in format to the daily table except that WorkDT would be weekStartDT and the WorkHours would be the sum of hours for the week. This would be 50,000 per year.

New rows could be added to this table each week by linking to the finance spreadsheet and appending the data to your work hours table. Unless you need some other information for each person, all your reporting could be done from this table.
 

vhung

Member
Local time
Today, 07:37
Joined
Jul 8, 2020
Messages
159
"Attendance (in hours)" is a day to day data. One staff, one day, one record. If the design of "Attendance_Records" is fine, then for 1,000 staff, the table will have 365,000 records per year. Therefore I will need a method to calculate a weekly sum (adding 7 records) for each staff, should I add a calculated field in another table to store the weekly sum? Is it done by query with some sort of Datepart expressions? What are the ways/tools or keywords that I can try to play with? Thanks a lot.
very important data banking
>if for this year you can make SplitTable for that as your server for 2020 attendance
>well for 2021 separate SplitTable set on your link table manager, i used this method cause i also have data banking over 10k
>on the run your 2020 SplitTable for 2021 is unlinked already but you can simply link it set on your link table manager again if you need to browse 2020
>"weekly sum (adding 7 records)" that's fine;
>"should I add a calculated field in another table to store the weekly sum?: if you can do it that's better, but could that be sum on QueryTable
 

mrr010

New member
Local time
Today, 23:37
Joined
Sep 15, 2020
Messages
5
Although 365,000 rows in a single table is more than your typical first Access database, it is well within the capabilities of Access especially since this "transaction" table will be very narrow. i.e. it will have a lot of rows but few columns. Also, each person will most likely work only 5 days per week and so the count will be closer to 260,000.
TimeID (autonumber, PK)
PersonID (foreign key to the table that holds the people)
WorkDT (date for the hours)
WorkHours (hours worked)

Given the size of this table, I would suggest building the app using good client/server techniques so that in 2-3 years, you can convert the data to SQL Server. If you only need the detail for a couple of years, you can archive to a weekly table that is identical in format to the daily table except that WorkDT would be weekStartDT and the WorkHours would be the sum of hours for the week. This would be 50,000 per year.

New rows could be added to this table each week by linking to the finance spreadsheet and appending the data to your work hours table. Unless you need some other information for each person, all your reporting could be done from this table.
Thank you for your reply. Our staff work in shift 24/7, so I must record 7 days per week, but as you have pointed out, this size of data should be well within capabilities of Access. I will store about 2-3 years of daily attendance data for audit and analysis purposes. After that period, some of these temp staff, will change to contract/permanent staff, and by then, I won't need their records anymore. However your suggestion on making a weekly sum table is a great idea.

So I will have a "staff table", a "daily attendance table" and a "weekly attendance table".
[Staff Table]
Employee_id (Primary Key)
Rank (Short Text)
and etc.

[Daily Attendance Table]
Daily_attendance_id (Autonumber, Primary Key)
Employee_id (Number, Foreign Key to the staff table)
Att_date (Date/Time)
Att_hours (Number)

[Weekly Attendance Table]
Weekly_attendance_id (Autonumber, Primary Key)
Employee_id (Number, Foreign Key to the staff table)
Att_week_start (Date/Time)
Att_hours (Number)

Now I need a method to append the data from those spreadsheet to the "daily table". And method to calculate "weekly sum" from the "daily table".
For reporting, I will need to combine attendance data with staff information such as name, rank, dept, and etc.
 

arnelgp

error reading drive A:
Local time
Today, 23:37
Joined
May 7, 2009
Messages
10,871
imho, there is no need for the Weekly Attendance table.
the Data from Daily attendance table can be summarised in a Query:

select employee_id, sum(att_hours)
where datepart("ww", att_date) = datepart("ww", date)
group by employee_id;

or

select employee_id, sum(att_hours)
where att_date between #09/13/2020# and #09/19/2020#
group by employee_id;
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:37
Joined
Feb 19, 2002
Messages
30,120
Thank you for your reply. Our staff work in shift 24/7, so I must record 7 days per week
The number of records is dependent on
1. how many people
2. how many days per week they work

24/7 is irrelevant. I didn't say there wouldn't be entries for 7 days per week. I said that EACH person would more likely be working only 5 days per week. If you do shift work and long days, a single person might work 3 12 hour days, 5 8 hour days, 4 10 hour days or whatever. But unless there is some type of crunch, 5 days per week is the likely average max per week for most staff here in the US.

And method to calculate "weekly sum" from the "daily table".
I did NOT suggest creating a "current" weekly table. The suggestion was to create a weekly table for ARCHIVED data to reduce its size if you want historical reporting for more than a couple of years. It is poor practice to store the same data in multiple tables. The only time that is done is in a data warehouse and is valid because users are the ones querying a warehouse and you want to make it as simple as possible for them. Also, NOTHING in the warehouse is EVER updated by a user. ALL the data in the warehouse is replaced at one time at some defined interval such as weekly/monthly/yearly depending on your reporting requirements. As arne suggested, summarizing a week's worth of data in a query is trivial. Although you would never hardcode the criteria as arne's samples show. The criteria would always be variable. Usually, you would have start and end dates on a form to control the range. That gives you the flexibility of daily, weekly, monthly, annually. All with the same query.

And my final suggestion was - PLAN ahead for the move to SQL Server. Design with that as the ultimate target BE even though you can start today with ACE without a problem.

To import the spreadsheet data, you would link to the spreadsheet. This link would be permanent and you could refresh it each week if the downloaded spreadsheet file name were always the same. Otherwise, you would relink each week but as long as you don't change the name of the "Access" table created by the link, none of your code or queries will break.

Once the newest version of the spreadsheet is linked, you would run an append query to copy the data from the spreadsheet to the permanent table.

Typically when doing a process like this, I want to take care to not import the same set of data multiple times. In your case, you can probably just add a unique index on the transaction table for Employee_id and Att_date. If you want to go further, you can create a log table where you store the name of the file you imported and use an autonumber as the PK so each import has a "batch" number. You would then use the new batch number as a field in your attendance table so you can tell that row 456,789 came in on batch 397. That also makes it easy to back out an import if something was wrong with it.
 
Last edited:

Users who are viewing this thread

Top Bottom