ProwlingEyes
Registered User.
- Local time
- Today, 13:06
- Joined
- Sep 22, 2013
- Messages
- 12
I posted this question in "Reports" section and here I am posting the answer. Because I think it is more related to "Queries" than reports.
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: goo.gl/Y3FYTj)
At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Crosstab query did the trick..
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: goo.gl/Y3FYTj)
At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;