CODEWITHSUNDEEP
ms-access
Anne String
Anne String

Reputation: 3

MS Access Percentages of Column Values

I have a simple Access table called Test, with two columns - Field1 and Field2. The data in the table looks like this. 

Field1  Field2
1       2
2       2
2       2
2       1
2       1

I'm trying to return the percentage of times each data type exists in each column. (ie - In Field1, the number 1 exists 20% and the number 2 exists 80%)

I found this query and it works great returning one column at a time. 

Select Field1, (Count(Field1)* 100 / (select Count(*) From Test)) as fieldA
FROM Test
Group By Field1;

How would I select both columns? I tried this but it doesn't work. 

Select 
  Field1, (Count(Field1)* 100 / (select Count(*) From Test)) as fieldA,
  Field2, (Count(Field2)* 100 / (select Count(*) From Test)) as fieldB
FROM Test
Group By Field1;

Sorry for posting such a simple question. I just started using Access a few days ago.

Thanks,

Anne

Upvotes: 0

Views: 124

Answers (1)

jpw
jpw

Reputation: 44871

One way (of many) to do this is to do two separate queries (one for each field) and wrap them in an outer query like this:

select * from (
    SELECT Field1, (Count(Field1)* 100 / (select Count(*) From Test)) as fieldA 
    FROM Test 
    GROUP BY Field1
    ) a 
inner join (
    SELECT Field2, (Count(Field2)* 100 / (select Count(*) From Test)) as fieldB 
    FROM Test 
    GROUP BY Field2
    ) b on a.Field1 = b.Field2

This would give the following result:

Field1  fieldA  Field2  fieldB
1       20      1       40
2       80      2       60

This assumes the same numbers exists in both columns; if it can be that a number only exists in one of the columns you might want to change the inner join to a left join instead.

Edit: As a follow-up to the modifications asked for in the comments to the question (adding more fields) it's worth mentioning that due to the join semantics in Access-SQL it might be easier to take another approach, and save a query for each of the aggregate statement SELECT Field1, (Count(Field1)* 100 / (select Count(*) From Test)) AS fieldA FROM Test GROUP BY Field1; and then use the visual query design tool to build a query that uses the previously saved queries as source.

Upvotes: 2

Related Questions