Question about Access 2007

[Arcadio]

First of all, I know that Access is not the same as Excel, but I still need to know if there is a way that a field can be calculated using data on other fields.

For example, let's say that there are three fields in the database:

Amount 1
Amount 2
Total.

I want the "Total" field to reflect the sum of the "Amount 1" and "Amount 2".

Is there a way to do this dynamically, that is, as soon as a value is entered in Amount 1 or Amount 2, the "Total" field will reflect the sum of the two fields?

Or is this done with a special "Run" command, like running a query, and cannot be done dynamically?

 

[MrChad]

Your best bet is to setup a Table with Amount 1 and Amount 2, then setup a Query that presents both amounts plus the calculated values.

 

[Arcadio]

Your best bet is to setup a Table with Amount 1 and Amount 2, then setup a Query that presents both amounts plus the calculated values.

but the query won't run automatically every time I enter a value in Amount1 or Amount2, right? I need to "run" the query manually to get the total. Is that right?

 

[KB]

but the query won't run automatically every time I enter a value in Amount1 or Amount2, right? I need to "run" the query manually to get the total. Is that right?

Thats true you would need to rerun the query to get the total after updating the value. The question is if you are using Access why are you entering data directly into the table? You should create a form in access for entering the data. If you use a form then you can have events that do rerun the query when you change a value. If you want to enter data directly in a table use Excel.

 

[MrMatt]

but the query won't run automatically every time I enter a value in Amount1 or Amount2, right? I need to "run" the query manually to get the total. Is that right?

Why not do it in a form? Put the two controls linked to the two amounts on the form. Put a third unbound control on the form, make it =Amount1+Amount2 (or whatever the names are). You could even view the form in table view if you want it to look like a spreadsheet.

 

[Nothinman]

Or just use Excel and it's pivot tables to query the database for the values.

 

[Arcadio]

Ok, I created a form. There are three controls in the form: Amount1, Amount2, and Total. I set the Total control source as =[Amount1] + [Amount2]. When I update one of the Amounts, the Total reflects the value automatically. The problem now is that the Total is not saved into the database. What should I do to update the database with the value in the form?

 

[sao123]

Ok, I created a form. There are three controls in the form: Amount1, Amount2, and Total. I set the Total control source as =[Amount1] + [Amount2]. When I update one of the Amounts, the Total reflects the value automatically. The problem now is that the Total is not saved into the database. What should I do to update the database with the value in the form?

doing so would be "bad" by database principals.
calculated values are supposed to be calculated on the fly every time they are needed, not stored.

im not sure what you are trying to do, why do you feel the need to waste database space by storing something which will be calculated every time it is accessed?

 

[Arcadio]

doing so would be "bad" by database principals.
calculated values are supposed to be calculated on the fly every time they are needed, not stored.

im not sure what you are trying to do, why do you feel the need to waste database space by storing something which will be calculated every time it is accessed?

Ok, I understand that.

This is what I did so far:

I created a table with 3 fields:

Name
Amount1
Amount2

The table will be filled with data as follows:

Name______Amount1_________Amount2
John_______$50_____________$55
Carlos______$34_____________$23
Martha_____$12_____________$28

I would like to generate a query that calculates the TOTAL for each person, in a new column, and a new row at the bottom with all the totals added. It would look something like this:

Name______Amount1_________Amount2_____TOTAL
John_______$50_____________$55_________$105
Carlos______$34_____________$23_________$57
Martha_____$12_____________$28_________$40

_________________________ GRAND TOTAL: $202

What's the best way to do this?

 

[Nothinman]

SELECT Name, Amount1, Amount2, Amount1 + Amount2 AS TOTAL from TABLE

 

[sao123]

This should give you the assistance you need. i did this in access 2007, but the screens are the same for any version > XP.

The summary total at the bottom is actually handled by the reporting feature in access. I'll do one of those later.

 

[Arcadio]

Thanks a lot... that's exactly what I needed.