Query to count multi columns as one field

[yus2aces]

I have a problem to count a multi columns into one field. For example, I have a table consist of these fields (simplified version):
- id
- block_name
- handle_type (lookup: big and small)
- kernel_type (lookup: big and small)
- fruit_set (lookup: 1, 2, 3 and 4)

I want to create a query to count a combination of handle_type and kernel_type columns as one column. So the query field would shows these fields:

- block_name
- count_of_big_handle_big_kernel
- count_of_big_handle_small_kernel
- count_of_small_handle_big_kernel
- count_of_small_handle_small_kernel
- count_of_fruit_set_1
- count_of_fruit_set_2
- count_of_fruit_set_3
- count_of_fruit_set_4

How to accomplish this? Please help me.

 

[yus2aces]

I have found the solution for my problem. This is the steps:
- Create a select query (name it q_combine) to combine the handle_type and kernel_type columns as one.

SELECT blockname, [handle_type] & [kernel_type] AS hk_combination
FROM table;
​

- Create a crosstab query to show the count value in the hk_combination above.
- Create another crosstab query to show the count value of fruit_set field.
- Combine the crosstabs above as one.