Access request: how to query to get the calculated values?
HI,
I have a large table from which I can query to get the following table
type no of times type occurs
101 450
102 562
103 245
also i can get another table
code no of times code occurs
0 1222
1 750
2 355
but now I want to write a query that could get me the following table
type no of timescode1occurs %of timescode1 occurs out of %of times code1 occurs out of
no of times type occurs no of times code occcurs
101 50 11% 6%
102 75 13% 10%
How can I write a request to get this?
thank
+1
source to share
2 answers
Assuming a table like this:
type, code, ... other columns.
I am assuming your first 2 requests are something like
select type, count(*) from mytable group by type
select code, count(*) from mytable group by code
Then you want to do something like
SELECT DISTINCTROW mytable.Type, mytable.Code,
Count(*)/q1.[Count of type] AS [Percent Of Type],
Count(*)/q2.[Count of code] AS [Percent Of Code]
FROM mytable,
(select type, count(*) as [Count of type] from mytable group by type) q1,
(select code, count(*) as [Count of code] from mytable group by code) q2
where mytable.Type =q1.Type
and mytable.Code=q2.Code
GROUP BY mytable.Type, mytable.Code, q1.[Count of type], q2.[Count of code];
Hope this helps. Chris
+2
source to share