SQL Question and Form Field Displays

  • Thread starter Thread starter Bob Quintal
  • Start date Start date
B

Bob Quintal

Can some one please give me a bit of advice on how to accomplish
the following:

I have two tables (called Questions and Answers). The Questions
table has five records, each with a unique question_Number field
(from 1 - 5). I have created a query that pulls in all five
Question records based on their primary key field and I am able to
sequentially display all five records in my form.

The Answers table has a single record that is associated with the
records on the Question table by referencing the primary key
field.

The Problem:
1. I want the single Answer record to appear with each individual
Question record. I can display all five Questions without a
problem, but how do I make the single Answer record appear five
times. The Answer records should all be the same, but should
display the question_Number depending on the Question that it is
displayed with. Sounds like I need a loop for the Answer table
record? Or, can I use some kind of join to link the two tables to
create a single query that I can use to display in the form?

This is my first time working with VB and forms. If anyone could
show me the SQL I would be very appreciative. Thanks.
Click to expand...
You'll have to split the Answers table into five rows. You wouold
use a UNION query that calls the answers table with one answer
column eaxch time

SELECT [PrimaryKey],
1 as AnswerNumber,
answer1 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
2 as AnswerNumber,
answer2 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
3 as AnswerNumber,
answer3 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
4 as AnswerNumber,
answer4 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
5 as AnswerNumber,
answer5 as Answer FROM Answers

Either use the query instead of the table in the query that joins
Questions And Answers or actually create the modified table and
delete the existing Answers table.
 
Can some one please give me a bit of advice on how to accomplish the
following:

I have two tables (called Questions and Answers). The Questions table has
five records, each with a unique question_Number field (from 1 - 5). I have
created a query that pulls in all five Question records based on their
primary key field and I am able to sequentially display all five records in
my form.

The Answers table has a single record that is associated with the records on
the Question table by referencing the primary key field.

The Problem:
1. I want the single Answer record to appear with each individual Question
record. I can display all five Questions without a problem, but how do I
make the single Answer record appear five times. The Answer records should
all be the same, but should display the question_Number depending on the
Question that it is displayed with. Sounds like I need a loop for the Answer
table record? Or, can I use some kind of join to link the two tables to
create a single query that I can use to display in the form?

This is my first time working with VB and forms. If anyone could show me the
SQL I would be very appreciative. Thanks.
 
Thanks Bob,

I'm beginning to understand what I have to do. I have another question. Is
there any way to perform a loop using VBA so that I could simply have four
blank fields display on the form five times? Sort of like this:

cnt = 0

if cnt < 4 do loop
display field1, field2, field3, field4
cnt++

end loop

The display fields will appear in a row on the form.
Can I do this with Access?

Bob said:
Can some one please give me a bit of advice on how to accomplish
the following:
Click to expand...
[quoted text clipped - 21 lines]
This is my first time working with VB and forms. If anyone could
show me the SQL I would be very appreciative. Thanks.
Click to expand...

You'll have to split the Answers table into five rows. You wouold
use a UNION query that calls the answers table with one answer
column eaxch time

SELECT [PrimaryKey],
1 as AnswerNumber,
answer1 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
2 as AnswerNumber,
answer2 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
3 as AnswerNumber,
answer3 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
4 as AnswerNumber,
answer4 as Answer FROM Answers
UNION
SELECT [PrimaryKey],
5 as AnswerNumber,
answer5 as Answer FROM Answers

Either use the query instead of the table in the query that joins
Questions And Answers or actually create the modified table and
delete the existing Answers table.
Click to expand...
 
Back
Top