How to calculate a field value

**Mihail** · Jan 16 '12, 07:34 AM

First of all, I think that your database has a (very) poor design.
That because in the table Export you have four columns that represent the same object: country.
What happen if you need to add a new country ? You will need to change the design for table Export by adding a new field, isn't it ?
But, in a good database, is no need to modify the design for tables in order to add new dates.

So, my advice is to design a new table, tblCountries, with two fields: ID_Country, CountryName. Of course you can add more other fields as properties for a country(Preside ntName, Capital and so on)
Populate this table with countries names: USA, UK, France, Italy (for now) and, if you need more countries, add new countries to this table.

Then, in table Export, remove the fields UK, France,.... and design a new field (say Country) as a look up on table Countries.

From this point you can design only one query to see, as you need, the total for a certain country or a Grand Total.

I don't know if this is the best approach but I am sure that is better than your.
............... ............... ..............

Now, an answer to your original question:
Design a query based on table Export.
This query must have (at least) the fields USA, UK, France, Italy from the Export table and one more field Total: [USA]+[UK]+[France]+[Italy]. Of course, in every record, three of them will be Null, so take a look to NZ function to handle this situation.

After that you can sum the Total field in order to obtain the Grand Total.

I repeat: this is a BAD approach.

Good luck !

**NeoPa** · Jan 16 '12, 07:07 PM

So, to clarify, you only want a single value produced (The one for TOTAL VEHICLES)?
Why does the total shown in your example say 60 rather than the total as described in your explanation, which would be 70?

**dheerloveguru** · Jan 16 '12, 07:12 PM

Thanks for replying.... I just wanted to no if it's possible if I select "Total Vehicles" from the combo box as a new record, there is way that it will add the value for Car/Van or Lorry to Total Vehicles.... If there is any code that I could write in the Item control source property in a form and if it could be like that:

IF items = "Total Vehicles" Then
(lookup the items = 'Cars', "Vans', 'Lorries' and add the values)

My question is only if Item = Total Vehicles so then add value of car/van or lorry...

In my form, there is already a text box which is calculated the the TOTAL for each country and using the Nz function. Thanks in advance

**dheerloveguru** · Jan 16 '12, 07:39 PM

Reply to NeoPa...

Actually the form look like that:

link... http://www.mediafire.com/i/?ub1rbe9lt2482m8
Please let me know... if it's possible or have to find another way

**NeoPa** · Jan 16 '12, 10:47 PM

The total of 60 is wrong because the data values for France and Italy are different from those in the actual calculation.

Now. We need to understand what the question is, as your question is not clear. Your linked image shows what looks like an Excel worksheet illustrating what you have, but your question asks for this in Access. It sounds like (You make little real sense so it's hard to be sure exactly what you mean) you want a total type field for every record. You have a form that you want to show this on, but the form can be bound to a query with this field included at least as easily as creating a new control to show the same result without the extra field.

If it's done in the query it would look something like :

Code:

SELECT [USA]
     , [UK]
     , [France]
     , [Italy]
     , Nz([USA],0) + Nz([UK],0) + Nz([France],0) + Nz([Italy],0) AS [TOTAL]
FROM   [Export]

If done in a new control on the form then the ControlSource would be (Assuming TextBoxes for the other controls named "txt" plus their field names) :

Code:

=Nz([txtUSA],0) + Nz([txtUK],0) + Nz([txtFrance],0) + Nz([txtItaly],0)

How to calculate a field value

How to calculate a field value

Comment

Comment

Comment

Comment

Comment