Linking tables?

[BobG]

I have two tables that are imported from a club membership service.
One is members (contact information) with one record per member and several
fields of information for each record.
The other is questions with 9 separate records per each member. This one
has 9 questions, their names and answers. Both tables have membership no,
and first and last names of the members. I want to create a query that will
make all the answers to the 9 questions available tied to each related member
for use in forms and reports. I think I used to know how to do this by
creating a relationship (link) between tables but I can’t seem to make it
work so that each member has the answers to his 9 questions appear with his
record in the main members file. Should I look at some how combining the two
tables into one with all the information, this is how I had it designed
before we started using the membership service. I want to get back to where
I can use the existing forms, reports and sorts/queries by just changing the
field names. Thanks in advance.

 

[Frank J. Kobes]

You need a question table whose index is the question number. At least one
other field would be the question. So now you have all the questions in just
one place. The index for the member answers table would be the member number
and the question number. Another field would be the member's answer. You
could have a main form or report whose datasource is the member table. A sub
form or report would be linked to the main by member number. The data source
for the sub form/report would be a query using the Member Answer table and
the questions table linked on question number. This assumes all the question
are the same, if not just put the questions and answers in the same table
(the AnswerQuestion table).

 

[BobG]

Frank, thanks for the prompt response
I probably wasn't clear. The second file "Questions" looks like this
member_number question_name answer first_name last_name
026511 How Found Cynthia Pyron
026511 Crew Cynthia Pyron
026511 Cabins/Heads 1/1 Cynthia Pyron
026511 Hull No BEY03511F686 Cynthia Pyron
026511 Model First 26 Cynthia Pyron
026511 Slip Cynthia Pyron
026511 Year 1986 Cynthia Pyron
026511 Marina Piney Narrows Yacht Haven, Kent Island, MD Cynthia Pyron
026511 Boat Name Wind-Flirt 3 Cynthia Pyron
026511 Home Port Washington, DC Cynthia Pyron
029750 How Found Richard Ordeman
029750 Crew Richard Ordeman
029750 Cabins/Heads Richard Ordeman
029750 Hull No BEY03750D585 Richard Ordeman
029750 Model First 29 Richard Ordeman
029750 Slip Richard Ordeman
029750 Year 1985 Richard Ordeman
029750 Marina Herrington Harbour South, Friendship, MD Richard Ordeman
029750 Boat Name Spook Richard Ordeman
029750 Home Port Richard Ordeman
With 9 records for each member. There are no "question numbers" just
"member_number". I want to be able to do this with the least manupliation as
I'll be importing these files everytime the membership service updates.

 

[ACG]

Have you tried a CrossTab query? this is the SQL using
just the question table you showed (Scrap2 is the name I
gave to your question table) -

TRANSFORM First(Scrap2.answer) AS [The Value]
SELECT Scrap2.first_name, Scrap2.last_name,
Scrap2.member_number
FROM Scrap2
GROUP BY Scrap2.first_name, Scrap2.last_name,
Scrap2.member_number
PIVOT Scrap2.question_name;

 

[BobG]

Thanks Frank and ACG. I'm not entierly sure how, but the crosstab query did
what I needed. I'll study it later. Thanks again

Have you tried a CrossTab query? this is the SQL using
just the question table you showed (Scrap2 is the name I
gave to your question table) -

TRANSFORM First(Scrap2.answer) AS [The Value]
SELECT Scrap2.first_name, Scrap2.last_name,
Scrap2.member_number
FROM Scrap2
GROUP BY Scrap2.first_name, Scrap2.last_name,
Scrap2.member_number
PIVOT Scrap2.question_name;

-----Original Message-----

I have two tables that are imported from a club membership service.
One is members (contact information) with one record per member and several
fields of information for each record.
The other is questions with 9 separate records per each member. This one
has 9 questions, their names and answers. Both tables have membership no,
and first and last names of the members. I want to create a query that will
make all the answers to the 9 questions available tied to each related member
for use in forms and reports. I think I used to know how to do this by
creating a relationship (link) between tables but I canâ? Tt seem to make it
work so that each member has the answers to his 9 questions appear with his
record in the main members file. Should I look at some how combining the two
tables into one with all the information, this is how I had it designed
before we started using the membership service. I want to get back to where
I can use the existing forms, reports and sorts/queries by just changing the
field names. Thanks in advance.

.