crosstab or looping statement help

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.

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


"jmoore" wrote:

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.

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

"Duane Hookom" wrote:

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


"jmoore" wrote:

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.

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


"jmoore" wrote:

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

"Duane Hookom" wrote:

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


"jmoore" wrote:

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.

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 a

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.

"Duane Hookom" wrote:

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


"jmoore" wrote:

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

"Duane Hookom" wrote:

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


"jmoore" wrote:

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.

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;

"jmoore" wrote:

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 a

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.

"Duane Hookom" wrote:

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


"jmoore" wrote:

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

"Duane Hookom" wrote:

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


"jmoore" wrote:

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.

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;

"jmoore" wrote:

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;

"jmoore" wrote:

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 a

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.

"Duane Hookom" wrote:

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


"jmoore" wrote:

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

"Duane Hookom" wrote:

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


"jmoore" wrote:

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.

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


"jmoore" wrote:

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;

"jmoore" wrote:

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;

"jmoore" wrote:

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 a

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.

"Duane Hookom" wrote:

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


"jmoore" wrote:

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

"Duane Hookom" wrote:

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


"jmoore" wrote:

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.

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" wrote:

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

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


"jmoore" wrote:

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" wrote:

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