Counts based on strata

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

Guest

Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
 
Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Thanks for the info. Unfortunately, I'm rather new to SQL, so I have a
couple of questions. First, since I will be pulling the information from a
single source (another query), I don't understand why you have two tables
listed (T and T1). Also, do I need to enter anything specific where the
asterisk is (*) or do I enter it as just that?

Thanks,
Eric

Tom Ellison said:
Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
Click to expand...
Click to expand...
 
One more thing: I copied what you did and replaced the asterisks with the
field name (I assume this is correct). It looks as if I'm almost there, but
the issue now is that each column shows the total for ALL records, not just
those that correspond to the specific name and chart (I'm grouping by two
fields, [WR_LNG_NAM] and [Chart]).

Here is the exact SQL I am using:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak = 1) AS [#1],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 2 AND 10) AS [2-10],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 40) AS [>40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 0) AS [Total]
FROM [*qryAllSongsByWriter]
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];


Thanks,
Eric


Eric Stephens said:
Tom,

Thanks for the info. Unfortunately, I'm rather new to SQL, so I have a
couple of questions. First, since I will be pulling the information from a
single source (another query), I don't understand why you have two tables
listed (T and T1). Also, do I need to enter anything specific where the
asterisk is (*) or do I enter it as just that?

Thanks,
Eric

Tom Ellison said:
Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
Click to expand...
Click to expand...
Click to expand...
 
Dear Eric:

The "two tables (T and T1)" are not two tables, but two independent
references to the same table. Both refer to the single table I called
YourTable, but operate independently. This is essential to the
technique used.

The asterisk should be fine as is.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Thanks for the info. Unfortunately, I'm rather new to SQL, so I have a
couple of questions. First, since I will be pulling the information from a
single source (another query), I don't understand why you have two tables
listed (T and T1). Also, do I need to enter anything specific where the
asterisk is (*) or do I enter it as just that?

Thanks,
Eric

Tom Ellison said:
Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
Click to expand...
Click to expand...
Click to expand...
 
Dear Eric:

Please try this:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter] T1
WHERE T1.[WR_LNG_NAM] = T.[WR_LNG_NAM] AND T1.[Chart] = T.Chart
AND Peak = 1) AS [#1]
FROM [*qryAllSongsByWriter] T
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];

If that works correctly for the first column, just make the same
adjustment to the other columns as I showed them before.

The difference between using a * and a field name in the COUNT depends
solely on whether that column might be null. If you specify a column
and it is null, then it is not counted. Using * all rows are counted.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


One more thing: I copied what you did and replaced the asterisks with the
field name (I assume this is correct). It looks as if I'm almost there, but
the issue now is that each column shows the total for ALL records, not just
those that correspond to the specific name and chart (I'm grouping by two
fields, [WR_LNG_NAM] and [Chart]).

Here is the exact SQL I am using:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak = 1) AS [#1],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 2 AND 10) AS [2-10],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 40) AS [>40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 0) AS [Total]
FROM [*qryAllSongsByWriter]
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];


Thanks,
Eric


Eric Stephens said:
Tom,

Thanks for the info. Unfortunately, I'm rather new to SQL, so I have a
couple of questions. First, since I will be pulling the information from a
single source (another query), I don't understand why you have two tables
listed (T and T1). Also, do I need to enter anything specific where the
asterisk is (*) or do I enter it as just that?

Thanks,
Eric

Tom Ellison said:
Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 12 Oct 2004 14:25:32 -0700, "Eric Stephens"

Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
Click to expand...
Click to expand...
Click to expand...
 
Tom,

Thanks for the tip. I copied EXACTLY what you listed below, to try it as a
test for the first column only (#1). I let the query run for over 30 minutes
but it never completed. I have a fairly new system with 512MB RAM, so I'm not
sure that my PC specs are to blame (in fact, I've never had a query take this
long to run). The underlying query that this one I'm trying to build is based
on usually takes only about 1 min to run, and currently yields 9,044 records.
I'm not really sure what to do at this point.

Thanks,
Eric

Tom Ellison said:
Dear Eric:

Please try this:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter] T1
WHERE T1.[WR_LNG_NAM] = T.[WR_LNG_NAM] AND T1.[Chart] = T.Chart
AND Peak = 1) AS [#1]
FROM [*qryAllSongsByWriter] T
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];

If that works correctly for the first column, just make the same
adjustment to the other columns as I showed them before.

The difference between using a * and a field name in the COUNT depends
solely on whether that column might be null. If you specify a column
and it is null, then it is not counted. Using * all rows are counted.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


One more thing: I copied what you did and replaced the asterisks with the
field name (I assume this is correct). It looks as if I'm almost there, but
the issue now is that each column shows the total for ALL records, not just
those that correspond to the specific name and chart (I'm grouping by two
fields, [WR_LNG_NAM] and [Chart]).

Here is the exact SQL I am using:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak = 1) AS [#1],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 2 AND 10) AS [2-10],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 40) AS [>40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 0) AS [Total]
FROM [*qryAllSongsByWriter]
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];


Thanks,
Eric


Eric Stephens said:
Tom,

Thanks for the info. Unfortunately, I'm rather new to SQL, so I have a
couple of questions. First, since I will be pulling the information from a
single source (another query), I don't understand why you have two tables
listed (T and T1). Also, do I need to enter anything specific where the
asterisk is (*) or do I enter it as just that?

Thanks,
Eric

:

Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 12 Oct 2004 14:25:32 -0700, "Eric Stephens"

Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
Click to expand...
Click to expand...
Click to expand...
 
Dear Eric:

You can reduce the amount of time it takes to run by filtering it to
just one WR_LNG_NAM (temporarily) so you can determine if it is
working.

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter] T1
WHERE T1.[WR_LNG_NAM] = T.[WR_LNG_NAM] AND T1.[Chart] = T.Chart
AND Peak = 1) AS [#1]
FROM [*qryAllSongsByWriter] T
WHERE WR_LNG_NAM = "JOE"
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];

Of course, you must us an actual name for the value here.

Your performance may be resulting from not having an index on the
table(s) behind this. As you are basing this query on another query,
I don't know where to start on this. Query optimization can result in
huge changes in performance, and my guess is that you're operating on
the wrong side of this issue.

Alternatively, let it run over night, or over a weekend. If you run
it through code you can capture the time it ends and see just how long
it took without sitting and waiting for it. This may not be all that
helpful.

Adding the other columns you wanted will be pretty painful in terms of
performance, obviously.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Thanks for the tip. I copied EXACTLY what you listed below, to try it as a
test for the first column only (#1). I let the query run for over 30 minutes
but it never completed. I have a fairly new system with 512MB RAM, so I'm not
sure that my PC specs are to blame (in fact, I've never had a query take this
long to run). The underlying query that this one I'm trying to build is based
on usually takes only about 1 min to run, and currently yields 9,044 records.
I'm not really sure what to do at this point.

Thanks,
Eric

Tom Ellison said:
Dear Eric:

Please try this:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter] T1
WHERE T1.[WR_LNG_NAM] = T.[WR_LNG_NAM] AND T1.[Chart] = T.Chart
AND Peak = 1) AS [#1]
FROM [*qryAllSongsByWriter] T
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];

If that works correctly for the first column, just make the same
adjustment to the other columns as I showed them before.

The difference between using a * and a field name in the COUNT depends
solely on whether that column might be null. If you specify a column
and it is null, then it is not counted. Using * all rows are counted.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


One more thing: I copied what you did and replaced the asterisks with the
field name (I assume this is correct). It looks as if I'm almost there, but
the issue now is that each column shows the total for ALL records, not just
those that correspond to the specific name and chart (I'm grouping by two
fields, [WR_LNG_NAM] and [Chart]).

Here is the exact SQL I am using:

SELECT [WR_LNG_NAM], [Chart],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak = 1) AS [#1],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 2 AND 10) AS [2-10],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 40) AS [>40],
(SELECT COUNT(Peak) FROM [*qryAllSongsByWriter]
WHERE Peak > 0) AS [Total]
FROM [*qryAllSongsByWriter]
GROUP BY [WR_LNG_NAM], [Chart]
ORDER BY [WR_LNG_NAM], [Chart];


Thanks,
Eric


:

Tom,

Thanks for the info. Unfortunately, I'm rather new to SQL, so I have a
couple of questions. First, since I will be pulling the information from a
single source (another query), I don't understand why you have two tables
listed (T and T1). Also, do I need to enter anything specific where the
asterisk is (*) or do I enter it as just that?

Thanks,
Eric

:

Dear Eric:

While it is possible to do something like this in a crosstab query,
I'm going to suggest an alternative. If the categories you show are
static and not based on ranges set up in a table where you could
reconfigure them, then a set of subqueries will do this:

SELECT [Name],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 1 AND 10) AS [1-10],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 11 AND 20) AS [11-20],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 21 AND 30) AS [21-30],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position BETWEEN 31 AND 40) AS [31-40],
(SELECT COUNT(*) FROM YourTable T1
WHERE T1.Name = T.Name
AND Position > 40) AS [<40]
FROM YourTable T
GROUP BY [Name]

This illustrates that crosstabs are something that can be accomplished
using other means. The crosstab facility in Access is a shortcut to
doing that dynamically without coding. But it is also restrictive.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 12 Oct 2004 14:25:32 -0700, "Eric Stephens"

Hello,

I am trying to develop a query that will count the number of records in
which the value of a particular field falls within a specified range. For
example, if I have the following information:

Name Position
Mike 1
Mike 7
Mike 14
Mike 18
Mike 25
Mike 33
Mike 35
Mike 52

I want to get the following output:

Name 1-10 11-20 21-30 31-40 >40
Total
Mike 2 2 1 2
1 8

I am completely baffled on how to go about this. I tried using a Count
function in the Total row and entering the ranges in the Criteria row (in
Design view) for each of the ranges, but I get no records returned when I do
this. Even if there are no matching records within one of the ranges, I want
it to show as 0 for that range. Any help would be greatly appreciated! BTW,
my SQL skills are minimal at best.

Thanks,
Eric
Click to expand...
Click to expand...
Click to expand...
 
Back
Top