3NF & Table Relationships

Help me, please! I am putting together an automated Time Card Entry Database
based on a timesheet and other reports already existed in Excel.
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 fields (1. Employee
Last Name; 2. Employee First Name) after user select correct Employee Number.
3. Employee Last Name: automatically populates when Employee Number is
selected.
4. Employee First Name: automatically populates when Employee Number is
selected.
5. Acct Id: combo box containing a list of labor description that has
associated fields as follows.
6. Description: Text Box will populate after selecting Acct Id.
7. Cost Center: Text Box will populate after selecting Acct Id.
8. Acct: Text Box will populate after selecting Acct Id.
9. Category: Text Box will populate after selecting Acct Id.
10 Pay Type Id: combo box that will populate Pay Type.
10. Pay Type: automatically populates when Pay Type Id is selected.
11. Allocation: Free form, to type comments.
12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
13. Total Wk Hrs: total hours for the week for per line of Acct Id.

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) intPayPeriodId = Number
3) dtmPayStartDate = Date/Time
4) dtmPayEndDate = Date/Time
5) dtmCheckDate = Date/Time

5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayPeriodId = 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 my form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name
6. Acct Id
7. Description
8. Cost Center
9. Acct
10. Category
11. Pay Type Id
12. Pay Type
13. Allocation
14. SAT
15. SUN
16. MON
17. TUE
18. WED
19. THU
20. FRI
18. Wk Hrs

Question 1: Please review table relationships to see if I overlooked any
tables that can be broken down or named its column differently?

Question 2: I have restructured these tables into 3NF, please see my
previous posts under AccessRookie (although before 3/2005, someone used this
ID).

Question 3: 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? Currently, user selects from Combo Box(Pay
Period Id) then it populates Text Box(Week Ending).

Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not
populating?
I think it is my table relationship: tblTimecard & tblTimecardHours.

Question 5: 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.

I need your assistance since sometimes, it just takes another pair of eyes
to review another peers' work. It's been ages since I've done any
development from scratch. Help!!! It's too bad, I can't attach my database
for someone to review it.
Sincerely,
AccessRookie =)

Answered in another group. Please don't post the same message separately
to multiple groups: it wastes people's time.

On Fri, 1 Apr 2005 16:25:04 -0800, AccessRookie
wrote:

Help me, please! I am putting together an automated Time Card Entry Database
based on a timesheet and other reports already existed in Excel.
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 fields (1. Employee
Last Name; 2. Employee First Name) after user select correct Employee Number.
3. Employee Last Name: automatically populates when Employee Number is
selected.
4. Employee First Name: automatically populates when Employee Number is
selected.
5. Acct Id: combo box containing a list of labor description that has
associated fields as follows.
6. Description: Text Box will populate after selecting Acct Id.
7. Cost Center: Text Box will populate after selecting Acct Id.
8. Acct: Text Box will populate after selecting Acct Id.
9. Category: Text Box will populate after selecting Acct Id.
10 Pay Type Id: combo box that will populate Pay Type.
10. Pay Type: automatically populates when Pay Type Id is selected.
11. Allocation: Free form, to type comments.
12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
13. Total Wk Hrs: total hours for the week for per line of Acct Id.

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) intPayPeriodId = Number
3) dtmPayStartDate = Date/Time
4) dtmPayEndDate = Date/Time
5) dtmCheckDate = Date/Time

5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayPeriodId = 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 my form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name
6. Acct Id
7. Description
8. Cost Center
9. Acct
10. Category
11. Pay Type Id
12. Pay Type
13. Allocation
14. SAT
15. SUN
16. MON
17. TUE
18. WED
19. THU
20. FRI
18. Wk Hrs

Question 1: Please review table relationships to see if I overlooked any
tables that can be broken down or named its column differently?

Question 2: I have restructured these tables into 3NF, please see my
previous posts under AccessRookie (although before 3/2005, someone used this
ID).

Question 3: 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? Currently, user selects from Combo Box(Pay
Period Id) then it populates Text Box(Week Ending).

Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not
populating?
I think it is my table relationship: tblTimecard & tblTimecardHours.

Question 5: 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.

I need your assistance since sometimes, it just takes another pair of eyes
to review another peers' work. It's been ages since I've done any
development from scratch. Help!!! It's too bad, I can't attach my database
for someone to review it.
Sincerely,
AccessRookie =)

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Sorry about that and wanted to get a quicker response since I have noticed
some sections there were not that many responses to members' questions.

I fixed my database and found out my table relationships had some flaws
therefore, I had some problems on my data entry form. It's too bad, I can't
paste my table relationship on this site for everyone to see as a sample if
they need help in a similar project.

"John Nurick" wrote:

Answered in another group. Please don't post the same message separately
to multiple groups: it wastes people's time.

On Fri, 1 Apr 2005 16:25:04 -0800, AccessRookie
wrote:

Help me, please! I am putting together an automated Time Card Entry Database
based on a timesheet and other reports already existed in Excel.
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 fields (1. Employee
Last Name; 2. Employee First Name) after user select correct Employee Number.
3. Employee Last Name: automatically populates when Employee Number is
selected.
4. Employee First Name: automatically populates when Employee Number is
selected.
5. Acct Id: combo box containing a list of labor description that has
associated fields as follows.
6. Description: Text Box will populate after selecting Acct Id.
7. Cost Center: Text Box will populate after selecting Acct Id.
8. Acct: Text Box will populate after selecting Acct Id.
9. Category: Text Box will populate after selecting Acct Id.
10 Pay Type Id: combo box that will populate Pay Type.
10. Pay Type: automatically populates when Pay Type Id is selected.
11. Allocation: Free form, to type comments.
12. Days of Week: Sat, Sun, Mon, Tue, Wed, Thu, Fri
13. Total Wk Hrs: total hours for the week for per line of Acct Id.

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) intPayPeriodId = Number
3) dtmPayStartDate = Date/Time
4) dtmPayEndDate = Date/Time
5) dtmCheckDate = Date/Time

5. tblTimecard:
1) pkeyTimecardId = AutoNumber
2) intStaffNumber = Number
3) fkeyPayPeriodId = 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 my form in this order:
1. Pay Period Id
2. Week Ending
3. Employee Number
4. Employee Last Name
5. Employee First Name
6. Acct Id
7. Description
8. Cost Center
9. Acct
10. Category
11. Pay Type Id
12. Pay Type
13. Allocation
14. SAT
15. SUN
16. MON
17. TUE
18. WED
19. THU
20. FRI
18. Wk Hrs

Question 1: Please review table relationships to see if I overlooked any
tables that can be broken down or named its column differently?

Question 2: I have restructured these tables into 3NF, please see my
previous posts under AccessRookie (although before 3/2005, someone used this
ID).

Question 3: 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? Currently, user selects from Combo Box(Pay
Period Id) then it populates Text Box(Week Ending).

Question 4: how come "tblTimecardHours.fkeyTimecardId" field is not
populating?
I think it is my table relationship: tblTimecard & tblTimecardHours.

Question 5: 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.

I need your assistance since sometimes, it just takes another pair of eyes
to review another peers' work. It's been ages since I've done any
development from scratch. Help!!! It's too bad, I can't attach my database
for someone to review it.
Sincerely,
AccessRookie =)

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.