Re: Calculating Proportions of values within a field or grouped

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Imran J Khan (Khan_at_discussions.microsoft.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 03:25:03 -0700

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" wrote:

>
> "Imran J Khan" wrote:
> > 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
>
> 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
>
>
>

Relevant Pages

  • Calculating Proportions of values within a field or grouped
    ... In a single QBE, I want to be able to find out the proportions of records ... [Education], and if possible I would also like to group [Age] ...
    (microsoft.public.access.queries)
  • Re: Hooray - Tech Colleges for 14 Year Olds - NUT to Oppose?
    ... at risk of being pushed into vocational courses and pupils forced to ... "It is not acceptable that at the age of 14 pupils may ... future education or career choices. ... better off than a 14 year who goes to school. ...
    (uk.legal)
  • Re: Daily strip-testing debated in Canada
    ... Instead they should look at the level of diabetes education ... Died at age ... still had debilitating hypos or nasty complications.? ... I was of course younger then and never really got involved in his diabetes. ...
    (alt.support.diabetes)
  • Re: Gary Puckett and the Union Gap
    ... It's harder to grow into an adult ... He would probably have left at the minimum leaving age like his father if he had not been bright enough to get a scholarship to King Edward's Birmingham. ... Higher/university education was much more the preserve of the middle/upper classes in those days. ...
    (uk.people.support.depression)