updating table

[cochise]

I have a table with primary key TechNum and DateWorked. For simplicity's sake, there are two other fields, TimeWorked and Sales. I have a TimeCard module, in which the user enters start time and end time and some other info. One of my queries returns HoursWorked. Then I have a form in which a user types in the daily information, including Sales. I want the HoursWorked to be saved in the table DailyPerformance. Then, when the 2nd user goes to enter daily information and he enters the TechNum and DateWorked the HoursWorked would already be there so he wouldn't need to look it up and rekey it in. Any help would be greatly appreciated?

 

[gusbrunston]

Hi, Cochise:

The gods of "normalization" insist that calculated values not be stored. 'TimeWorked' or 'HoursWorked' is calculated from the factors 'StartTime' and 'EndTime'. It can be recalculated for each query, form or report that needs it.

The rationale of this rule is based on the possibility that the original value of 'StartTime' or 'EndTime' could be changed, and that change should be reflected in future calculations.

Don't shoot the messenger (nor the piano player either!) Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[cochise]

Well that makes perfect sense. Is there a way to put a query in a control on a form or should I create a function to retrieve the data?

 

[sailorjwd]

Put in a dummy control and put your calculations in the control source property starting it with an equal sign. Example

= [field1] - [field2]

Joe.

http://www.blueclaw-db.com/

 

[gusbrunston]

For each form or report, you can do a calculation in the control or in the query itself.

In a query, you can enter a calculated field, e.g.,:

HoursWorked: [EndTime] - [StartTime]

or, enter the data source for a control 'HoursWorked' on a form or in a report as

=[EndTime]-[StartTime]

I've not done calculations with time--I just suppose that EndTime - StartTime will give you HoursWorked. You may have to experiment with the formatting. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

Hi, again.
I just searched under "calculate time" and found a thread that said you have to save the "time" as a double precision number. You may want to look at this: thread181-12491 Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

Hi, once more:
My earlier posts assumed a simplicity that doesn't seem to exist in calculating elapsed time. Here is a Microsoft FAQ that seems to me to be the best reference to calculating EndTime - StartTime.

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210604

Although the ';en-us;Q210604' doesn't show in blue, it is still part of the URL, and you'll have to include it.

Cheers Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com