Calculating Proportions of values within a field or grouped

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have following fields
[Age] ; [Education] ; [Owns_CellPhone] etc.
In a single QBE, I want to be able to find out the proportions of records
for either value for [Owns_CellPhone] for each distinct [Age] and
[Education], and if possible I would also like to group [Age] in intervals of
5
 
Imran J Khan said:
I have following fields
[Age] ; [Education] ; [Owns_CellPhone] etc.
In a single QBE, I want to be able to find out the proportions of records
for either value for [Owns_CellPhone] for each distinct [Age] and
[Education], and if possible I would also like to group [Age] in intervals of
5
Click to expand...

Hi Imran,

I might use the Partition function to create
your age range, then group on this and Education.
Count the number in each group, then sum the
absolute value of Owns_CellPhone (if is Yes/No
field) to count number of owners in group.

Something like (if your table name were "People"):

SELECT
Partition([Age],0,100,5) AS AgeRange,
People.Education, Count(*) AS AgeRangeCount,
Sum(Abs([Owns_CellPhone])) AS CntOwns,
[CntOwns]/[AgeRangeCount] AS Proportion
FROM People
GROUP BY Partition([Age],0,100,5), People.Education;

if your data looked like:

PersonID Age Education Owns_CellPhone
1 4 PreSchool No
2 7 GradeSchool No
3 9 GradeSchool Yes
4 10 GradeSchool No
5 11 GradeSchool Yes
6 12 GradeSchool Yes
7 16 HighSchool Yes
8 17 HighSchool Yes
9 18 College Yes
10 19 College No
11 20 College Yes

above query would return:

AgeRange Education AgeRangeCount CntOwns Proportion
0: 4 PreSchool 1 0 0
5: 9 GradeSchool 2 1 0.5
10: 14 GradeSchool 3 2 0.67
15: 19 College 2 1 0.5
15: 19 HighSchool 2 2 1
20: 24 College 1 1 1

of course, you could right-mouse click on
Proportion column in grid, choose Properties,
then set Format to Percent and choose
Decimal Places for precision you want to see.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Thanks a lot Gary. That was very helpful. However, if possible, could
resubmit the query with chages as follows
I have following fields
[Age] ; [Education]
In a single QBE, I want to be able to find out the proportions of records
of each distinct [Education] for each distinct [Age], and if possible I
would also like to group [Age] in intervals of 5

Thanks in advance
Imran

Gary Walter said:
Imran J Khan said:
I have following fields
[Age] ; [Education] ; [Owns_CellPhone] etc.
In a single QBE, I want to be able to find out the proportions of records
for either value for [Owns_CellPhone] for each distinct [Age] and
[Education], and if possible I would also like to group [Age] in intervals of
5
Click to expand...

Hi Imran,

I might use the Partition function to create
your age range, then group on this and Education.
Count the number in each group, then sum the
absolute value of Owns_CellPhone (if is Yes/No
field) to count number of owners in group.

Something like (if your table name were "People"):

SELECT
Partition([Age],0,100,5) AS AgeRange,
People.Education, Count(*) AS AgeRangeCount,
Sum(Abs([Owns_CellPhone])) AS CntOwns,
[CntOwns]/[AgeRangeCount] AS Proportion
FROM People
GROUP BY Partition([Age],0,100,5), People.Education;

if your data looked like:

PersonID Age Education Owns_CellPhone
1 4 PreSchool No
2 7 GradeSchool No
3 9 GradeSchool Yes
4 10 GradeSchool No
5 11 GradeSchool Yes
6 12 GradeSchool Yes
7 16 HighSchool Yes
8 17 HighSchool Yes
9 18 College Yes
10 19 College No
11 20 College Yes

above query would return:

AgeRange Education AgeRangeCount CntOwns Proportion
0: 4 PreSchool 1 0 0
5: 9 GradeSchool 2 1 0.5
10: 14 GradeSchool 3 2 0.67
15: 19 College 2 1 0.5
15: 19 HighSchool 2 2 1
20: 24 College 1 1 1

of course, you could right-mouse click on
Proportion column in grid, choose Properties,
then set Format to Percent and choose
Decimal Places for precision you want to see.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
 
Sorry Imran...I took some time off from computers.

It would really help if you would provide
some sample data and the results you would
like to see from your query.

Thanks,

Gary Walter

Thanks a lot Gary. That was very helpful. However, if possible, could
resubmit the query with chages as follows
I have following fields
[Age] ; [Education]
In a single QBE, I want to be able to find out the proportions of records
of each distinct [Education] for each distinct [Age], and if possible I
would also like to group [Age] in intervals of 5

Thanks in advance
Imran

Gary Walter said:
Imran J Khan said:
I have following fields
[Age] ; [Education] ; [Owns_CellPhone] etc.
In a single QBE, I want to be able to find out the proportions of records
for either value for [Owns_CellPhone] for each distinct [Age] and
[Education], and if possible I would also like to group [Age] in intervals of
5
Click to expand...

Hi Imran,

I might use the Partition function to create
your age range, then group on this and Education.
Count the number in each group, then sum the
absolute value of Owns_CellPhone (if is Yes/No
field) to count number of owners in group.

Something like (if your table name were "People"):

SELECT
Partition([Age],0,100,5) AS AgeRange,
People.Education, Count(*) AS AgeRangeCount,
Sum(Abs([Owns_CellPhone])) AS CntOwns,
[CntOwns]/[AgeRangeCount] AS Proportion
FROM People
GROUP BY Partition([Age],0,100,5), People.Education;

if your data looked like:

PersonID Age Education Owns_CellPhone
1 4 PreSchool No
2 7 GradeSchool No
3 9 GradeSchool Yes
4 10 GradeSchool No
5 11 GradeSchool Yes
6 12 GradeSchool Yes
7 16 HighSchool Yes
8 17 HighSchool Yes
9 18 College Yes
10 19 College No
11 20 College Yes

above query would return:

AgeRange Education AgeRangeCount CntOwns Proportion
0: 4 PreSchool 1 0 0
5: 9 GradeSchool 2 1 0.5
10: 14 GradeSchool 3 2 0.67
15: 19 College 2 1 0.5
15: 19 HighSchool 2 2 1
20: 24 College 1 1 1

of course, you could right-mouse click on
Proportion column in grid, choose Properties,
then set Format to Percent and choose
Decimal Places for precision you want to see.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Back
Top