Percentage calculations and 0 values - Help

[Guest]

I need to calculate approx 40 fields and give a percentage, but some times
the fields are 0 as they are not included. How do I get the calculation to
ignore the 0 instead of counting it as the value. Hope this make sense

 

[Dale Fye]

Tracy,

If you are trying to sum or count or do any other math across multilpe fields, it is an indication that your table structure needs to be revised. Personally, if I had a structure like yours, I would export the table to EXCEL and do the calculations there. Access is not a spreadsheet, don't use it like one.

The data you now have in fields (don't know what you are calling them , should be normalized, which would then make doing what you ask extremely easy. Lets assume that your table tracks sales and has the following columns and sample data:

Location Jan Feb Mar Apr May
New York 10 20 15 30 10
Miami 20 10 30 45 50

To do what you want to do, you will have to write an extremely long statement that looks something like

CumSales = Jan + Feb + Mar + Apr + May
CountMon = iif(jan=0,0, 1) + iif(feb = 0, 0, 1) + ...

You get the picture. If your data were normalized it would look like this:

Location Month Sales
New York Jan 10
New York Feb 20
New York Mar 15

and so on.....

Then to get the calculation you want all you would need to do is sum the Sales column

SELECT Location, Sum(Sales) as CumSales
FROM yourTable
WHERE Sales <> 0

HTH
Dale

 

[Guest]

Thanks for your help I will give this a try

Tracy,

If you are trying to sum or count or do any other math across multilpe fields, it is an indication that your table structure needs to be revised. Personally, if I had a structure like yours, I would export the table to EXCEL and do the calculations there. Access is not a spreadsheet, don't use it like one.

The data you now have in fields (don't know what you are calling them , should be normalized, which would then make doing what you ask extremely easy. Lets assume that your table tracks sales and has the following columns and sample data:

Location Jan Feb Mar Apr May
New York 10 20 15 30 10
Miami 20 10 30 45 50

To do what you want to do, you will have to write an extremely long statement that looks something like

CumSales = Jan + Feb + Mar + Apr + May
CountMon = iif(jan=0,0, 1) + iif(feb = 0, 0, 1) + ...

You get the picture. If your data were normalized it would look like this:

Location Month Sales
New York Jan 10
New York Feb 20
New York Mar 15

and so on.....

Then to get the calculation you want all you would need to do is sum the Sales column

SELECT Location, Sum(Sales) as CumSales
FROM yourTable
WHERE Sales <> 0

HTH
Dale