Difficult formula? Code? If statement? Help?

[afs24]

I'm trying to write an if statement in the control source section of a report. Unless there is an easier way? Example:

A B Cartons Units (column A X Cartons)

12 6 2 24
6 6 2 12
12 6 3 36
16 8 2 36
12 4 2 24

What would be the formula to pull the percent of all As that equal 12 and B
s that equal 6. Basically, I want everything were column A & B equal 12 and 6 and what that percent of units is to the total units. For this example, rows 1 & 3 equal 12/6 which totals to 60 units. Now that I got the 60 units what percent is 60 units to the total which is 132 (add up all the units in the units column).

 

[SydneyGeek]

You'll need 3 fields and a couple of extra queries.

First, go to the original data source -- if it's a query, modify it as below. If a table, create a query from it with the same fields as the original, then make the changes. Let's call this Query1.

After the A B Cartons Units fields, create these:
ATwelve:Iif([A]=12,[A],0)
BSix:Iif(=6,,0)
APlusB:[A]+

Query2: Uses Query1 as the data source but only the 3 new fields to start --
ATwelve, BSix, APlusB
Create a new field -- [ATwelve]+[BSix], called MySum
Now click the Totals button on the query toolbar (the Sigma). In the Totals row for each field, change Group By to Sum. That will give you single-line totals.

Query3: Use Query2 as the source. New field, PercentAB, = SumOfMySum/SumOfAPlusB. Format as a Percent.

You can now use that query in your report.

Note: If you are feeling brave, the query calculations that I described above can be done in the report but they have th potential to get messy.

HTH

Denis