Thread: Need Subforms?
View Single Post
  #5  
Old April 7th, 2005, 08:25 PM
AccessRookie
external usenet poster
 
Posts: n/a
Default

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

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.

--
Steve Schapel, Microsoft Access MVP

AccessRookie wrote:
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/pr...3208&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 =)