calculate and store value

R

RaunLGoode

Registered User.

Local time

Today, 12:51

Joined

Feb 18, 2004

Messages

122

• Feb 18, 2004

• #1

I am working on a form for time entry and I want to generate subtotals to reduce data entry errors.
Using the "properties-->Data-->Control Source" for a text box I can either add the values in other text boxes OR write the value in the text box to a table, but I don't know how to do both. I really need to sum the values AND write the sum to a table.

I know this is a rookie question, but it's got me stumped. Can anybody out there help?

 

T

theprez

Registered User.

Local time

Today, 18:51

Joined

Nov 8, 2001

Messages

140

• Feb 18, 2004

• #2

The rule I try to use for fields is if I can create a total or value, don't store the value. If you need a total, use a sub form to add values. As the users enter values on the subform, you can have a field on the main form display totals. The Northwind Sample Database has a good example of this on the Order Form.

 

R

RaunLGoode

Registered User.

Local time

Today, 12:51

Joined

Feb 18, 2004

Messages

122

• Feb 18, 2004

• #3

I agree with that concept too, in fact, that is what I had originally intended, but when I began to create the report that will be printed and used as the hard copy time sheet (signatures, legal issues, etc.) I have to recreate these sub totals and totals all over again, which also seems contrary to good practice. That and a basic desire to understand expression builder and the control source property made me reconsider adding these subtotal and total fields.

 

T

theprez

Registered User.

Local time

Today, 18:51

Joined

Nov 8, 2001

Messages

140

• Feb 18, 2004

• #4

I hear you, but just like you used a subform to total on the form side, you can use the sub report on the report side.

Also, you can use the =sum[Fields] as the control source for your total field.

 

R

RaunLGoode

Registered User.

Local time

Today, 12:51

Joined

Feb 18, 2004

Messages

122

• Feb 19, 2004

• #5

Prez,
Thanks for pointing me in the right direction. I think I understand, but I would like to look at this Northwind sample database. Where is it? I am still curious, is there a way to perform a calculation and then save the result to a table? Am I just writing the expression in the control source wrong?

 

R

Rich

Guest

• Feb 19, 2004

• #6

If you store a calculated value you cannot ensure its integrity, Northwind is in the sample folder

 

T

theprez

Registered User.

Local time

Today, 18:51

Joined

Nov 8, 2001

Messages

140

• Feb 19, 2004

• #7

Like Rich said, its in the sample folder. I also believe you can download from Microsoft.

 

Mile-O

Back once again...

Local time

Today, 18:51

Joined

Dec 10, 2002

Messages

11,316

• Feb 20, 2004

• #8

RaunLGoode said:

Is there a way to perform a calculation and then save the result to a table?

Click to expand...

There is but, like thePrez and Rich, I would recommend against it.

The reason(s)?

• it violates Third Normal Form (3NF);
• it can create inconsistencies in data;

The reasons are against creating what's referred to as a non-key dependancy in database design. If you have values and need to perform a calculation on them then alway perform the calculation; never store the result as you could store the result, change one of the values and - instantly, the stored result is incorrect. This affects any reporting you may wish to do.

Whereas, if you calculate at runtime you can be sure that your data is always summed correctly, and be happy in the knowledge that you are not wasting space within your database.