How to count some data percent based on table? Thanks a lot!!

X

xpengi

Hi,

I have been stifled with question for 5 days...my boss will lose hi
patience....help please!!

i have a table "students" in my case as below,
Student# -------------------City --------------------------Amount
1 -----------------------toronto -------------------------1000
2 -----------------------ottawa --------------------------3000
3 -----------------------toronto --------------------------4000
4 -----------------------ottawa --------------------------8000
5 -----------------------montreal ------------------------11000
6 -----------------------vancouver---------------------- 15000
7 -----------------------toronto
8 -----------------------ottawa
9 -----------------------montreal
10 -----------------------vancouver



I want to build a query to generate the output is like below,

RANGE----------------------------NUMBER OF STUDENT
------------------PERCENT of TOTAL(%)
0-5000---------------------------------
------------------------------------------30%
5001-10000----------------------------1------------------------------------------10%
10001-15000--------------------------2------------------------------------------20%
null valu
------------------------------4------------------------------------------40%

how to build such a query? i need paint a bar & pie chart based on thi
query.

i got 2 ideas from some experts in this forum,

1.use partition function, but still problematic to work.

SELECT Partition(amount, 0, 20000, 5000),
Count(*) AS [NUMBER OFSTUDENTS]
FROM students
GROUP BY Partition(amount, 0, 20000, 5000)


2. create a new table with "RANGE"...., then apply subquery to obtai
wanted data. it works, BUT cannot showing "null value" and i don't kno
how to get data of "PERCENT of Total(%)".

Thanks a thousand!!!

Pete
 
K

KARL DEWEY

This might do it -- Four queries -

Query12 --
SELECT [YourTable-2].City, Partition([amount],0,25000,5000) AS Expr1,
Count(*) AS [NUMBER OF STUDENTS]
FROM [YourTable-2]
GROUP BY [YourTable-2].City, Partition([amount],0,25000,5000);

Query12_Crosstab --
TRANSFORM Sum(Query12.[NUMBER OF STUDENTS]) AS [SumOfNUMBER OF STUDENTS]
SELECT Query12.City, Sum(Query12.[NUMBER OF STUDENTS]) AS [Total Of NUMBER
OF STUDENTS]
FROM Query12
GROUP BY Query12.City
PIVOT Query12.Expr1;

This is Query13 --
SELECT Sum(Query12_Crosstab.[Total Of NUMBER OF STUDENTS]) AS [SumOfTotal Of
NUMBER OF STUDENTS]
FROM Query12_Crosstab;

Query12_Crosstab-1 --
TRANSFORM Sum(Query12.[NUMBER OF STUDENTS]) AS [SumOfNUMBER OF STUDENTS]
SELECT Query12.Expr1 AS Grouping, Sum([SumOfTotal Of NUMBER OF
STUDENTS]/[NUMBER OF STUDENTS]) AS [Percent], Sum(Query12.[NUMBER OF
STUDENTS]) AS [Total Of NUMBER OF STUDENTS]
FROM Query12, Query13
GROUP BY Query12.Expr1
PIVOT Query12.[NUMBER OF STUDENTS];

Hope you can sort it out.

xpengi said:
Hi,

I have been stifled with question for 5 days...my boss will lose his
patience....help please!!

i have a table "students" in my case as below,
Student# -------------------City --------------------------Amount
1 -----------------------toronto -------------------------1000
2 -----------------------ottawa --------------------------3000
3 -----------------------toronto --------------------------4000
4 -----------------------ottawa --------------------------8000
5 -----------------------montreal ------------------------11000
6 -----------------------vancouver---------------------- 15000
7 -----------------------toronto
8 -----------------------ottawa
9 -----------------------montreal
10 -----------------------vancouver



I want to build a query to generate the output is like below,

RANGE----------------------------NUMBER OF STUDENTS
------------------PERCENT of TOTAL(%)
0-5000--------------------------------- 3
------------------------------------------30%
5001-10000----------------------------1------------------------------------------10%
10001-15000--------------------------2------------------------------------------20%
null value
------------------------------4------------------------------------------40%

how to build such a query? i need paint a bar & pie chart based on this
query.

i got 2 ideas from some experts in this forum,

1.use partition function, but still problematic to work.

SELECT Partition(amount, 0, 20000, 5000),
Count(*) AS [NUMBER OFSTUDENTS]
FROM students
GROUP BY Partition(amount, 0, 20000, 5000)


2. create a new table with "RANGE"...., then apply subquery to obtain
wanted data. it works, BUT cannot showing "null value" and i don't know
how to get data of "PERCENT of Total(%)".

Thanks a thousand!!!

Peter
Click to expand...
 
D

Dale Fye

Try this:

1. Create a table (tbl_Ranges) with the following data types and values
SortOrder (Number)
Range (Text, 25)

tbl_Ranges SortOrder Range
1 0 to 5000
2 5001 to 10000
3 10000 and up
4 NULL


Now, create a query (Query1) with the following SQL. I've used the Switch
function, which is not quite as neat as Partition, but which allows you to
trap for ISNULL() and to make the individual partitions as small or large as
you want.

SELECT Switch(IsNull([Amount]),"NULL",[Amount]<=5000,"0 to
5000",[Amount]<=10000,"5001 to 10000",[Amount]>10000,"10000 and up") AS
Range, Count(tbl_Students.StudentID) AS CountOfStudentID
FROM tbl_Students
GROUP BY Switch(IsNull([Amount]),"NULL",[Amount]<=5000,"0 to
5000",[Amount]<=10000,"5001 to 10000",[Amount]>10000,"10000 and up")
ORDER BY Switch(IsNull([Amount]),"NULL",[Amount]<=5000,"0 to
5000",[Amount]<=10000,"5001 to 10000",[Amount]>10000,"10000 and up")

Last step,

Create another query based on Query1 and tbl_Ranges:

SELECT tbl_Ranges.Range, Query1.CountOfStudentID,
[CountofStudentID]/DCount("StudentID","tbl_Students") AS [Pct of Total]
FROM Query1 INNER JOIN tbl_Ranges ON Query1.Range = tbl_Ranges.Range
ORDER BY tbl_Ranges.SortOrder;

HTH
Dale


xpengi said:
Hi,

I have been stifled with question for 5 days...my boss will lose his
patience....help please!!

i have a table "students" in my case as below,
Student# -------------------City --------------------------Amount
1 -----------------------toronto -------------------------1000
2 -----------------------ottawa --------------------------3000
3 -----------------------toronto --------------------------4000
4 -----------------------ottawa --------------------------8000
5 -----------------------montreal ------------------------11000
6 -----------------------vancouver---------------------- 15000
7 -----------------------toronto
8 -----------------------ottawa
9 -----------------------montreal
10 -----------------------vancouver



I want to build a query to generate the output is like below,

RANGE----------------------------NUMBER OF STUDENTS
------------------PERCENT of TOTAL(%)
0-5000--------------------------------- 3
------------------------------------------30%
5001-10000----------------------------1------------------------------------------10%
10001-15000--------------------------2------------------------------------------20%
null value
------------------------------4------------------------------------------40%

how to build such a query? i need paint a bar & pie chart based on this
query.

i got 2 ideas from some experts in this forum,

1.use partition function, but still problematic to work.

SELECT Partition(amount, 0, 20000, 5000),
Count(*) AS [NUMBER OFSTUDENTS]
FROM students
GROUP BY Partition(amount, 0, 20000, 5000)


2. create a new table with "RANGE"...., then apply subquery to obtain
wanted data. it works, BUT cannot showing "null value" and i don't know
how to get data of "PERCENT of Total(%)".

Thanks a thousand!!!

Peter
Click to expand...
 
K

KARL DEWEY

In my haste I did not correct part of Query12_Crosstab-1. Here is the
corrected query --
Query12_Crosstab-1
TRANSFORM Sum(Query12.[NUMBER OF STUDENTS]) AS [SumOfNUMBER OF STUDENTS]
SELECT Query12.Expr1 AS Grouping, Sum([NUMBER OF STUDENTS]/[SumOfTotal Of
NUMBER OF STUDENTS])*100 AS [Percent], Sum(Query12.[NUMBER OF STUDENTS]) AS
[Total Of NUMBER OF STUDENTS]
FROM Query12, Query13
GROUP BY Query12.Expr1
PIVOT Query12.[NUMBER OF STUDENTS];


KARL DEWEY said:
This might do it -- Four queries -

Query12 --
SELECT [YourTable-2].City, Partition([amount],0,25000,5000) AS Expr1,
Count(*) AS [NUMBER OF STUDENTS]
FROM [YourTable-2]
GROUP BY [YourTable-2].City, Partition([amount],0,25000,5000);

Query12_Crosstab --
TRANSFORM Sum(Query12.[NUMBER OF STUDENTS]) AS [SumOfNUMBER OF STUDENTS]
SELECT Query12.City, Sum(Query12.[NUMBER OF STUDENTS]) AS [Total Of NUMBER
OF STUDENTS]
FROM Query12
GROUP BY Query12.City
PIVOT Query12.Expr1;

This is Query13 --
SELECT Sum(Query12_Crosstab.[Total Of NUMBER OF STUDENTS]) AS [SumOfTotal Of
NUMBER OF STUDENTS]
FROM Query12_Crosstab;

Query12_Crosstab-1 --
TRANSFORM Sum(Query12.[NUMBER OF STUDENTS]) AS [SumOfNUMBER OF STUDENTS]
SELECT Query12.Expr1 AS Grouping, Sum([SumOfTotal Of NUMBER OF
STUDENTS]/[NUMBER OF STUDENTS]) AS [Percent], Sum(Query12.[NUMBER OF
STUDENTS]) AS [Total Of NUMBER OF STUDENTS]
FROM Query12, Query13
GROUP BY Query12.Expr1
PIVOT Query12.[NUMBER OF STUDENTS];

Hope you can sort it out.

xpengi said:
Hi,

I have been stifled with question for 5 days...my boss will lose his
patience....help please!!

i have a table "students" in my case as below,
Student# -------------------City --------------------------Amount
1 -----------------------toronto -------------------------1000
2 -----------------------ottawa --------------------------3000
3 -----------------------toronto --------------------------4000
4 -----------------------ottawa --------------------------8000
5 -----------------------montreal ------------------------11000
6 -----------------------vancouver---------------------- 15000
7 -----------------------toronto
8 -----------------------ottawa
9 -----------------------montreal
10 -----------------------vancouver



I want to build a query to generate the output is like below,

RANGE----------------------------NUMBER OF STUDENTS
------------------PERCENT of TOTAL(%)
0-5000--------------------------------- 3
------------------------------------------30%
5001-10000----------------------------1------------------------------------------10%
10001-15000--------------------------2------------------------------------------20%
null value
------------------------------4------------------------------------------40%

how to build such a query? i need paint a bar & pie chart based on this
query.

i got 2 ideas from some experts in this forum,

1.use partition function, but still problematic to work.

SELECT Partition(amount, 0, 20000, 5000),
Count(*) AS [NUMBER OFSTUDENTS]
FROM students
GROUP BY Partition(amount, 0, 20000, 5000)


2. create a new table with "RANGE"...., then apply subquery to obtain
wanted data. it works, BUT cannot showing "null value" and i don't know
how to get data of "PERCENT of Total(%)".

Thanks a thousand!!!

Peter
Click to expand...
Click to expand...
 
X

xpengi

it works, many thanks to Karl and other experts here for being helpful.

still headache questions for this case,

1. if i define these depended queriese in code, query12
query12_crosstab, query13, query13_crosstab, then i want to set th
form's record source is query13_crosstab, how to do that?

in my code, (not work)
dim query12...

query12 = "select.....from.."
query12_crosstab ="select query12......"
query13 = "select.....from query12_crosstab......"
query13_crosstab = "select query13......"

defQuery = CurrentDb.QueryDefs("query13_crosstab").SQL

Me.RecordSource = ????????

Call modADO.ConnectToThisDatabase
Call modADO.Students(modGlobal.curConn, modGlobal.rsRec
modGlobal.defQuery)
....


2.
i need to paint statistical bar & pie charts based on the quer
result.
i applied MS Chart control 6.0 in this case and the chart is built O
based on a test query.
the problem is how to assign the percent value of each (0-4999
5000-9999....) to each bar of in chart? in Pie chart? Is it possible t
assign field's value of the query to each legend of the chart?

i coded as below but not work:
...
With MSChart1 <-- bar chart
Set .DataSource = rsRec
.Visible = True
.ShowLegend = True
.Plot.Axis(VtChAxisIdY).AxisScale.Type = VtChScaleTypePercent
.Plot.Axis(VtChAxisIdY).AxisScale.PercentBasis
VtChPercentAxisBasisMaxChart
End With

With MSChart2 <- pie chart
Set .DataSource = rsRec
.Visible = True
.ShowLegend = True
End With
....

Thanks a lot!

Pete
 
Top