How to Count a string in a column and print it in a report? (1 Viewer)

ProwlingEyes

Registered User.
Local time
Tomorrow, 00:27
Joined
Sep 22, 2013
Messages
12
I am migrating a database from Spreadsheets to Access 2010. Everything else is going well but I am stuck at one point. A table has Name, Gender, and Nationality fields. Now, I need a report that will have only Nationality and Gender fields. The nationalities will be in a list and another column should have total count for each nationality. Then, the Grand total should be print at the bottom of the report. Moreover, two other columns should have a count of each gender (male and female) against every nationality. [This is how I did it in Spreadsheets: imgur.com/W6aDhPT ]
Let me put it in a simple way: I need Report which will have Four columns i.e. Nationalities, Total, Male, Female... The nationalities column will contain a list of nationalities that are there in the data table [field name: National].. The total column will count and show the sum of each nationality from the data table [same field: national]... The Male and Female columns will do the same i.e. count the occurrence and show the total for Male and Female from the data table [field name: Gender].
 

pr2-eugin

Super Moderator
Local time
Today, 21:27
Joined
Nov 30, 2011
Messages
8,494
Hello ProwlingEyes, What you need is a GROUP BY Clause, the link will have a good explanation on how to achieve your result.

If you are stuck post back, we will help you out. Hope this helps !
 

ProwlingEyes

Registered User.
Local time
Tomorrow, 00:27
Joined
Sep 22, 2013
Messages
12
Hello ProwlingEyes, What you need is a GROUP BY Clause, the link will have a good explanation on how to achieve your result.

Seems the thing i need, but donno how to apply it...do i have to make a query or something? a little light on the matter will be apreciated.. thanks
 

pr2-eugin

Super Moderator
Local time
Today, 21:27
Joined
Nov 30, 2011
Messages
8,494
Something along the lines of..
Code:
SELECT [COLOR=Blue]nationFieldName[/COLOR], Count([COLOR=Blue]maleFieldName[/COLOR]) As MaleCount, Count([COLOR=Blue]femaleFieldName[/COLOR]) As FemaleCount
FROM [COLOR=Blue]yourTableName[/COLOR]
GROUP BY [COLOR=Blue]nationFieldName[/COLOR];
Change the highlighted bits to match your design..
 

ProwlingEyes

Registered User.
Local time
Tomorrow, 00:27
Joined
Sep 22, 2013
Messages
12
Something along the lines of..
Code:
SELECT [COLOR=Blue]nationFieldName[/COLOR], Count([COLOR=Blue]maleFieldName[/COLOR]) As MaleCount, Count([COLOR=Blue]femaleFieldName[/COLOR]) As FemaleCount
FROM [COLOR=Blue]yourTableName[/COLOR]
GROUP BY [COLOR=Blue]nationFieldName[/COLOR];
Change the highlighted bits to match your design..

well, thank you very much..it worked, report has nationalities in descending order.. a snap of the report can be seen here imgur.com/HxFB33U and the SQL command looks like this:
Code:
SELECT Register.National, Count(Register.National) AS Total
FROM Register
GROUP BY Register.National
ORDER BY Count(Register.National) DESC;
But I can't get the same code to work for Gender, because gender has only one field not two. Male and Female are on the same field. By using the SQL code you have provided,i can only calculate both male and female as one number for each nationality.. but i want to calculate male and female separately for each nationality.. is that possible?
 

pr2-eugin

Super Moderator
Local time
Today, 21:27
Joined
Nov 30, 2011
Messages
8,494
Oops, so sorry I thought they were two and just aircoded it.. When you posted a reply I realized my mistake. Try this code..
Code:
SELECT Register.National, Sum(IIF([COLOR=Blue][B]genderFieldName [/B][/COLOR]= 'Male', 1, 0)) As MaleCount, 
Sum(IIF([COLOR=Blue][B]genderFieldName [/B][/COLOR]= 'Female', 1, 0)) As FemaleCount, 
Count(Register.National) AS Total
FROM Register
GROUP BY Register.National
ORDER BY Count(Register.National) DESC;
 

ProwlingEyes

Registered User.
Local time
Tomorrow, 00:27
Joined
Sep 22, 2013
Messages
12
I created a cross-tab query and it solved the problem. selected the nationality column as row header, gender column as column header and customer Id as calculation point. the SQL code looks like this:
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;
 
Last edited:

Users who are viewing this thread

Top Bottom