multiple grouped counts in one query

J

jmoore

I have a query based on a query that has a calculated field to display the
results, in days, between two date fields. The example below gives me the
count of records between 0 and 45 days. I also want to calculate 3 other
ranges. I can get the results I need with 4 queries, but it seems there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
 
V

vanderghast

TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the intervals are
regular (ie. always by step of 45, rather than, like here, 90-120 is a step
of 30 )


Vanderghast, Access MVP
 
J

jmoore

Thank you. I got it to work after a couple of error messages. I copied the
code below that works, but I would like to understand why. I have other
queries in the same database that work without the brackets around the query
name, but this one did not. Access also added "AS CountOfRefBeforeOpen" in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



vanderghast said:
TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the intervals are
regular (ie. always by step of 45, rather than, like here, 90-120 is a step
of 30 )


Vanderghast, Access MVP

jmoore said:
I have a query based on a query that has a calculated field to display the
results, in days, between two date fields. The example below gives me the
count of records between 0 and 45 days. I also want to calculate 3 other
ranges. I can get the results I need with 4 queries, but it seems there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
Click to expand...
Click to expand...
 
V

vanderghast

You need the [ ] around the query name since otherwise, that name will be
seen with a subtraction, and there is a space in the name: that is not a
legal name, so you need [ ] around it.

The PIVOT clause determines the new columns. Here, we would create columns
"[less than 0[" ,"[0-45[" ,"[45-60[", and ,"[60+"
(which are also illegal names, by the way).

Under each of these new columns, for each group, which are for every
CNTYNAME value, the COUNT of not null values in RefBeforeOpen is computed.
The alias supplied by the query designer, CountOfRefBeforeOpen, is not use
furthermore but it could be use if we were interested by reaching each
"cell" of the tableau of COUNTs, in our SQL Statement:


For a given record, in the initial table (here, [qsubReport-Sect F] used as
if it was a table), to know under which of the new columns a record will
increase the COUNT, it is a matter 'decided' by the SWITCH statement: the
first time a condition evaluates to true, the 'name' following that said
condition will be the name of the new column for which this record will
increase the COUNT.

As example, if RefBeforeOpen = 32 for a record, then


Switch( RefBeforeOpen<0, "[less than 0[",
RefBeforeOpen<45, "[0-45[",
RefBeforeOpen<90, "[45-60[",
True, "[60+" )


would return "[0-45[" and the count under that column will be increased.


Vanderghast, Access MVP



jmoore said:
Thank you. I got it to work after a couple of error messages. I copied
the
code below that works, but I would like to understand why. I have other
queries in the same database that work without the brackets around the
query
name, but this one did not. Access also added "AS CountOfRefBeforeOpen"
in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



vanderghast said:
TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the intervals
are
regular (ie. always by step of 45, rather than, like here, 90-120 is a
step
of 30 )


Vanderghast, Access MVP

jmoore said:
I have a query based on a query that has a calculated field to display
the
results, in days, between two date fields. The example below gives me
the
count of records between 0 and 45 days. I also want to calculate 3
other
ranges. I can get the results I need with 4 queries, but it seems
there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
Click to expand...
Click to expand...
Click to expand...
 
J

jmoore

Thank you. Now I see why other queries worked without the brackets.

In the same query, I have another calculated field, MedNeeds. I need to get
similar information, but with different ranges. Can I combine them both into
one query, or do I have to create two queries first, and them combine them?

vanderghast said:
You need the [ ] around the query name since otherwise, that name will be
seen with a subtraction, and there is a space in the name: that is not a
legal name, so you need [ ] around it.

The PIVOT clause determines the new columns. Here, we would create columns
"[less than 0[" ,"[0-45[" ,"[45-60[", and ,"[60+"
(which are also illegal names, by the way).

Under each of these new columns, for each group, which are for every
CNTYNAME value, the COUNT of not null values in RefBeforeOpen is computed.
The alias supplied by the query designer, CountOfRefBeforeOpen, is not use
furthermore but it could be use if we were interested by reaching each
"cell" of the tableau of COUNTs, in our SQL Statement:


For a given record, in the initial table (here, [qsubReport-Sect F] used as
if it was a table), to know under which of the new columns a record will
increase the COUNT, it is a matter 'decided' by the SWITCH statement: the
first time a condition evaluates to true, the 'name' following that said
condition will be the name of the new column for which this record will
increase the COUNT.

As example, if RefBeforeOpen = 32 for a record, then


Switch( RefBeforeOpen<0, "[less than 0[",
RefBeforeOpen<45, "[0-45[",
RefBeforeOpen<90, "[45-60[",
True, "[60+" )


would return "[0-45[" and the count under that column will be increased.


Vanderghast, Access MVP



jmoore said:
Thank you. I got it to work after a couple of error messages. I copied
the
code below that works, but I would like to understand why. I have other
queries in the same database that work without the brackets around the
query
name, but this one did not. Access also added "AS CountOfRefBeforeOpen"
in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



vanderghast said:
TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the intervals
are
regular (ie. always by step of 45, rather than, like here, 90-120 is a
step
of 30 )


Vanderghast, Access MVP

I have a query based on a query that has a calculated field to display
the
results, in days, between two date fields. The example below gives me
the
count of records between 0 and 45 days. I also want to calculate 3
other
ranges. I can get the results I need with 4 queries, but it seems
there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

It would be more efficient and simple to maintain (in three months, or in
three years) with a second crosstab query and eventually, 'merge' the two
crosstabs, if, and when, required.

Vanderghast, Access MVP


jmoore said:
Thank you. Now I see why other queries worked without the brackets.

In the same query, I have another calculated field, MedNeeds. I need to
get
similar information, but with different ranges. Can I combine them both
into
one query, or do I have to create two queries first, and them combine
them?

vanderghast said:
You need the [ ] around the query name since otherwise, that name will be
seen with a subtraction, and there is a space in the name: that is not a
legal name, so you need [ ] around it.

The PIVOT clause determines the new columns. Here, we would create
columns
"[less than 0[" ,"[0-45[" ,"[45-60[", and ,"[60+"
(which are also illegal names, by the way).

Under each of these new columns, for each group, which are for every
CNTYNAME value, the COUNT of not null values in RefBeforeOpen is
computed.
The alias supplied by the query designer, CountOfRefBeforeOpen, is not
use
furthermore but it could be use if we were interested by reaching each
"cell" of the tableau of COUNTs, in our SQL Statement:


For a given record, in the initial table (here, [qsubReport-Sect F] used
as
if it was a table), to know under which of the new columns a record will
increase the COUNT, it is a matter 'decided' by the SWITCH statement: the
first time a condition evaluates to true, the 'name' following that said
condition will be the name of the new column for which this record will
increase the COUNT.

As example, if RefBeforeOpen = 32 for a record, then


Switch( RefBeforeOpen<0, "[less than 0[",
RefBeforeOpen<45, "[0-45[",
RefBeforeOpen<90, "[45-60[",
True, "[60+" )


would return "[0-45[" and the count under that column will be
increased.


Vanderghast, Access MVP



jmoore said:
Thank you. I got it to work after a couple of error messages. I
copied
the
code below that works, but I would like to understand why. I have
other
queries in the same database that work without the brackets around the
query
name, but this one did not. Access also added "AS
CountOfRefBeforeOpen"
in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS
CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



:

TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the
intervals
are
regular (ie. always by step of 45, rather than, like here, 90-120 is a
step
of 30 )


Vanderghast, Access MVP

I have a query based on a query that has a calculated field to
display
the
results, in days, between two date fields. The example below gives
me
the
count of records between 0 and 45 days. I also want to calculate 3
other
ranges. I can get the results I need with 4 queries, but it seems
there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
Click to expand...
Click to expand...
Click to expand...
 
J

jmoore

I need to merge them now for a report, but it makes sense to have them
separate for future updates.

The first column created in the SQL statement (<0) is the column furthest to
the right in the datasheet view. Is this normal, or am I missing something?

vanderghast said:
It would be more efficient and simple to maintain (in three months, or in
three years) with a second crosstab query and eventually, 'merge' the two
crosstabs, if, and when, required.

Vanderghast, Access MVP


jmoore said:
Thank you. Now I see why other queries worked without the brackets.

In the same query, I have another calculated field, MedNeeds. I need to
get
similar information, but with different ranges. Can I combine them both
into
one query, or do I have to create two queries first, and them combine
them?

vanderghast said:
You need the [ ] around the query name since otherwise, that name will be
seen with a subtraction, and there is a space in the name: that is not a
legal name, so you need [ ] around it.

The PIVOT clause determines the new columns. Here, we would create
columns
"[less than 0[" ,"[0-45[" ,"[45-60[", and ,"[60+"
(which are also illegal names, by the way).

Under each of these new columns, for each group, which are for every
CNTYNAME value, the COUNT of not null values in RefBeforeOpen is
computed.
The alias supplied by the query designer, CountOfRefBeforeOpen, is not
use
furthermore but it could be use if we were interested by reaching each
"cell" of the tableau of COUNTs, in our SQL Statement:


For a given record, in the initial table (here, [qsubReport-Sect F] used
as
if it was a table), to know under which of the new columns a record will
increase the COUNT, it is a matter 'decided' by the SWITCH statement: the
first time a condition evaluates to true, the 'name' following that said
condition will be the name of the new column for which this record will
increase the COUNT.

As example, if RefBeforeOpen = 32 for a record, then


Switch( RefBeforeOpen<0, "[less than 0[",
RefBeforeOpen<45, "[0-45[",
RefBeforeOpen<90, "[45-60[",
True, "[60+" )


would return "[0-45[" and the count under that column will be
increased.


Vanderghast, Access MVP



Thank you. I got it to work after a couple of error messages. I
copied
the
code below that works, but I would like to understand why. I have
other
queries in the same database that work without the brackets around the
query
name, but this one did not. Access also added "AS
CountOfRefBeforeOpen"
in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS
CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



:

TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the
intervals
are
regular (ie. always by step of 45, rather than, like here, 90-120 is a
step
of 30 )


Vanderghast, Access MVP

I have a query based on a query that has a calculated field to
display
the
results, in days, between two date fields. The example below gives
me
the
count of records between 0 and 45 days. I also want to calculate 3
other
ranges. I can get the results I need with 4 queries, but it seems
there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
Click to expand...
Click to expand...
Click to expand...
 
J

jmoore

I need to know how many cases of the MedNeeds field are less than a negative
30, and how many are between a negative 30 and zero. Is this the correct way
or do I have to add something to the negative sign?

PIVOT Switch([qSubReport-Sect F].MedNeeds < -30,"less than minus 30",
MedNeeds<=0,"[minus 30 To 0[",
MedNeeds<=30,"[0 To 30[",
True,"[31+");


vanderghast said:
It would be more efficient and simple to maintain (in three months, or in
three years) with a second crosstab query and eventually, 'merge' the two
crosstabs, if, and when, required.

Vanderghast, Access MVP


jmoore said:
Thank you. Now I see why other queries worked without the brackets.

In the same query, I have another calculated field, MedNeeds. I need to
get
similar information, but with different ranges. Can I combine them both
into
one query, or do I have to create two queries first, and them combine
them?

vanderghast said:
You need the [ ] around the query name since otherwise, that name will be
seen with a subtraction, and there is a space in the name: that is not a
legal name, so you need [ ] around it.

The PIVOT clause determines the new columns. Here, we would create
columns
"[less than 0[" ,"[0-45[" ,"[45-60[", and ,"[60+"
(which are also illegal names, by the way).

Under each of these new columns, for each group, which are for every
CNTYNAME value, the COUNT of not null values in RefBeforeOpen is
computed.
The alias supplied by the query designer, CountOfRefBeforeOpen, is not
use
furthermore but it could be use if we were interested by reaching each
"cell" of the tableau of COUNTs, in our SQL Statement:


For a given record, in the initial table (here, [qsubReport-Sect F] used
as
if it was a table), to know under which of the new columns a record will
increase the COUNT, it is a matter 'decided' by the SWITCH statement: the
first time a condition evaluates to true, the 'name' following that said
condition will be the name of the new column for which this record will
increase the COUNT.

As example, if RefBeforeOpen = 32 for a record, then


Switch( RefBeforeOpen<0, "[less than 0[",
RefBeforeOpen<45, "[0-45[",
RefBeforeOpen<90, "[45-60[",
True, "[60+" )


would return "[0-45[" and the count under that column will be
increased.


Vanderghast, Access MVP



Thank you. I got it to work after a couple of error messages. I
copied
the
code below that works, but I would like to understand why. I have
other
queries in the same database that work without the brackets around the
query
name, but this one did not. Access also added "AS
CountOfRefBeforeOpen"
in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS
CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



:

TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the
intervals
are
regular (ie. always by step of 45, rather than, like here, 90-120 is a
step
of 30 )


Vanderghast, Access MVP

I have a query based on a query that has a calculated field to
display
the
results, in days, between two date fields. The example below gives
me
the
count of records between 0 and 45 days. I also want to calculate 3
other
ranges. I can get the results I need with 4 queries, but it seems
there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
Click to expand...
Click to expand...
Click to expand...
 
J

jmoore

Sorry for the multiple posts. I posed the question about negtive numbers
because I received an error message and I thought it was because I needed to
designate the negative number differently. But I think the problem is the
calculated field.

MedNeeds: IIf([F6]=#1/1/9999#,"Not App",IIf([F6]=#1/1/7777#,"Not
Avail",DateDiff("d",[F6],[F3])))

How would I pull the count only for those records where a calculation was
performed?

Thanks for your help.

vanderghast said:
It would be more efficient and simple to maintain (in three months, or in
three years) with a second crosstab query and eventually, 'merge' the two
crosstabs, if, and when, required.

Vanderghast, Access MVP


jmoore said:
Thank you. Now I see why other queries worked without the brackets.

In the same query, I have another calculated field, MedNeeds. I need to
get
similar information, but with different ranges. Can I combine them both
into
one query, or do I have to create two queries first, and them combine
them?

vanderghast said:
You need the [ ] around the query name since otherwise, that name will be
seen with a subtraction, and there is a space in the name: that is not a
legal name, so you need [ ] around it.

The PIVOT clause determines the new columns. Here, we would create
columns
"[less than 0[" ,"[0-45[" ,"[45-60[", and ,"[60+"
(which are also illegal names, by the way).

Under each of these new columns, for each group, which are for every
CNTYNAME value, the COUNT of not null values in RefBeforeOpen is
computed.
The alias supplied by the query designer, CountOfRefBeforeOpen, is not
use
furthermore but it could be use if we were interested by reaching each
"cell" of the tableau of COUNTs, in our SQL Statement:


For a given record, in the initial table (here, [qsubReport-Sect F] used
as
if it was a table), to know under which of the new columns a record will
increase the COUNT, it is a matter 'decided' by the SWITCH statement: the
first time a condition evaluates to true, the 'name' following that said
condition will be the name of the new column for which this record will
increase the COUNT.

As example, if RefBeforeOpen = 32 for a record, then


Switch( RefBeforeOpen<0, "[less than 0[",
RefBeforeOpen<45, "[0-45[",
RefBeforeOpen<90, "[45-60[",
True, "[60+" )


would return "[0-45[" and the count under that column will be
increased.


Vanderghast, Access MVP



Thank you. I got it to work after a couple of error messages. I
copied
the
code below that works, but I would like to understand why. I have
other
queries in the same database that work without the brackets around the
query
name, but this one did not. Access also added "AS
CountOfRefBeforeOpen"
in
the transform statement - I did not type it.

I am trying to learn SQL on my own. Would you know where I could find
tutorials/information that would be helpful? Thanks.

TRANSFORM Count([qsubReport-Sect F].RefBeforeOpen) AS
CountOfRefBeforeOpen
SELECT [qsubReport-Sect F].CNTYNAME
FROM [qsubReport-Sect F]
GROUP BY [qsubReport-Sect F].CNTYNAME
PIVOT Switch([qSubReport-Sect F].RefBeforeOpen<0,"[less than 0[",
RefBeforeOpen<45,"[0-45[",
RefBeforeOpen<90,"[45-60[",
True,"[60+");



:

TRANSFORM COUNT(RefBeforeOpen)
SELECT cntyname
FROM [qsubReport-Sect F]
GROUP BY cntyname
PIVOT SWITCH( RefBeforeOpen <=0, "up to 0 incl.",
RefBeforeOpen <45, "[0-45[",
RefBeforeOpen< 90, "[45-90[",
RefBeforeOpen< 120, "[90-120[",
True, "[120+" )


You can also use the predefined VBA function PARTITION if the
intervals
are
regular (ie. always by step of 45, rather than, like here, 90-120 is a
step
of 30 )


Vanderghast, Access MVP

I have a query based on a query that has a calculated field to
display
the
results, in days, between two date fields. The example below gives
me
the
count of records between 0 and 45 days. I also want to calculate 3
other
ranges. I can get the results I need with 4 queries, but it seems
there
should be a better way to do it to. Any advice?

SELECT DISTINCT [qSubReport-Sect F].CNTYNAME,
Count([qSubReport-SectF].RefBeforeOpen) AS UnderZero
FROM [qSubReport-Sect F]
WHERE (([qSubReport-Sect F].RefBeforeOpen) BETWEEN 0 AND 45
GROUP BY [qSubReport-Sect F].CNTYNAME;
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

calculations in report controls 2
Totals Query with Multiple Counts 3
Complex Count Query 0
Number Items Grouped in a Query 0
IIf statement in query criteria 3
Why is my query taking so long? 7
Calculated field to group numbers 2
'Data Type Mismatch In Criteria Expression' error when using 'Count' in query Design; Help! 0

Top