Getting information from several surveys in Access

[forest8]

Hi there

I have created three surveys which contains information from students. The
students complete the surveys at intervals of 3 months so within 1 year a
student can have, at most, completed 4 surveys. The pre-survey which is
conducted at the start of the year and a survey at the end of the first term.
The post survey that is conducted at the end of the second and third terms
has elements of the pre-survey and the survey that is conducted at the end of
the 1st term.

For instance, on questions on how they rate their teachers on a range of
topics such as knowledge, ease of explaining new terms, addressing students
needs, has teaching improved since the previous term, etc.

I have 3 tables because there are three different surveys but there are
similar questions in all the surveys but they're not all the same. The
pre-survey is the shortest. The post survey the longest.


What I want to do is see all the results for the same questions on a single
report. For instance, for each student, I want to see if their ratings have
changed over time (at the beginning to the end).

Thank you in advance.

 

[KARL DEWEY]

You talked a lot about your methods but nothing of the table and field names
or datatype.
There is two ways to view data from multiple tables - vertical or horizontal.

For vertical use a union query.

For horizontal use a common field and join the tables. You may need to first
use a union query to create a complete list of data that is your common field
and then left join that to the other tables.

 

[Clifford Bass via AccessMonster.com]

Hi,

You can do this with a union all query. The SQL would look something
like this:

select 1 as SurveyNumber, StudentID, q1, q5, q10
from survey1
union all
select 2, StudentID, q3, q8, q14
from survey2
union all
select 3, StudentID, q7, q8, q12
from survey3
union all
select 4, StudentID, q2, q19, q24
from survey4;

Each select must have the same number of columns. In this example you
will note that I include a column that returns the number of the survey so
that you can display the results in survey order. Column q1 in survey1 is
the same question as q3 in survey2 which is the same question as q7 in
survey3 which is the same question as q2 in survey4. Union all tells the
query engine to return all rows from the four selects. If it used just union,
it would attempt to eliminate duplicate rows. Now, in this case there should
not be any duplicate rows due to the nature of the statement so a plain union
should give the same results.

If you are not sure about constructing the query from scratch you can
construct each query separately in the query designer. Then create a fifth
query. Cancel the add table dialog and it will go into SQL view. While
there type something like:

select * from surveryquery1
union all
select * from surveryquery2
union all
select * from surveryquery3
union all
select * from surveryquery4;

where surveyqueryn is the name of the query.

Hope that helps,

Clifford Bass