Originally Posted by
NickCarroll
Because what you refer to as 'repeating groups' relate to 'days of the week', there's only ever going to be a maximum of 7 instances.
A spreadsheet it typically "short and wide".
A relational database is typically "tall and narrow".
I would guess that you also have a date field "WkBeginning"?
Lets say that you have a job (the ID is 10) that began on Wed and ends on the following Wed. Your table looks like
wgtID
|
wgtJobID |
wgtJobDesc |
wgtQty |
wgtMoHrs |
wgtMoRate |
wgtTuHrs |
wgtTuRate |
wgtWeHrs |
wgtWeRate |
wgtThHrs |
wgtThRate |
wgtFrHrs |
wgtFrRate |
wgtSaHrs |
wgtSaRate |
wgtSuHrs |
wgtSuRate |
wgtSaRateWgBegin |
100 |
10 |
Not needed |
1 |
|
|
|
|
8 |
25.00 |
8 |
25.00 |
8 |
25.00 |
|
|
|
|
10/15/2017 |
154 |
10 |
is in tblJobs |
1 |
8 |
25.00 |
8 |
25.00 |
|
|
|
|
|
|
|
|
|
|
10/22/2017 |
A normalized table:
wgtID |
wgtJobID |
wgtQty |
wgtDate |
wgtHrs |
wgtRate |
100 |
10 |
1 |
10/18/2017 |
8 |
25.00 |
101 |
10 |
1 |
10/19/2017 |
8 |
25.00 |
102 |
10 |
1 |
10/20/2017 |
8 |
25.00 |
103 |
10 |
1 |
10/23/2017 |
8 |
25.00 |
104 |
10 |
1 |
10/24/2017 |
8 |
25.00 |
|
|
|
|
|
|
A query would look like:
Code:
SELECT [wgtHrs]*[wgtRate] AS TDailyAmt, tblWagesTable.wgtDate, tblWagesTable.wgtJobID_FK
FROM tblWagesTable
WHERE (((tblWagesTable.wgtDate) Between #10/18/2017# And #10/24/2017#) AND ((tblWagesTable.wgtJobID_FK)=10));
A totals query to get the total cost would be
Code:
SELECT Sum([wgtHrs]*[wgtRate]) AS TDailyAmt, tblWagesTable.wgtJobID_FK
FROM tblWagesTable
WHERE tblWagesTable.wgtDate Between #10/18/2017# And #10/24/2017#
GROUP BY tblWagesTable.wgtJobID_FK
HAVING tblWagesTable.wgtJobID_FK = 10;
Don't even want to think about what a query would look like with your design..
Which table design do you think would be easier to calculate a total cost from? (Not sure how field "wgtQty" fits in )
Now think about the job lasting 6 weeks.
Originally Posted by
NickCarroll
Typically, our staff might work the same hours on Mon/Tue/Wed/Thu/Fri at the same rate of pay however, it's not unusual for people to work M/W/F only or for pay rates to be different on different days. In addition to recording the hours worked on different days of the week and the rates of pay for those shifts, the table also records the annual holiday entitlement for each person of this pay type and then goes on (via the query) to calculate the employees weekly pay, the payroll taxes, holiday pay and eventually a totally weekly contract cost.
Maybe the table "tblWagesTable" need an "EmpID_FK" field also???
Could also have another field for PayType - ST, OT, Holiday, etc.
Would be easy to add another PayType with the normalized table. With your table(spreadsheet) design, adding another field would require changing the table, queries, form, report and code.
Anyway.... that's the theory....