query to calculate percentages

[miconian]

So, I have three fields:

Revenue Under Goal (currency)
Site (text)
Order Line (number)

My goal is to answer this question:

By site, what percentage of the order lines have revenue under goal?

So the ideal results would looks something like this:

SiteA 20%
SiteB 43%

I admit that I'm really not even sure where to begin with this one. Any thoughts much appreciated. Thanks!

 

[Joe4]

How is the percentage calculated? Where is that number coming from?

 

[miconian]

If we were to do things more slowly, the percentage might be calculated like this:

Step 1: Choose one site (based on the Site field). Do a make-table query that returns only the records that only apply to that particular site, say SiteA. So now we have a new table, and every record says 'Site A' in the site field.

Step 2: Determine how many records there are, for that site, when grouped by order line, that have a value greater than zero in the Revenue Under Goal field.

Step 3: Determine how many records there are, for that site, when grouped by order line, in general.

Step 4: Divide the result from Step 2 by the result from Step 3. Express the answer as a percentage. Now we know what percent of order lines for Site A have revenue under goal.

Step 5: Repeat steps one through 4 with every site.


The term 'revenue under goal' might make this problem sound more complicated than it is. That field either has a positive number, or zero. The fact that it relates to some outside goal is not relevant; I should have picked a different name for it for this purpose.

 

[Joe4]

It sounds to me that maybe you are looking for an Average? Is that correct?
If so, creating an Aggregate Query and using the Average function should work.

Is that is not it, it may make more sense to work us through a simple example (not asking you do go through all the steps you posted in your previous post, but rather post a small data sample, along with the expected results and explain the mathematical logic for getting the result you are looking for).

 

[miconian]

I'm definitely not looking for an average.

Suppose we have the following data.

SITE ORDERLINE MONEY
A 123 $10.00
A 523 $23.73
A 8476 $1,232,123.23
A 233 $0
A 2345 $0.01
B 1234 $0.25
B 942 $0

The desired results of the query would look something like this:

SITE MONEY%
A 80%
B 50%

Explanation: A is 80% because 80% of the order lines (four out of five) related to site A had a value in the MONEY column greater than zero. B is 50% because one of the two records for site B had a value in the MONEY column greater than zero.

 

[miconian]

Note that the amount of money in the MONEY column doesn't matter. It's purely a question of whether that number is zero, or greater than zero, for each record.

 

[Joe4]

That's not to bad. We can do it in a step of two queries (it could actually all be done in one if you imbed them, but I think it is easier to understand if done in a two step process).

In the first query, we are simply going to return each record, just the Site and a 1 if the money is greater than zero and a 0 if it is not. The SQL for that query looks something like this:

SELECT 
    MyTable.Site, 
    IIf([MyTable]![Money]>0,1,0) AS MyCount
FROM 
    MyTable;

Let's name this query "QueryA". Now, just create a second query based on "QueryA" where we simply group by "Site", and divide the sum of the MyCount values by the Count of them, like this:

SELECT 
    QueryA.Site, 
    Sum(QueryA.MyCount)/Count(QueryA.MyCount) AS MoneyPct
FROM 
    QueryA
GROUP BY 
    QueryA.Site;

This should give you what you want. I tested it out and it returns your figures.

 

[Joe4]

Actually, we could use AVERAGE to simplify the second query a little, as when we have calculated each record to be a 1 or a 0, average would give us what we are after, i.e.

SELECT 
    QueryA.Site, 
    Avg(QueryA.MyCount) AS MoneyPct
FROM 
    QueryA
GROUP BY 
    QueryA.Site;

 

[miconian]

Okay... but what if there are many sites? Must I create a new query (or set of queries) for each one? Or is there another way (besides a VBA loop)?

 

[Joe4]

What do you mean?
It already does ALL sites in the table found in your table, however many you may have. There is nothing in the queries I have written to limit it to a single site.

Did you try it out?