I have restructed my original tables into 3NF and added relationships. But I think I am missing a few more items to go on to next step. 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!!! Is there any way, I can attach my zipped database? It is 217KB zipped but this site only allow 100KB attachments.
Sincerely,
AccessRookie =)

ques 1,2,3: You have a little more work to do on normalization.
. There should not be three dates in tblPayrollSchedule. Usually PayEndDate is the only date stored since the others can be calculated from it.
. Time card hours should not have 7 days in one record. Each day should be its own record so you need an additional table. Also, the accounting information is at the lowest level, usually below day. That way you can allocate 2 hours to acct1 and 3 hours to acct2 on the same day.

As far as the column names go, prefixing them as you have is rarely done. Variables may be prefixed but table fields generally are not. Also, good practice is to use the same name for the foreign key as its parent primary key.
ques 4: You should use a form for employee with a subform for the timecard info.
ques 5: If you normalize the table, you can just sum the says with an aggregate function.

Pat,

Thanks for your suggestion on normalizing tables since others (other sites) also gave me the same suggestion. :) Yes, there needs to be 3 dates in tblPayrollSchedule table since I am making this project as easy as possible for future maintenance and changes for queries/reports. Yes, in my situation, it does make sense to have 7 days in one record for each account name selected in timecard detail section to make it easy to pull the records for future reference. 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 learned the naming column & table convention by one of Microsoft's sample database provided on their site. I also changed foreign key names to match with primary key names since it does make sense with your suggestion so I won't be confuse in the future when needing to make changes.

Yes, I created a main form and subform. Hmmm...I am not sure on which Text Box property I should be summing these columns:
=[intSat]+[intSun]+[intMon]+[intTue]+[intWed]+[intThu]+[intFri]

or should I be using another formula?

Here are the following Text Box property:
1. Before Update
2. After Update
3. On Dirty
4. On Undo
5. On Change
6. On Enter
7. On Exit
8. On Got Focus
9. On Lost Focus
10. On Click
11. On Dbl Click
12. On Mouse Down
13. On Mouse Up
14. On Key Down
15. On Key Up
16. On Key Press

Here are restructured 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.

I attached table relationship and sample timesheet form that has been converted into data entry form in Access. Help!!!
Sincerely,
AccessRookie =)

You are not going to like this...

Listening to Pat Hartman is the easy path.

What you are attempting, is creating difficulties (and future headaches).

Sorry, storing days as repeating groups does not make sence. It will make it more difficult to pull records for future reference, and will create future headaches where you'll have to create non sql (read manual/programattic/less effective/more static) solutions to retrieve information. It will also be much harder to maintain, provide ad hoc reports, or reporting in general (because you can't rely on SQL to do the job)

One of these, you've alredy presented field1+field2... - what happens if one of them is Null?

Question 1 is answered by Pat Hartman - don't store them, calculate on the fly
Question 2 is answered by Pat Hartman - normalise the table and use standard functionality

I told you how to structure the application so you don't need code but you seem to know better. Good Luck :)

Interesting thread here.
I have to disagree (slightly) with PAT on the tblPayrollSchedule. We have found that when we let our Payroll people setup the schedule, they have an easier time of they can enter all the dates for some reason, the application could handle this, but being vendor supplied, it is what it is. Also we have DueInDate and DueOutDate which tracks when all the data has to be in, and when we have to send it to the service provider. Having the start and end dates (although start is easy to calculate) besides the payroll clerk not being as confused, accessing the payroll schedule for other needs is simplified. Our users write their own reports and seem to be able to do so with less I.T. intervention using this method. It could be circumvented with views (Sql Server in our case), but for a single date field, the benefits of the storing the field outweigh the disadvantages of true normalization.

How ever I would take Pat's suggestion on storing the 7 days in your table, she is right on the money with that one. It may seem easier up front to access the data this way, but I bet you will find in the long run it is much more diffecult. I strongly urge you to rethink that approach.