By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

How to calculate a field value

P: 11
I'm working on a project named Export and I'm stuck..... I just want to calculate the Total Vehicle which for the time being I'm doing it manually.... My project looks like that:


Table - Export

Fields
ID
Date
Shift (First/Second/Third Shift Duty)
Items
USA
UK
France
Italy
TOTAL

The data entries are being done in a subform in datasheet view and the field Items is a combo box with value "Cars", "Van", "Lorries", "TOTAL VECHILE", "Shirt", "Short", etc... The records are shown like that:

ITEMS USA UK France Italy TOTAL
Cars 10 10 20
Vans 15 10 10 35
Lorries 5 5 5 15
TOTAL VEHICLES 30 15 15 10 60
Shirt 10 20 30
Short 5 5
etc...

I just want if it's possible:

IF items = "Total Vehicles" Then
(lookup the items = 'Cars', "Vans', 'Lorries' and add the value for UK, USA, France and Italy)
Jan 15 '12 #1
Share this Question
Share on Google+
5 Replies


100+
P: 759
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(PresidentName, 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 !
Jan 16 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
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?
Jan 16 '12 #3

P: 11
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
Jan 16 '12 #4

P: 11
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
Jan 16 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [USA]
  2.      , [UK]
  3.      , [France]
  4.      , [Italy]
  5.      , Nz([USA],0) + Nz([UK],0) + Nz([France],0) + Nz([Italy],0) AS [TOTAL]
  6. 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) :
Expand|Select|Wrap|Line Numbers
  1. =Nz([txtUSA],0) + Nz([txtUK],0) + Nz([txtFrance],0) + Nz([txtItaly],0)
Jan 16 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.