Select Statement

[accessma]

Is there a way to assign a field a value temporarily in a query?

My problem seems to be that if you are calculating a percentage of say 15,000 which in this case are total sales. That number is the total of two categories; East and West.

The East and West totals are aggregates of months pulled from another table.

The problem is that if there are no sales for certain months in the West it gives a false percentage.

So if East has sales in months 1,2,3,4,5,6,7 and West has sales in only 2,4,5,6,7 the percentage of West is inflated by what I think is missing months of data.

So When you divide the total of East by 15,000 you get the correct percentage. When you do the same for West you get an inflated percentage.

By the way, if you enter dummy records of 0.01 for the missing months for West it works correctly.

Anyone have any ideas on how to beat this?

 

[llkhoutx]

How are your determining the denominator, counting rows or using 6 instead of the actual number of rows.

Alternatively, use a union query, the first query of which produces one zero value row for each month and each region "unioned" with your regular query.