crosstab or looping statement help

[jmoore]

I hope what I need to do is possible with the database I have. My experience
with VBA is very minimal. The database consists of two related tables, with
6 look-up tables that contain answer possibilities (e.g., 1(yes), 2(no),
3(not avail), 4(not applic), 9(missing)). One table was set up to mimic a
data collection tool, where each variable corresponds to a question on the
tool. I need to produce a report with the total number, and percent, of
responses for each answer option by county. I was able to create a crosstab
query that produces the number of responses for only one question with the
SQL code:

TRANSFORM Count([qReview Sample].ID) AS CountOfID
SELECT [qReview Sample].A1, Count([qReview Sample].ID) AS [Total Of ID]
FROM [qReview Sample]
GROUP BY [qReview Sample].A1
PIVOT [qReview Sample].CNTYNAME;

Now I need to repeat this for each question and calculate the percentage of
each answer option (e.g., number who answered 1 divided by the count of ID).
I am using Access 2003, but the database is 2000 file format. I expect this
needs to be done with looping code, but I don’t know how. I’d love to hear
there is an easier way, but I would be thrilled if it can be done at all.

Your help is greatly appreciated.

 

[Duane Hookom]

What is your actual table structure(s)? Do you have each answer as a
different field rather than normalized table structure where each answer to
each question creates a different record?

If you have multiple question fields, you may need to start by creating a
normalizing union query. You can then aggregate your responses to get
percents and counts.

 

[jmoore]

One table (2007 Sample) contains the Case ID number and county (the same ID
number can appear in multiple counties). The primary key (Key) is an auto
number. The other table (tReview) has another auto number primary key with
Key from 2007 Sample table as the foreign key. Each field in tReview is a
question, and each record is the answer to all questions for each ID. There
are six additional tables that provide answer options (from 2 to 5 options in
various combinations).

From your reply, it looks like I need to create a normalizing union query.
Can you please explain how to do this, or provide an example.

Thanks very much.
Joan

What is your actual table structure(s)? Do you have each answer as a
different field rather than normalized table structure where each answer to
each question creates a different record?

If you have multiple question fields, you may need to start by creating a
normalizing union query. You can then aggregate your responses to get
percents and counts.

--
Duane Hookom
Microsoft Access MVP

I hope what I need to do is possible with the database I have. My experience
with VBA is very minimal. The database consists of two related tables, with
6 look-up tables that contain answer possibilities (e.g., 1(yes), 2(no),
3(not avail), 4(not applic), 9(missing)). One table was set up to mimic a
data collection tool, where each variable corresponds to a question on the
tool. I need to produce a report with the total number, and percent, of
responses for each answer option by county. I was able to create a crosstab
query that produces the number of responses for only one question with the
SQL code:

TRANSFORM Count([qReview Sample].ID) AS CountOfID
SELECT [qReview Sample].A1, Count([qReview Sample].ID) AS [Total Of ID]
FROM [qReview Sample]
GROUP BY [qReview Sample].A1
PIVOT [qReview Sample].CNTYNAME;

Now I need to repeat this for each question and calculate the percentage of
each answer option (e.g., number who answered 1 divided by the count of ID).
I am using Access 2003, but the database is 2000 file format. I expect this
needs to be done with looping code, but I don’t know how. I’d love to hear
there is an easier way, but I would be thrilled if it can be done at all.

Your help is greatly appreciated.

 

[Duane Hookom]

If you want help writing SQL, we really need to know your table and field
names. However, try something like:
SELECT [FKID], 1 as Question, [A1] as Answer
FROM tReview
UNION ALL
SELECT [FKID], 2, [A2]
FROM tReview
UNION ALL
SELECT [FKID], 3, [A3]
FROM tReview
UNION ALL
-- etc --
SELECT [FKID], N, [AN]
FROM tReview;
Once you have the union query, you should be able to aggregate the results
with a totals query.


--
Duane Hookom
Microsoft Access MVP

One table (2007 Sample) contains the Case ID number and county (the same ID
number can appear in multiple counties). The primary key (Key) is an auto
number. The other table (tReview) has another auto number primary key with
Key from 2007 Sample table as the foreign key. Each field in tReview is a
question, and each record is the answer to all questions for each ID. There
are six additional tables that provide answer options (from 2 to 5 options in
various combinations).

From your reply, it looks like I need to create a normalizing union query.
Can you please explain how to do this, or provide an example.

Thanks very much.
Joan

What is your actual table structure(s)? Do you have each answer as a
different field rather than normalized table structure where each answer to
each question creates a different record?

If you have multiple question fields, you may need to start by creating a
normalizing union query. You can then aggregate your responses to get
percents and counts.

--
Duane Hookom
Microsoft Access MVP

I hope what I need to do is possible with the database I have. My experience
with VBA is very minimal. The database consists of two related tables, with
6 look-up tables that contain answer possibilities (e.g., 1(yes), 2(no),
3(not avail), 4(not applic), 9(missing)). One table was set up to mimic a
data collection tool, where each variable corresponds to a question on the
tool. I need to produce a report with the total number, and percent, of
responses for each answer option by county. I was able to create a crosstab
query that produces the number of responses for only one question with the
SQL code:

TRANSFORM Count([qReview Sample].ID) AS CountOfID
SELECT [qReview Sample].A1, Count([qReview Sample].ID) AS [Total Of ID]
FROM [qReview Sample]
GROUP BY [qReview Sample].A1
PIVOT [qReview Sample].CNTYNAME;

Now I need to repeat this for each question and calculate the percentage of
each answer option (e.g., number who answered 1 divided by the count of ID).
I am using Access 2003, but the database is 2000 file format. I expect this
needs to be done with looping code, but I don’t know how. I’d love to hear
there is an easier way, but I would be thrilled if it can be done at all.

Your help is greatly appreciated.

 

[jmoore]

Thanks for the reply. I thought it would be more efficient to paste a screen
shot here, but I was not able to. The fields I need in the crosstab are:

2007 Sample.Key
2007 Sample.CNTYNAME
2007 Sample.ID
tReview.ReviewKey
tReview.Key
tReview.A1
tReview.A2
tReview.A3, etc.

Again, thanks for your time and assistance.

If you want help writing SQL, we really need to know your table and field
names. However, try something like:
SELECT [FKID], 1 as Question, [A1] as Answer
FROM tReview
UNION ALL
SELECT [FKID], 2, [A2]
FROM tReview
UNION ALL
SELECT [FKID], 3, [A3]
FROM tReview
UNION ALL
-- etc --
SELECT [FKID], N, [AN]
FROM tReview;
Once you have the union query, you should be able to aggregate the results
with a totals query.


--
Duane Hookom
Microsoft Access MVP

One table (2007 Sample) contains the Case ID number and county (the same ID
number can appear in multiple counties). The primary key (Key) is an auto
number. The other table (tReview) has another auto number primary key with
Key from 2007 Sample table as the foreign key. Each field in tReview is a
question, and each record is the answer to all questions for each ID. There
are six additional tables that provide answer options (from 2 to 5 options in
various combinations).

From your reply, it looks like I need to create a normalizing union query.
Can you please explain how to do this, or provide an example.

Thanks very much.
Joan

What is your actual table structure(s)? Do you have each answer as a
different field rather than normalized table structure where each answer to
each question creates a different record?

If you have multiple question fields, you may need to start by creating a
normalizing union query. You can then aggregate your responses to get
percents and counts.

--
Duane Hookom
Microsoft Access MVP


:

I hope what I need to do is possible with the database I have. My experience
with VBA is very minimal. The database consists of two related tables, with
6 look-up tables that contain answer possibilities (e.g., 1(yes), 2(no),
3(not avail), 4(not applic), 9(missing)). One table was set up to mimic a
data collection tool, where each variable corresponds to a question on the
tool. I need to produce a report with the total number, and percent, of
responses for each answer option by county. I was able to create a crosstab
query that produces the number of responses for only one question with the
SQL code:

TRANSFORM Count([qReview Sample].ID) AS CountOfID
SELECT [qReview Sample].A1, Count([qReview Sample].ID) AS [Total Of ID]
FROM [qReview Sample]
GROUP BY [qReview Sample].A1
PIVOT [qReview Sample].CNTYNAME;

Now I need to repeat this for each question and calculate the percentage of
each answer option (e.g., number who answered 1 divided by the count of ID).
I am using Access 2003, but the database is 2000 file format. I expect this
needs to be done with looping code, but I don’t know how. I’d love to hear
there is an easier way, but I would be thrilled if it can be done at all.

Your help is greatly appreciated.

 

[jmoore]

Hooray, this is so exciting! I was able to create a query that displays one
question, the answer and ReviewKey in one record. I also need the county
name from another table (2007 Sample.CNTYNAME). Should I select from a query
instead of the tReview table to include fields from both tables?

Also, what does the N and [AN] stand for in the last line of your sample
code, SELECT [FKID], N, [AN]? I included that and Access interpreted it as a
parameter.

SELECT [tReview].ReviewKey, "A1a" as Question, [A1a] as Answer
FROM tReview
UNION ALL
SELECT [tReview].ReviewKey, "A1b" as Question, [A1b] as Answer
FROM tReview
UNION ALL SELECT [tReview].ReviewKey, "A2" as Question, [A2] as Answer
FROM tReview;

Thanks for the reply. I thought it would be more efficient to paste a screen
shot here, but I was not able to. The fields I need in the crosstab are:

2007 Sample.Key
2007 Sample.CNTYNAME
2007 Sample.ID
tReview.ReviewKey
tReview.Key
tReview.A1
tReview.A2
tReview.A3, etc.

Again, thanks for your time and assistance.

If you want help writing SQL, we really need to know your table and field
names. However, try something like:
SELECT [FKID], 1 as Question, [A1] as Answer
FROM tReview
UNION ALL
SELECT [FKID], 2, [A2]
FROM tReview
UNION ALL
SELECT [FKID], 3, [A3]
FROM tReview
UNION ALL
-- etc --
SELECT [FKID], N, [AN]
FROM tReview;
Once you have the union query, you should be able to aggregate the results
with a totals query.


--
Duane Hookom
Microsoft Access MVP

One table (2007 Sample) contains the Case ID number and county (the same ID
number can appear in multiple counties). The primary key (Key) is an auto
number. The other table (tReview) has another auto number primary key with
Key from 2007 Sample table as the foreign key. Each field in tReview is a
question, and each record is the answer to all questions for each ID. There
are six additional tables that provide answer options (from 2 to 5 options in
various combinations).

From your reply, it looks like I need to create a normalizing union query.
Can you please explain how to do this, or provide an example.

Thanks very much.
Joan

:

What is your actual table structure(s)? Do you have each answer as a
different field rather than normalized table structure where each answer to
each question creates a different record?

If you have multiple question fields, you may need to start by creating a
normalizing union query. You can then aggregate your responses to get
percents and counts.

--
Duane Hookom
Microsoft Access MVP


:

I hope what I need to do is possible with the database I have. My experience
with VBA is very minimal. The database consists of two related tables, with
6 look-up tables that contain answer possibilities (e.g., 1(yes), 2(no),
3(not avail), 4(not applic), 9(missing)). One table was set up to mimic a
data collection tool, where each variable corresponds to a question on the
tool. I need to produce a report with the total number, and percent, of
responses for each answer option by county. I was able to create a crosstab
query that produces the number of responses for only one question with the
SQL code:

TRANSFORM Count([qReview Sample].ID) AS CountOfID
SELECT [qReview Sample].A1, Count([qReview Sample].ID) AS [Total Of ID]
FROM [qReview Sample]
GROUP BY [qReview Sample].A1
PIVOT [qReview Sample].CNTYNAME;

Now I need to repeat this for each question and calculate the percentage of
each answer option (e.g., number who answered 1 divided by the count of ID).
I am using Access 2003, but the database is 2000 file format. I expect this
needs to be done with looping code, but I don’t know how. I’d love to hear
there is an easier way, but I would be thrilled if it can be done at all.

Your help is greatly appreciated.

 

[Duane Hookom]

The AN is just a place holder for the last field in your table since I didn't
know what your field names were.

You should be able to link the union query to the table with the county in
it. This might require adding another field to each section of the union
query.


--
Duane Hookom
Microsoft Access MVP

Hooray, this is so exciting! I was able to create a query that displays one
question, the answer and ReviewKey in one record. I also need the county
name from another table (2007 Sample.CNTYNAME). Should I select from a query
instead of the tReview table to include fields from both tables?

Also, what does the N and [AN] stand for in the last line of your sample
code, SELECT [FKID], N, [AN]? I included that and Access interpreted it as a
parameter.

SELECT [tReview].ReviewKey, "A1a" as Question, [A1a] as Answer
FROM tReview
UNION ALL
SELECT [tReview].ReviewKey, "A1b" as Question, [A1b] as Answer
FROM tReview
UNION ALL SELECT [tReview].ReviewKey, "A2" as Question, [A2] as Answer
FROM tReview;

Hooray, this is so exciting! I was able to create a query that displays one
question, the answer and ReviewKey in one record. I also need the county
name from another table (2007 Sample.CNTYNAME). Should I select from a query
instead of the tReview table to include fields from both tables?

Also, what does the N and [AN] stand for in the last line of your sample
code, SELECT [FKID], N, [AN]? I included that and Access interpreted it as a
parameter.

SELECT [tReview].ReviewKey, "A1a" as Question, [A1a] as Answer
FROM tReview
UNION ALL
SELECT [tReview].ReviewKey, "A1b" as Question, [A1b] as Answer
FROM tReview
UNION ALL SELECT [tReview].ReviewKey, "A2" as Question, [A2] as Answer
FROM tReview;

Thanks for the reply. I thought it would be more efficient to paste a screen
shot here, but I was not able to. The fields I need in the crosstab are:

2007 Sample.Key
2007 Sample.CNTYNAME
2007 Sample.ID
tReview.ReviewKey
tReview.Key
tReview.A1
tReview.A2
tReview.A3, etc.

Again, thanks for your time and assistance.

:

If you want help writing SQL, we really need to know your table and field
names. However, try something like:
SELECT [FKID], 1 as Question, [A1] as Answer
FROM tReview
UNION ALL
SELECT [FKID], 2, [A2]
FROM tReview
UNION ALL
SELECT [FKID], 3, [A3]
FROM tReview
UNION ALL
-- etc --
SELECT [FKID], N, [AN]
FROM tReview;
Once you have the union query, you should be able to aggregate the results
with a totals query.


--
Duane Hookom
Microsoft Access MVP


:

One table (2007 Sample) contains the Case ID number and county (the same ID
number can appear in multiple counties). The primary key (Key) is an auto
number. The other table (tReview) has another auto number primary key with
Key from 2007 Sample table as the foreign key. Each field in tReview is a
question, and each record is the answer to all questions for each ID. There
are six additional tables that provide answer options (from 2 to 5 options in
various combinations).

From your reply, it looks like I need to create a normalizing union query.
Can you please explain how to do this, or provide an example.

Thanks very much.
Joan

:

What is your actual table structure(s)? Do you have each answer as a
different field rather than normalized table structure where each answer to
each question creates a different record?

If you have multiple question fields, you may need to start by creating a
normalizing union query. You can then aggregate your responses to get
percents and counts.

--
Duane Hookom
Microsoft Access MVP


:

I hope what I need to do is possible with the database I have. My experience
with VBA is very minimal. The database consists of two related tables, with
6 look-up tables that contain answer possibilities (e.g., 1(yes), 2(no),
3(not avail), 4(not applic), 9(missing)). One table was set up to mimic a
data collection tool, where each variable corresponds to a question on the
tool. I need to produce a report with the total number, and percent, of
responses for each answer option by county. I was able to create a crosstab
query that produces the number of responses for only one question with the
SQL code:

TRANSFORM Count([qReview Sample].ID) AS CountOfID
SELECT [qReview Sample].A1, Count([qReview Sample].ID) AS [Total Of ID]
FROM [qReview Sample]
GROUP BY [qReview Sample].A1
PIVOT [qReview Sample].CNTYNAME;

Now I need to repeat this for each question and calculate the percentage of
each answer option (e.g., number who answered 1 divided by the count of ID).
I am using Access 2003, but the database is 2000 file format. I expect this
needs to be done with looping code, but I don’t know how. I’d love to hear
there is an easier way, but I would be thrilled if it can be done at all.

Your help is greatly appreciated.

 

[jmoore]

Sorry for the duplicate posts. I tried several times because I received a
message that it was unsuccessful when I submitted. I tried another one later
that apparently did not go through. I revised the code to use a query. This
is better because I don't need all records in the 2007 Sample table. Will
using a query create any difficulties?

I then created another query based on the union query. I set the Total row
to Group By for CNTYNAME, Question and Answer fields. I added a second
Answer field and set it to Count. This produces the required results (# of
responses for each answer option (1, 2, 3 or 9) for each question by county
(e.g., For countyA there were 22 that answered 1, one that answered 2 and one
who answered 3 to question A1a. So far, good. However, the null responses
are not counted. There is a row for question A1a with a null value in the
answer column, with the count of zero.

Is it possible to produce a count of null values?

SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1a" as Question, [A1a]
as Answer
FROM [qReview Sample]
UNION ALL
SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1b" as Question, [A1b]
as Answer
FROM [qReview Sample]
UNION ALL SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];

Thanks very much for all of your help.

 

[Duane Hookom]

The typical method of displaying the Null records is to create a cartesian
query that includes all distinct possible values. For instance if you have a
table of Shapes and a table of Colors and want to get all combinations of
Shapes and Colors, the query might look like:

--- qcarColorsShapes ---
SELECT DISTINCT [Color], [Shape]
FROM tblColors, tblShapes;

You can then use this cartesian query (qcarColorsShapes) in another with a
join that includes all of the records from qcarColorsShapes.
--
Duane Hookom
Microsoft Access MVP

Sorry for the duplicate posts. I tried several times because I received a
message that it was unsuccessful when I submitted. I tried another one later
that apparently did not go through. I revised the code to use a query. This
is better because I don't need all records in the 2007 Sample table. Will
using a query create any difficulties?

I then created another query based on the union query. I set the Total row
to Group By for CNTYNAME, Question and Answer fields. I added a second
Answer field and set it to Count. This produces the required results (# of
responses for each answer option (1, 2, 3 or 9) for each question by county
(e.g., For countyA there were 22 that answered 1, one that answered 2 and one
who answered 3 to question A1a. So far, good. However, the null responses
are not counted. There is a row for question A1a with a null value in the
answer column, with the count of zero.

Is it possible to produce a count of null values?

SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1a" as Question, [A1a]
as Answer
FROM [qReview Sample]
UNION ALL
SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1b" as Question, [A1b]
as Answer
FROM [qReview Sample]
UNION ALL SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];

Thanks very much for all of your help.

The AN is just a place holder for the last field in your table since I didn't
know what your field names were.

You should be able to link the union query to the table with the county in
it. This might require adding another field to each section of the union
query.

 

[jmoore]

Could you please explain what a cartesian query is, and how I would use that
with the union query.

Thank you.

The typical method of displaying the Null records is to create a cartesian
query that includes all distinct possible values. For instance if you have a
table of Shapes and a table of Colors and want to get all combinations of
Shapes and Colors, the query might look like:

--- qcarColorsShapes ---
SELECT DISTINCT [Color], [Shape]
FROM tblColors, tblShapes;

You can then use this cartesian query (qcarColorsShapes) in another with a
join that includes all of the records from qcarColorsShapes.
--
Duane Hookom
Microsoft Access MVP

Sorry for the duplicate posts. I tried several times because I received a
message that it was unsuccessful when I submitted. I tried another one later
that apparently did not go through. I revised the code to use a query. This
is better because I don't need all records in the 2007 Sample table. Will
using a query create any difficulties?

I then created another query based on the union query. I set the Total row
to Group By for CNTYNAME, Question and Answer fields. I added a second
Answer field and set it to Count. This produces the required results (# of
responses for each answer option (1, 2, 3 or 9) for each question by county
(e.g., For countyA there were 22 that answered 1, one that answered 2 and one
who answered 3 to question A1a. So far, good. However, the null responses
are not counted. There is a row for question A1a with a null value in the
answer column, with the count of zero.

Is it possible to produce a count of null values?

SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1a" as Question, [A1a]
as Answer
FROM [qReview Sample]
UNION ALL
SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1b" as Question, [A1b]
as Answer
FROM [qReview Sample]
UNION ALL SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];

Thanks very much for all of your help.

The AN is just a place holder for the last field in your table since I didn't
know what your field names were.

You should be able to link the union query to the table with the county in
it. This might require adding another field to each section of the union
query.

 

[Duane Hookom]

Apparently you need every answer for every question for every CNTYNAME? If
this is the case, I think you need to union your 6 lookup tables with a
derived column that matches the question value ie: A1a, A1b,...etc. Then
create a cartesian query that select every distinct CNTYNAME with every
distinct value in the new union query.

I don't know what your lookup tables are named or their structure. I also
would expect you to have a table with a record for each CNTYNAME.

--
Duane Hookom
Microsoft Access MVP

Could you please explain what a cartesian query is, and how I would use that
with the union query.

Thank you.

The typical method of displaying the Null records is to create a cartesian
query that includes all distinct possible values. For instance if you have a
table of Shapes and a table of Colors and want to get all combinations of
Shapes and Colors, the query might look like:

--- qcarColorsShapes ---
SELECT DISTINCT [Color], [Shape]
FROM tblColors, tblShapes;

You can then use this cartesian query (qcarColorsShapes) in another with a
join that includes all of the records from qcarColorsShapes.
--
Duane Hookom
Microsoft Access MVP

Sorry for the duplicate posts. I tried several times because I received a
message that it was unsuccessful when I submitted. I tried another one later
that apparently did not go through. I revised the code to use a query. This
is better because I don't need all records in the 2007 Sample table. Will
using a query create any difficulties?

I then created another query based on the union query. I set the Total row
to Group By for CNTYNAME, Question and Answer fields. I added a second
Answer field and set it to Count. This produces the required results (# of
responses for each answer option (1, 2, 3 or 9) for each question by county
(e.g., For countyA there were 22 that answered 1, one that answered 2 and one
who answered 3 to question A1a. So far, good. However, the null responses
are not counted. There is a row for question A1a with a null value in the
answer column, with the count of zero.

Is it possible to produce a count of null values?

SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1a" as Question, [A1a]
as Answer
FROM [qReview Sample]
UNION ALL
SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1b" as Question, [A1b]
as Answer
FROM [qReview Sample]
UNION ALL SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];

Thanks very much for all of your help.


:

The AN is just a place holder for the last field in your table since I didn't
know what your field names were.

You should be able to link the union query to the table with the county in
it. This might require adding another field to each section of the union
query.

 

[jmoore]

Hi Duane,

Yes, and I have accomplished that, but I need more. Ignore the question
about null values; we are not going to allow nulls.

Let me back up a bit and make sure I am making myself clear. There are
varying number of reviews for each county. In the original database, each
record (or row) is one review identified by ReviewKey, and each question is a
field (or column heading).

Your recommendation for a union query was very helpful. The fields in the
union query are CNTYNAME, ReviewKey, Question, and Answer. I then created a
crosstab query using the union query. Using the Union Crosstab Query in the
report resulted in a table format with each county name as a column heading
and a row for each answer with the count for each response. I’ve tried to
show and example below.

CountyA CountyB
A1-1 9 1
A1-2 10 5
A1-3 25 39
A1-4 4 2
A2-1 (etc)

This is fine so far, but I also need to have another column under each
county to show the percentage of total reviews per county for each response.
For example, the total number of reviews [CountOfReviewKey] for CountyA is
12; divided by 9 that answered 1 to question A1; is 75%.

CountOfReviewKey comes from a SELECT DISTINCT on CNTYNAME query. It has
only 2 fields; CNTYNAME and CountOfReviewKey. I would prefer to have the
CountOfReviewKey display on the report, but it’s not critical. But I can’t
find a way to use this with the crosstab query on the report.

In the report, the county names are in the page header and the question
number is in a question header with the answer options in the detail section.
I tried adding a text box in the detail beside the answer text box in the
detail section with the control source set to =([CountyA/12)*100. This gives
the correct answer for each answer, but not very practical when the next
report with different counties has to be done. I tried using a calculation
in the control source, =[CountyA]/([qReview
Count].[CountOfReviewKey]=([qReview Count].[CNTYNAME]="CountyA")), which
didn’t work and it still required “hard coding†the county name.

Please let me know if you have suggestions for other ways to create both
count and percentage columns on the report.

Thanks much.

Apparently you need every answer for every question for every CNTYNAME? If
this is the case, I think you need to union your 6 lookup tables with a
derived column that matches the question value ie: A1a, A1b,...etc. Then
create a cartesian query that select every distinct CNTYNAME with every
distinct value in the new union query.

I don't know what your lookup tables are named or their structure. I also
would expect you to have a table with a record for each CNTYNAME.

--
Duane Hookom
Microsoft Access MVP

Could you please explain what a cartesian query is, and how I would use that
with the union query.

Thank you.

The typical method of displaying the Null records is to create a cartesian
query that includes all distinct possible values. For instance if you have a
table of Shapes and a table of Colors and want to get all combinations of
Shapes and Colors, the query might look like:

--- qcarColorsShapes ---
SELECT DISTINCT [Color], [Shape]
FROM tblColors, tblShapes;

You can then use this cartesian query (qcarColorsShapes) in another with a
join that includes all of the records from qcarColorsShapes.
--
Duane Hookom
Microsoft Access MVP


:

Sorry for the duplicate posts. I tried several times because I received a
message that it was unsuccessful when I submitted. I tried another one later
that apparently did not go through. I revised the code to use a query. This
is better because I don't need all records in the 2007 Sample table. Will
using a query create any difficulties?

I then created another query based on the union query. I set the Total row
to Group By for CNTYNAME, Question and Answer fields. I added a second
Answer field and set it to Count. This produces the required results (# of
responses for each answer option (1, 2, 3 or 9) for each question by county
(e.g., For countyA there were 22 that answered 1, one that answered 2 and one
who answered 3 to question A1a. So far, good. However, the null responses
are not counted. There is a row for question A1a with a null value in the
answer column, with the count of zero.

Is it possible to produce a count of null values?

SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1a" as Question, [A1a]
as Answer
FROM [qReview Sample]
UNION ALL
SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1b" as Question, [A1b]
as Answer
FROM [qReview Sample]
UNION ALL SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];

Thanks very much for all of your help.


:

The AN is just a place holder for the last field in your table since I didn't
know what your field names were.

You should be able to link the union query to the table with the county in
it. This might require adding another field to each section of the union
query.

 

[Duane Hookom]

You should be able to create another crosstab of your distinct query that has
a static derived column of "RowHead" as the Row Heading, First Of
CountOfReviewKey as the Value, and [CNTYName] & "Cnt" as the Column Heading.
This will create a single row query that can be added to a new query with
your existing crosstab so you have all of the values available in your report.
--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Yes, and I have accomplished that, but I need more. Ignore the question
about null values; we are not going to allow nulls.

Let me back up a bit and make sure I am making myself clear. There are
varying number of reviews for each county. In the original database, each
record (or row) is one review identified by ReviewKey, and each question is a
field (or column heading).

Your recommendation for a union query was very helpful. The fields in the
union query are CNTYNAME, ReviewKey, Question, and Answer. I then created a
crosstab query using the union query. Using the Union Crosstab Query in the
report resulted in a table format with each county name as a column heading
and a row for each answer with the count for each response. I’ve tried to
show and example below.

CountyA CountyB
A1-1 9 1
A1-2 10 5
A1-3 25 39
A1-4 4 2
A2-1 (etc)

This is fine so far, but I also need to have another column under each
county to show the percentage of total reviews per county for each response.
For example, the total number of reviews [CountOfReviewKey] for CountyA is
12; divided by 9 that answered 1 to question A1; is 75%.

CountOfReviewKey comes from a SELECT DISTINCT on CNTYNAME query. It has
only 2 fields; CNTYNAME and CountOfReviewKey. I would prefer to have the
CountOfReviewKey display on the report, but it’s not critical. But I can’t
find a way to use this with the crosstab query on the report.

In the report, the county names are in the page header and the question
number is in a question header with the answer options in the detail section.
I tried adding a text box in the detail beside the answer text box in the
detail section with the control source set to =([CountyA/12)*100. This gives
the correct answer for each answer, but not very practical when the next
report with different counties has to be done. I tried using a calculation
in the control source, =[CountyA]/([qReview
Count].[CountOfReviewKey]=([qReview Count].[CNTYNAME]="CountyA")), which
didn’t work and it still required “hard coding†the county name.

Please let me know if you have suggestions for other ways to create both
count and percentage columns on the report.

Thanks much.

Apparently you need every answer for every question for every CNTYNAME? If
this is the case, I think you need to union your 6 lookup tables with a
derived column that matches the question value ie: A1a, A1b,...etc. Then
create a cartesian query that select every distinct CNTYNAME with every
distinct value in the new union query.

I don't know what your lookup tables are named or their structure. I also
would expect you to have a table with a record for each CNTYNAME.

--
Duane Hookom
Microsoft Access MVP

Could you please explain what a cartesian query is, and how I would use that
with the union query.

Thank you.

:

The typical method of displaying the Null records is to create a cartesian
query that includes all distinct possible values. For instance if you have a
table of Shapes and a table of Colors and want to get all combinations of
Shapes and Colors, the query might look like:

--- qcarColorsShapes ---
SELECT DISTINCT [Color], [Shape]
FROM tblColors, tblShapes;

You can then use this cartesian query (qcarColorsShapes) in another with a
join that includes all of the records from qcarColorsShapes.
--
Duane Hookom
Microsoft Access MVP


:

Sorry for the duplicate posts. I tried several times because I received a
message that it was unsuccessful when I submitted. I tried another one later
that apparently did not go through. I revised the code to use a query. This
is better because I don't need all records in the 2007 Sample table. Will
using a query create any difficulties?

I then created another query based on the union query. I set the Total row
to Group By for CNTYNAME, Question and Answer fields. I added a second
Answer field and set it to Count. This produces the required results (# of
responses for each answer option (1, 2, 3 or 9) for each question by county
(e.g., For countyA there were 22 that answered 1, one that answered 2 and one
who answered 3 to question A1a. So far, good. However, the null responses
are not counted. There is a row for question A1a with a null value in the
answer column, with the count of zero.

Is it possible to produce a count of null values?

SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1a" as Question, [A1a]
as Answer
FROM [qReview Sample]
UNION ALL
SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A1b" as Question, [A1b]
as Answer
FROM [qReview Sample]
UNION ALL SELECT [2007 Sample].CNTYNAME, [tReview].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];

Thanks very much for all of your help.


:

The AN is just a place holder for the last field in your table since I didn't
know what your field names were.

You should be able to link the union query to the table with the county in
it. This might require adding another field to each section of the union
query.

 

[jmoore]

Duane,

I can't thank you enough for you assistance. I am trying to teach myself
SQL and do not know how to create the static column.

This is my SQL for the SELECT DISTINCT query:
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

This is my attempt at the crosstab query based on it. Of course I get an
error for the Group By clause with Rowhead. Can you please explain what I
need to change.

TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT [qReview Sample for Annual Report Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY [qReview Sample for Annual Report Count] AS Rowhead
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

Thank you.

 

[Duane Hookom]

Got a message "Service unavailable" so this might be a duplicate
Try:
TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT "RowHead" as RowHead, [qReview Sample for Annual Report
Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY "RowHead"
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

--
Duane Hookom
Microsoft Access MVP

Duane,

I can't thank you enough for you assistance. I am trying to teach myself
SQL and do not know how to create the static column.

This is my SQL for the SELECT DISTINCT query:
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

This is my attempt at the crosstab query based on it. Of course I get an
error for the Group By clause with Rowhead. Can you please explain what I
need to change.

TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT [qReview Sample for Annual Report Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY [qReview Sample for Annual Report Count] AS Rowhead
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

Thank you.

You should be able to create another crosstab of your distinct query that has
a static derived column of "RowHead" as the Row Heading, First Of
CountOfReviewKey as the Value, and [CNTYName] & "Cnt" as the Column Heading.
This will create a single row query that can be added to a new query with
your existing crosstab so you have all of the values available in your report.

 

[jmoore]

I get an error message - "The Microsoft Jet database engine does not
recognize '[qReview Sample for Annual Report Count].CountOfReviewKey' as a
valid field name or expression." Any idea why this would occur?

Many thanks again.

Got a message "Service unavailable" so this might be a duplicate
Try:
TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT "RowHead" as RowHead, [qReview Sample for Annual Report
Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY "RowHead"
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

--
Duane Hookom
Microsoft Access MVP

Duane,

I can't thank you enough for you assistance. I am trying to teach myself
SQL and do not know how to create the static column.

This is my SQL for the SELECT DISTINCT query:
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

This is my attempt at the crosstab query based on it. Of course I get an
error for the Group By clause with Rowhead. Can you please explain what I
need to change.

TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT [qReview Sample for Annual Report Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY [qReview Sample for Annual Report Count] AS Rowhead
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

Thank you.

You should be able to create another crosstab of your distinct query that has
a static derived column of "RowHead" as the Row Heading, First Of
CountOfReviewKey as the Value, and [CNTYName] & "Cnt" as the Column Heading.
This will create a single row query that can be added to a new query with
your existing crosstab so you have all of the values available in your report.

 

[Duane Hookom]

Can we assume this SQL is a saved query?
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

If so, please provide the name. This saved query will be the source of your
next crosstab query.

--
Duane Hookom
Microsoft Access MVP

I get an error message - "The Microsoft Jet database engine does not
recognize '[qReview Sample for Annual Report Count].CountOfReviewKey' as a
valid field name or expression." Any idea why this would occur?

Many thanks again.

Got a message "Service unavailable" so this might be a duplicate
Try:
TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT "RowHead" as RowHead, [qReview Sample for Annual Report
Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY "RowHead"
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

--
Duane Hookom
Microsoft Access MVP

Duane,

I can't thank you enough for you assistance. I am trying to teach myself
SQL and do not know how to create the static column.

This is my SQL for the SELECT DISTINCT query:
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

This is my attempt at the crosstab query based on it. Of course I get an
error for the Group By clause with Rowhead. Can you please explain what I
need to change.

TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT [qReview Sample for Annual Report Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY [qReview Sample for Annual Report Count] AS Rowhead
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

Thank you.


:

You should be able to create another crosstab of your distinct query that has
a static derived column of "RowHead" as the Row Heading, First Of
CountOfReviewKey as the Value, and [CNTYName] & "Cnt" as the Column Heading.
This will create a single row query that can be added to a new query with
your existing crosstab so you have all of the values available in your report.

 

[jmoore]

Yes, it is saved as qReview Sample for Annual Report Count which you have in
your Transform query.

Before I received your reply, I decided to revise some of the query names to
shorten them. That seemed to resolve the issue of the error message about
the jet engine. I then received another error that specified one of the
fields was not included in an aggregate function. Switching to query design
view I found that field was inserted twice. After some trial and error, this
worked. I don’t understand why this does not require the brackets around the
query names, but I guess I’m happy that it works.

TRANSFORM Sum(qReviewSampleCount.TotalReviews) AS SumOfTotalReviews
SELECT "RowHead" AS RowHead
FROM qReviewSampleCount
GROUP BY "RowHead"
PIVOT qReviewSampleCount.CNTYNAME;

My next dilemma is a query based on a query that has a calculated field to
display the results in days between two date fields. In 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. Any advice?

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

Many, many thanks.

Can we assume this SQL is a saved query?
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

If so, please provide the name. This saved query will be the source of your
next crosstab query.

--
Duane Hookom
Microsoft Access MVP

I get an error message - "The Microsoft Jet database engine does not
recognize '[qReview Sample for Annual Report Count].CountOfReviewKey' as a
valid field name or expression." Any idea why this would occur?

Many thanks again.

Got a message "Service unavailable" so this might be a duplicate
Try:
TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT "RowHead" as RowHead, [qReview Sample for Annual Report
Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY "RowHead"
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

--
Duane Hookom
Microsoft Access MVP


:

Duane,

I can't thank you enough for you assistance. I am trying to teach myself
SQL and do not know how to create the static column.

This is my SQL for the SELECT DISTINCT query:
SELECT DISTINCT [qReview Sample for Annual Report].CNTYNAME, Count([qReview
Sample for Annual Report].ReviewKey) AS CountOfReviewKey
FROM [qReview Sample for Annual Report]
GROUP BY [qReview Sample for Annual Report].CNTYNAME;

This is my attempt at the crosstab query based on it. Of course I get an
error for the Group By clause with Rowhead. Can you please explain what I
need to change.

TRANSFORM Count([qReview Sample for Annual Report Count].CountOfReviewKey)
AS FirstOfCountOfReviewKey
SELECT [qReview Sample for Annual Report Count].CountOfReviewKey
FROM [qReview Sample for Annual Report Count]
GROUP BY [qReview Sample for Annual Report Count] AS Rowhead
PIVOT [qReview Sample for Annual Report Count].CNTYNAME;

Thank you.


:

You should be able to create another crosstab of your distinct query that has
a static derived column of "RowHead" as the Row Heading, First Of
CountOfReviewKey as the Value, and [CNTYName] & "Cnt" as the Column Heading.
This will create a single row query that can be added to a new query with
your existing crosstab so you have all of the values available in your report.