Summing Text Box in subform with actual table columns...

[Guest]

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 you all to review and give me suggestions.
Sincerely,
AccessRookie =)

 

[John Nurick]

You're asking for more work in one go than us volunteers are normally
able or willing to deliver. It's usually better to focus on a single
question in each message, and to post each message in the most
appropriate group (e.g. questions about table design shouldn't go in the
formscoding newsgroup). Here are a few comments:

1) At first glance the table structure seemed reasonable. Closer
examination shows several things that don't make sense to me - but I
don't know the business practices you are modelling. If you have doubts
about the structure you should resolve them *before* trying to design or
code your forms.

The most obvious problem is the seven "hours" fields. Their existence
means that you are *not* in 3NF: that would require the details table to
have fields like EmployeeID, AccountID, DateWorked, HoursWorked (and
probably no more than those).

Having those seven fields will complicate all your queries. On the other
hand, it makes it much easier to build the user interface for data
entry. You need to think hard about that. Also, these fields are
integers. Do you really only ever charge whole hours?

2) If you have tables with 1:M relationships, such as tblTimeCard and
tblTimeCardHours, display and data entry are usually best handled with a
form bound to the "one" table with a subform bound to the "many" table.
Trying to use a monolithic form tends to make things clumsy for the user
and very complicated for the programmer.