Complex Query ?

[Sean]

I have a table with 2 fields, Order and Zone. There are many orders but
there is only 7 zone types, A, B, C, D, F, J, N. In this table I only have
the orders that have 2 zones on them, so each order# will appear twice with
its respective zone. How do I write a query or queries that will show me the
percentage of which zones appear together more often on orders? Example,
40% of all orders have zone A and B, 20% of orders have zone J & F, etc.

Thanks,
Sean

 

[Michel Walsh]

SELECT zone,
COUNT(*) AS howMany
FROM tableName
GROUP BY zone



would return the number of records, per zone. To add the percentage, we have
to divide by the total number of records:



SELECT zone,
COUNT(*) AS howMany,
COUNT(*)/(SELECT COUNT(*) FROM tableName) As percentage
FROM tableName
GROUP BY zone



Those lines of code have to be typed in the SQL view (not in the graphical
view) of a query. Change the not-all-cap words by those that fit your
design.



Hoping it may help,
Vanderghast, Access MVP

 

[Sean]

Thanks for the reply but its not what I am looking for. I am looking to find
out wich 2 zones appear on the majority of orders. Each order has 2 zones,
they could be a and j, b and n, etc. and what I would like to see is, x% of
orders have zones a and J, b & n, etc. So let's say all orders have zone F

 

[Michel Walsh]

So, I assume your data is like:


OrderID, zone1, zone2 ' fields


Use a first query to normalize the data:


SELECT OrderID, zone1 As zone FROM tableName
UNION ALL
SELECT OrderID, zone2 FROM tableName


save that query under the name, say, of qu.


Then, make a second query:


SELECT zone, COUNT(*) as howMany
FROM qu
GROUP BY zone


which returns the number of time each zone is mentioned. Say it is query q1.


A last query. It stats with:

SELECT a.zone, LAST(a.howMany) AS hm, SUM(b.howMany) as runningSum
FROM q1 AS a INNER JOIN q1 AS b
ON a.zone=b.zone AND a.howMany >=b.howMany
GROUP BY a.zone


which returns the running sum, starting with the lowest occurrence. I mean,
if zones (a, b, c, d) occur (30, 20, 10, 6) times, then the running sum
will be

zone hm running
d 6 6
c 10 16
b 20 36
a 30 66

(the records may appear in a different order, only their fields values is
important).

So, we only have to keep those HAVING the running sum >= 66 * percentage
wanted. If the desired percentage is 40 percent, ie, which zones make at
least 40% of all orders:


SELECT a.zone, LAST(a.howMany) AS hm
FROM q1 AS a INNER JOIN q1 AS b
ON a.zone=b.zone AND a.howMany >=b.howMany
GROUP BY a.zone
HAVING SUM(b.howMany) >= 0.4*(SELECT COUNT(*) FROM qu)


which will be zones b and a, in this case.


So, make query qu (a saved query), and q1 (also a saved query) and run the
last one, change the 0.4 to the percentage you want.




Hoping it may help,
Vanderghast, Access MVP

 

[Sean]

Thanks again. Actually my data is like this
order zone
12345 b
12345 f

I know its a simple query but I forget how to do it to change the data above
to be

order zone1 zone2
12345 b f

Can you send that to me and then I'll try the other?

Thanks again for your help.

 

[Michel Walsh]

No, your data is already correct. Just skip over the first step, since your
data is already normalized, use your table instead of the saved query qu.



Vanderghast, Access MVP