Updating a Form field based on another field (1 Viewer)

[Dilbert99]

Hi,
I have a time card data entry form which is based on a query. On the same row of the continuous form I have created several unbound fields which I want to calculate values based on other fields' data. I plan to then use total calculation fields in the footer to display totals. I can't get the syntax to work correctly to do the following:

If TimeCode field = "H", then take the value from the TimeDuration field and copy it to the temporary form field called Holiday Hours, so that I can then sum the Holiday Hours in the Footer.

I would appreciate anyone's help with this.

My objective is to calculate Holiday Hours, Vacation Hours, Sick Hours, Overtime Hours, Regular Hours for the detailed time records from the TimeEntry table and build a separate table of those values for the payperiod.
:banghead:

 

[Micron]

Not really clear what you're doing. If you have time value fields in a form (e.g. Holiday, Vacation, SickLeave, etc.) that are bound I don't see the need for having unbound calculated fields. Can you not just sum the various time fields in the footer? DSum function is what you usually use to total form fields by referring to the fields and not the form control names (which may or may not be the same names).

 

[Dilbert99]

Micron, thank you so much for the prompt reply.
Maybe it will help if I list the table for the time entry and a separate table for the Time Summary. Think as if I'm entering time that an employee worked for 1 week:
32 hours regular time
8 hours vacation,
2 hours overtime.

The time entry screen is based on time entry, for example:
Sunday: login 8am, logout for lunch 12N, log back in after lunch, log back out for end of day, etc. etc.d

Here is the time entry form:

Payperiod, Employee ID, Date, Timecode (H=Holiday, S=Sick, V=Vacation, otherwise, theyare productive Hours), Login Time, Logout time. All of these fields are in a table.

Then, on the same record row, I am adding additional unbound fields that will conditionally capture hours, based on some logic. I plan to then total these unbound fields in the footer, and link the footer to another form, which will be tied to the TimeSummary table.

TimeSummary table will look like this:

PayPeriod, Employee, Reg_Hrs, Vac_Hrs, Sick_Hrs, Hol_Hrs, Overtime_Hrs.

I'm sure there is a better way to do this, but I'm not a very good programmer, so I like to limit my design to Queries, Form calcs, Macros, etc. I do use some small VBA for events, but that's about it.

Does this help?

 

[Micron]

It seems like you're over complicating things. If I understand the situation, you can get by with tblEmployee and a table for time transactions - same as you would have one for a purchase order and one for order line items. In your case, the employee PK value (primary key) would be the FK (foreign key) value in time transactions. What would not be completely normalized would be to have the date and transaction types as well (V, H, whatever) as fields. Thus there may be several records for the same date for one employee where most of the time type fields are empty since they don't apply. The disadvantage is quite evident if you need to add a new type as it means altering table and query design. That is a sure indicator of insufficient normalization.

To completely normalize the design you'd still have tblEmployee and tblTimeTrans as before, but there you only record the date, Empl_fk, TransType_fk and a value for the trans type. TransType comes from a new table that lists only the transaction type and maybe a description field. Its PK becomes the FK in tblTimeTrans. Now you have all time values in a column with fields that identify who, when, how much and what type. You sum all V values for employee 34 between two dates using whatever method is best (query, DSum). If creating relationships, there is no need to join trans type as it's just a feeder for an unbound combo on the form.

Maybe you would benefit from a search on normalization if that doesn't make sense. It may be that that is why you're taking your current approach.