How to tabulate (data analysis) the data

[indra059]

I have a data sheet in Column1 Age of respondents, in column1 Gender. and next column is Education. Now, I have to tabulate the data in different age group which is attached in sample file. I tried in MS Access querry but I can't do that. Your valuable suggestion is highly appreciated. Thanks.

Age|Male|Female|Total
0 - 5|5|5|10
6-15|15|25|40
16-30|45|20|65

 

[wiklendt]

have you tried a CROSSTAB query?

 

[spaddhu]

You will have to use Crosstab queries to achieve what you desire. First you have to write an Intermediate query that converts your age and gender to age group and string notation that you require. That query is a normal query as given below.

Inter_Query:
SELECT IIf([Age]<=10,"0-10",IIf([Age]<=15,"11-15",IIf([Age]<=25,"16-25",IIf([Age]<=40,"25-40","Above 40")))) AS [Age Group], IIf([Gender]=1,"M","F") AS Gender_Str, Table2.Education
FROM myTable
ORDER BY IIf([Age]<=10,"0-10",IIf([Age]<=15,"11-15",IIf([Age]<=25,"16-25",IIf([Age]<=40,"25-40","Above 40"))));

Then create a Crosstab query based on Inter_Query. Choose Age Group as Row Headings, Gender_Str as Column Headings and Count of Age_Group or Gender_Str or Education as Values. Later you can add additional row headings to indicate the percentages or sum of education values as you want.

Your completed crosstab query should look something like this.

TRANSFORM Count(Inter_Query.Gender_Str) AS [Count]
SELECT Inter_Query.[Age Group], Sum(Inter_Query.Education) AS [Total Of Education]
FROM Inter_Query
GROUP BY Inter_Query.[Age Group]
PIVOT Inter_Query.Gender_Str;

 

[indra059]

Thanks spaddhu.
But I still want to do is that in last column insert total number of male and female and calculate percentage of them. Also remove education from table. Thanks.

 

[wiklendt]

Thanks spaddhu.
But its revealed only male in gender field. I want to display female also and want to keep education in last column.

have you tried going into the design view of that query and playing around with it?

 

[indra059]

Yes wiklendt, you are right. I found my mistakes. Thanks

 

[indra059]

Thanks spaddhu.
But I still want to do is that insert total number of male and female in last column and calculate percentage of them. For details see attache file. Thanks.