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.

--
Steve Schapel, Microsoft Access MVP

AccessRookie wrote:
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 =)