Need Subforms?

[AccessRookie]

Help me, please! I am putting together an automated Time Card Entry Form. I
have the following fields on this form as follows:
1. Week Ending: automatically populates with "mm/dd/yyyy" date format
from tblPayrollSchedule. This field will insert the correct pay period with
comparing against today's date.
2. Employee Number: combo box that will populate next field, Employee
Name, after user select correct Employee Number.
3. Employee Name: Text Box will appear "LastName, FirstName" order.
4. Description: combo box containing a list of labor description that has
associated fields as follows.
5. Cost Center: Text Box will populate after selecting Description.
6. Acct: Text Box will populate after selecting Description.
7. Category: Text Box will populate after selecting Description.
8. Pay Type: Combo Box to eliminate data entry errors.
9. Allocation: Free form, to type comments.
10. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
11. Regular: totals of regular hours worked.
12. Overtime: totals of overtime hours worked.
13. Vacation: totals of vacation hours taken.
14. Holiday: totals of holiday hours taken.
15. Sick: totals of sick hours taken.
16. Personal: totals of personal hours taken.
17. Total Wk Hrs: total hours for the week for per line of Description.

Here are my tables:
1. tblEmployees:
1) LastName = Text
2) FirstName = Text
3) EmployeeId = Number (Primary Key)
4) EmployeeClass = Text
5) EmployeeType = Text

2. tblAccounts:
1) CostCenter = Number
2) AcctNo = Number
3) Category = Text
4) AcctName = Text
5) AcctDescription = Text (Primary Key)
6) PayType = Text

3. tblPayType:
1) PayType = Text
2) Description = Text

4. tblRegRate:
1) EmployeeId = Number (Primary Key)
2) EmployeeType = Text
3) PayType = Text
4) HourlyRate = Number
5) AnnualRate = Number

5. tblOtRate:
1) EmployeeId = Number (Primary Key)
2) EmployeeType = Text
3) PayType = Text
4) RegHourlyRate = Number
5) OTHourlyRate = Number
6) AnnualRate = Number

6. tblDdRate:
1) EmployeeId = Number (Primary Key)
2) EmployeeType = Text
3) PayType = Text
4) RegHourlyRate = Number
5) DDHourlyRate = Number
6) AnnualRate = Number

7. tblTimecard:
1) LastName = Text
2) FirstName = Text
3) EmployeeId = Number
4) PayPeriod = Date/Time
5) EmployeeClass = Text
6) EmployeeType = Text
7) SAT = Number
8) SUN = Number
9) MON = Number
10) TUE = Number
11) WED = Number
12) THU = Number
13) FRI = Number
14) CostCenter = Number
15) AcctNo = Number
16) Category = Text
17) AcctName = Text
18) AcctDescription = Text
19) PayType = Text
20) Allocation = Text
21) TotalRegHrs = Number
22) TotalOTHrs = Number
23) TotalVacHrs = Number
24) TotalHolHrs = Number
25) TotalSickHrs = Number
26) TotalPersHrs = Number
27) TotalWeekHrs = Numbers

8. tblPayrollSchedule:
1) PayId = Number
2) PayStartDate = Date/Time
3) PayEndDate = Date/Time
4) CheckDate = Date/Time

Here is the layout of my form in this order:
1. Week Ending
2. Employee Number
3. Employee Last Name
4. Employee First Name
5. Description
6. Cost Center
7. Acct
8. Category
9. Pay Type
10. Allocation
11. SAT
12. SUN
13. MON
14. TUE
15. WED
16. THU
17. FRI
18. Total Wk Hrs

Question 1: what code do I need to automatically populate "Week Ending"
field with the correct "CheckDate"
that will compare against today's date and insert into "Week Ending" field?

Question 2: how come the following fields are not populating:
1. LastName
2. FirstName
3. EmployeeClass
4. EmployeeType
5. CostCenter
6. AcctNo
7. Category
8. AcctName

But these fields are populating:
1. EmployeeId
2. PayPeriod
3. AcctDescription
4. PayType
5. Allocation
6. TotalRegHrs
7. TotalOTHrs
8. TotalVacHrs
9. TotalHolHrs
10. TotalSickHrs
11. TotalPersHrs

Question 3: how can I get the 2nd section which starts from "5. Description"
til "18. Total Wk Hrs" to display in multiple rows like a Datasheet format?
Do I need to create a subform? But "Description" field still needs to have a
combo box for user to pick from a list of labor description that will
automatically populate these fields:
1. Cost Center
2. Acct
3. Category

Question 4: If I do need to create subform then how can I link the mainform
to subform and what foreign keys
do I need and where do I put these foreign keys?

Question 5: what formula do I use to calculate "Total Wk Hrs" field? Do I
need to create an actual field in tblTimecard or
can I just get the total from summing with an Expression?

It's been a while since I have done Access development.
Sincerely,
AccessRookie =)

 

[Steve Schapel]

AccessRookie,

I am not really able to specifically answer your questions. But I would
comment that your table design at the moment involves a lot of
redundancy (i.e. the same information being stored in more than one
place), the storage of derived/calculated data (which makes things more
difficult and unnecessarily complicated), and other examples of
unnormalisation (for example the "fields as data" trap of separate
fields for the days of the week). Is there a chance you could review
your table structure?

 

[AccessRookie]

Thanks, Steve for your suggestion. Been busy with other projects to reply
back. With your suggestion, I reviewed table structure and design.
Initially, I started out with 4 tables:
1. tblAccounts
2. tblEmployees
3. tblPayType
4. tblTimecard

As a result of following documentation on Database Normalization Basics from
this site and another site:
http://www.utteraccess.com/forums/printthread.php?Board=53&main=343208&type=post

I restructured the tables and renamed more descriptive and reviewed table
relationships. Here are my tables:
1. tblEmployees:
1) pkeyEmployeeId = AutoNumber
2) strLastName = Text
3) intStaffNumber = Number
4) fkeyEmployeeTypeId = Number

2. tblEmployeeType:
1) pkeyEmployeeTypeId = AutoNumber
2) strEmployeeClass = Text
3) strEmployeeType = Text

3. tblAccounts:
1) pkeyAcctId = AutoNumber
2) intCostCenter = Number
3) intAcctNo = Number
4) intCategory = Text
5) strAcctName = Text
6) strAcctDescription = Text

3. tblPayType:
1) pkeyPayTypeId = AutoNumber
2) strPayType = Text
3) strDescription = Text

4. tblPayroll Schedule:
1) pkeyPayrollScheduleId = AutoNumber
2) intPayYear = Number
3) intPayPeriodId = Text
4) dtmPayStartDate = Date/Time
5) dtmPayEndDate = Date/Time
6) dtmCheckDate = Date/Time

5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayrollScheduleId = Number

6. tblTimecardHours:
1) pkeyTimecardDetailId = AutoNumber
2) fkeyTimecardId = Number
3) fkeyAcctId = Number
4) fkeyPayTypeId = Number
5) strAllocation = Text
6) intSat = Number
7) intSun = Number
8) intMon = Number
9) intTue = Number
10) intWed = Number
11) intThu = Number
12) intFri = Number

Here is the layout of Main Form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name

Here is the layout of Subform in this order:
1. Acct Id
2. Description
3. Cost Center
4. Acct
5. Category
6. Pay Type Id
7. Pay Type
8. Allocation
9. SAT
10. SUN
11. MON
12. TUE
13. WED
14. THU
15. FRI
16. Wk Hrs

I hope this posting will help others facing same issue as me.
AccessRookie =)

 

[Steve Schapel]

AccessRookie,

Thanks for getting back, and congratulations on the good work and the
progress you have made.

The biggest change I would make (and recommend) to what you have now
got, is in the intSat, intSun, intMon, etc fields in the
tblTimecardHours table. Whatever the data in these fields, it should
all be in only one field, with a separate record for each entry, and if
necessary a date and a type field.

 

[AccessRookie]

No, that's not a good suggestion because, I have not posted the format of how
the timesheet looks like, here is the detailed part:

Acct Name Acct # SAT SUN MON TUE WED THU FRI
Vaction 1111.1000 8 8
8 8 8

Each timecard detail has the specified labor allocated to each account
number for the total hours of each day per labor expense. Therefore, it
doesn't make sense in my situation to make it one field. I know I have not
totally described how the timesheet look like in detailed since I can't
attached any files to this message.

But now, I have another issue, please see my revised table structure on
tblPayrollSchedule and new questions:
elp me, please! I am putting together an automated Time Card Entry Database
based on a timesheet and other reports already existed in Excel.
Here are my tables:
1. tblEmployees:
1) pkeyEmployeeId = AutoNumber
2) strLastName = Text
3) intStaffNumber = Number
4) fkeyEmployeeTypeId = Number

2. tblEmployeeType:
1) pkeyEmployeeTypeId = AutoNumber
2) strEmployeeClass = Text
3) strEmployeeType = Text

3. tblAccounts:
1) pkeyAcctId = AutoNumber
2) intCostCenter = Number
3) intAcctNo = Number
4) intCategory = Text
5) strAcctName = Text
6) strAcctDescription = Text

3. tblPayType:
1) pkeyPayTypeId = AutoNumber
2) strPayType = Text
3) strDescription = Text

4. tblPayrollSchedule:
1) pkeyPayrollScheduleId = AutoNumber
2) intPayYear = Number
3) intPayPeriodId = Text
4) dtmPayStartDate = Date/Time
5) dtmPayEndDate = Date/Time
6) dtmCheckDate = Date/Time

5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayrollScheduleId = Number

6. tblTimecardHours:
1) pkeyTimecardDetailId = AutoNumber
2) fkeyTimecardId = Number
3) fkeyAcctId = Number
4) fkeyPayTypeId = Number
5) strAllocation = Text
6) intSat = Number
7) intSun = Number
8) intMon = Number
9) intTue = Number
10) intWed = Number
11) intThu = Number
12) intFri = Number

Here is the layout of Main Form in this order:
1. Pay Period Id --> combo box with primary key from tblPayrollSchedule
and payroll date.
2. Week Ending --> automatically populates with "mm/dd/yyyy" date format
from tblPayrollSchedule. This field will insert the correct pay period with
comparing against today's date.
3. Employee Number --> combo box that will populate next fields (1.
Employee Last Name; 2. Employee First Name) after user select correct
Employee Number.
4. Employee Last Name --> automatically populates when Employee Number is
selected.
5. Employee First Name --> automatically populates when Employee Number is
selected.

Here is the layout of Subform in this order:
1. Acct Id --> combo box containing a list of labor description that has
associated fields as follows.
2. Acct Name --> Text Box will populate after selecting Acct Id.
3. Cost Center --> Text Box will populate after selecting Acct Id.
4. Acct --> Text Box will populate after selecting Acct Id.
5. Category --> Text Box will populate after selecting Acct Id.
6. Pay Type Id --> combo box that will populate Pay Type.
7. Pay Type --> automatically populates when Pay Type Id is selected.
8. Allocation --> Free form, to type comments.
9. SAT --> hours worked for per line of acct id on specific day.
10. SUN --> hours worked for per line of acct id on specific day.
11. MON --> hours worked for per line of acct id on specific day.
12. TUE --> hours worked for per line of acct id on specific day.
13. WED --> hours worked for per line of acct id on specific day.
14. THU --> hours worked for per line of acct id on specific day.
15. FRI --> hours worked for per line of acct id on specific day.
16. Wk Hrs --> total hours for the week for per line of Acct Id.

Question 1: what code do I need to automatically populate "Week Ending"
field with the correct "dtmPayEndDate"
that will compare against today's date and insert into "Week Ending" field?
Currently, user selects from Combo Box(Pay Period Id) then it populates Text
Box(Week Ending).
or
Create a setup form in which the user selects "week Ending" date at
beginning before entering Time Card data.
Then from the setup form, data gets transferred/displayed into Main Form.
If so, can someone assist in writing code that
passes data from one form to another form or save setup form data into its
own table.

Question 2: what code do I need for "Wk Hrs" on subfrmTimeEntry, this column
needs to sum these columns: SAT, SUN, MON, TUE, WED, THU, FRI)?
No need to store total since it is only need to display in data entry form
(subform) and printing report.

Help!!!
Sincerely,
AccessRookie =)

 

[Steve Schapel]

AccessRookie,

The structure of your data (i.e. table design) should be dictated by the
nature of the data itself. Allowing your table design to be influenced
by what you want your forms to look like will often prove to be a
mistake. Having separate fields for each day of the week is incorrect.
Re-designing your form to meet the data requirements would be a better
approach.

As regards your question about the dtmPayEndDate entry, what is the
relationship between dtmPayStartDate and dtmPayEndDate? How does the
data get entered into dtmPayStartDate? Is dtmPayEndDate always 6 days
after dtmPayStartDate? If so, you probably only need one of these
fields. If not, please give an example of what you want to have happen.