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
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: