Thread: Need Subforms?
View Single Post
  #4  
Old April 7th, 2005, 12:08 AM
Steve Schapel
external usenet poster
 
Posts: n/a
Default

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 =)

Ads