Count Frequencies

A

Andibevan

Hi All,

I am having trouble creating a query that will provide information for input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these intervals -
any solutions / pointers would be gratefully received.

Ta

Andi
 
M

Marshall Barton

Andibevan said:
I am having trouble creating a query that will provide information for input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these intervals -
any solutions / pointers would be gratefully received.
Click to expand...


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.
 
A

Andibevan

Marshall Barton said:
If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.
Click to expand...

My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range, Count(Queryfour.DefectID) AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have inputted
the date ranges into a table called Table_Intervals and it is setup as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?

Thanks

Andi
 
M

Marshall Barton

Andibevan said:
"Marshall Barton" wrote
If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.
Click to expand...

My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range, Count(Queryfour.DefectID) AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have inputted
the date ranges into a table called Table_Intervals and it is setup as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?
Click to expand...


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200
Click to expand...

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]
 
A

Andibevan

Marshall Barton said:
Andibevan said:
"Marshall Barton" wrote
Andibevan wrote:
I am having trouble creating a query that will provide information for input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these intervals -
any solutions / pointers would be gratefully received.


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.
Click to expand...

My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range, Count(Queryfour.DefectID) AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have inputted
the date ranges into a table called Table_Intervals and it is setup as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?
Click to expand...


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200
Click to expand...

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]
Click to expand...

Hey Marsh,

Thanks so Much - that is fantastic - I just have 2 final quick questions:-

1) On the query there is a column <> that contains 1 zero and no other
information - how can I get rid of that?
2) Can you recommend any good "Teach Yourself SQL" websites or books that I
could use as a reference to try and solve these problems myself?

Thanks Again

Andi
 
D

Duane Hookom

Andibevan said:
Marshall Barton said:
Andibevan said:
"Marshall Barton" wrote
Andibevan wrote:
I am having trouble creating a query that will provide information
for
input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of
each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these
intervals -
any solutions / pointers would be gratefully received.


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.


My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range, Count(Queryfour.DefectID) AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have
inputted
the date ranges into a table called Table_Intervals and it is setup as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?
Click to expand...


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200
Click to expand...

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]
Click to expand...

Hey Marsh,

Thanks so Much - that is fantastic - I just have 2 final quick questions:-

1) On the query there is a column <> that contains 1 zero and no other
information - how can I get rid of that?
2) Can you recommend any good "Teach Yourself SQL" websites or books that
I
could use as a reference to try and solve these problems myself?

Thanks Again

Andi
Click to expand...
The <> column is the result of a Null value in the field [Severity/Closure].
Set the criteria to WHERE [Severity/Closure] is not null.
John Viescas has some good book references (including one he co-authored) at
www.viescas.com.
 
A

Andibevan

Duane Hookom said:
Andibevan said:
Marshall Barton said:
Andibevan wrote:

"Marshall Barton" wrote
Andibevan wrote:
I am having trouble creating a query that will provide information
for
input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of
each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these
intervals -
any solutions / pointers would be gratefully received.


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.


My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range,
Click to expand...
Count(Queryfour.DefectID)
AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have
inputted
the date ranges into a table called Table_Intervals and it is setup as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]
Click to expand...

Hey Marsh,

Thanks so Much - that is fantastic - I just have 2 final quick questions:-

1) On the query there is a column <> that contains 1 zero and no other
information - how can I get rid of that?
2) Can you recommend any good "Teach Yourself SQL" websites or books that
I
could use as a reference to try and solve these problems myself?

Thanks Again

Andi
Click to expand...
The <> column is the result of a Null value in the field [Severity/Closure].
Set the criteria to WHERE [Severity/Closure] is not null.
John Viescas has some good book references (including one he co-authored) at
www.viescas.com.
Click to expand...

Thanks Duane but I still have problems - I have used the following statement
but I now don't have any of the Severity / Closure columns I did before and
I only have the columns 0 and -1

Any ideas?

Thanks in advance

Andy
 
D

Duane Hookom

See what you return if your SQL is

PIVOT Nz(Queryfour.[Severity/Closure], "NA")


--
Duane Hookom
MS Access MVP


Andibevan said:
Duane Hookom said:
Andibevan said:
Andibevan wrote:

"Marshall Barton" wrote
Andibevan wrote:
I am having trouble creating a query that will provide information
for
input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of
each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these
intervals -
any solutions / pointers would be gratefully received.


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.


My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range, Count(Queryfour.DefectID)
AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have
inputted
the date ranges into a table called Table_Intervals and it is setup
as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]

--
Marsh
MVP [MS Access]

Hey Marsh,

Thanks so Much - that is fantastic - I just have 2 final quick questions:-

1) On the query there is a column <> that contains 1 zero and no other
information - how can I get rid of that?
2) Can you recommend any good "Teach Yourself SQL" websites or books that
I
could use as a reference to try and solve these problems myself?

Thanks Again

Andi
Click to expand...
The <> column is the result of a Null value in the field [Severity/Closure].
Set the criteria to WHERE [Severity/Closure] is not null.
John Viescas has some good book references (including one he co-authored) at
www.viescas.com.
Click to expand...

Thanks Duane but I still have problems - I have used the following
statement
but I now don't have any of the Severity / Closure columns I did before
and
I only have the columns 0 and -1

Any ideas?

Thanks in advance

Andy
Click to expand...
 
A

Andibevan

I get a 0 in the NA column



Duane Hookom said:
See what you return if your SQL is

PIVOT Nz(Queryfour.[Severity/Closure], "NA")


--
Duane Hookom
MS Access MVP


Andibevan said:
Duane Hookom said:
Andibevan wrote:

"Marshall Barton" wrote
Andibevan wrote:
I am having trouble creating a query that will provide information
for
input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of
each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these
intervals -
any solutions / pointers would be gratefully received.


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.


My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range, Count(Queryfour.DefectID)
AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have
inputted
the date ranges into a table called Table_Intervals and it is setup
as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]

--
Marsh
MVP [MS Access]

Hey Marsh,

Thanks so Much - that is fantastic - I just have 2 final quick questions:-

1) On the query there is a column <> that contains 1 zero and no other
information - how can I get rid of that?
2) Can you recommend any good "Teach Yourself SQL" websites or books that
I
could use as a reference to try and solve these problems myself?

Thanks Again

Andi


The <> column is the result of a Null value in the field [Severity/Closure].
Set the criteria to WHERE [Severity/Closure] is not null.
John Viescas has some good book references (including one he
Click to expand...
co-authored)
at
www.viescas.com.
Click to expand...

Thanks Duane but I still have problems - I have used the following
statement
but I now don't have any of the Severity / Closure columns I did before
and
I only have the columns 0 and -1

Any ideas?

Thanks in advance

Andy
Click to expand...
Click to expand...
 
A

Andibevan

I see the problem is that I don't have any values that have an age of less
than 7 days. I am after getting a 0 in one of the severity columns rather
than in a seperate column.

Ta

Andi

Andibevan said:
I get a 0 in the NA column



Duane Hookom said:
See what you return if your SQL is

PIVOT Nz(Queryfour.[Severity/Closure], "NA")


--
Duane Hookom
MS Access MVP


Andibevan said:
Andibevan wrote:

"Marshall Barton" wrote
Andibevan wrote:
I am having trouble creating a query that will provide information
for
input
into a histogram.

I have 3 Fields DefectID, Severity, Age
Severity is 1 of 4 catagories (high, medium, low, critical)

I need to create a query that will count the number of defects of
each
catagory that fit into the following intervals:-

0 - 10 days
11 - 20 days
21 - 30 days

I can manage to create total counts but not according to these
intervals -
any solutions / pointers would be gratefully received.


If you want the total over various age ranges, then the
query will have to group on the age range. For a nice
regular set of ranges like your example, you can use the
Partition function.

SELECT Partition(Age, 1,100,10) As Interval,
Severity, Count(*) As DefectCount
FROM thetable
GROUP BY Partition(Age, 1,100,10), Severity

If your age ranges are not regular, you should the create a
table that defines the ranges. THen you can join that table
to the data table using a non-equi join to get the Interval
field.


My actual query is now as follows:-

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Partition([VarAge],0,110,20) AS Range,
Count(Queryfour.DefectID)
AS
[Total Of DefectID]
FROM Queryfour
GROUP BY Partition([VarAge],0,110,20)
PIVOT Queryfour.[Severity/Closure];

I actually need the intervals to be uneven - as follows - I have
inputted
the date ranges into a table called Table_Intervals and it is setup
as
follows:-
0
7
14
28
56
84
200

How do I set-up a non-equi join to get the interval field as above?


To keep the query managable, I suggest that the table
Intervals look like:
Descr Low High
<=7 0 7
8-14 8 14
15-28 15 28
29-56 29 56
57-84 57 84
84 85 200

Your query would then be changed to:

TRANSFORM Count(Queryfour.DefectID) AS CountOfDefectID
SELECT Intervals.Descr AS Range,
Count(Queryfour.DefectID) AS [Total Of DefectID]
FROM Queryfour RIGHT JOIN Intervals
ON Queryfour.VarAge >= Intervals.Low
AND Queryfour.VarAge <= Intervals.High
GROUP BY Intervals.Descr
PIVOT Queryfour.[Severity/Closure]

--
Marsh
MVP [MS Access]

Hey Marsh,

Thanks so Much - that is fantastic - I just have 2 final quick
questions:-

1) On the query there is a column <> that contains 1 zero and no other
information - how can I get rid of that?
2) Can you recommend any good "Teach Yourself SQL" websites or books
that
I
could use as a reference to try and solve these problems myself?

Thanks Again

Andi


The <> column is the result of a Null value in the field
[Severity/Closure].
Set the criteria to WHERE [Severity/Closure] is not null.
John Viescas has some good book references (including one he co-authored)
at
www.viescas.com.

--
Duane Hookom
MS Access MVP



Thanks Duane but I still have problems - I have used the following
statement
but I now don't have any of the Severity / Closure columns I did before
and
I only have the columns 0 and -1

Any ideas?

Thanks in advance

Andy
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Newbie Question 1
Sort Query Results 1
Date Brackets as an IIf statement 3
Count frequencies; Pareto of nonumeric values 11
tscmd35.zip Useful NT/2000/XP script tricks and tips 0
History of Xbox 360 defects - Dean Takahashi has the inside story(long) 2
games with C++ source code (hey learn to create your own freeware!!!) 0
SOS Uploads for the Summer Doldrums 5

Top