If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking tables?
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. |
#2
|
|||
|
|||
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" wrote: 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. |
#3
|
|||
|
|||
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. "Frank J. Kobes" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
Have you tried a CrossTab query? this is the SQL using=20
just the question table you showed (Scrap2 is the name I=20 gave to your question table) - TRANSFORM First(Scrap2.answer) AS [The Value] SELECT Scrap2.first_name, Scrap2.last_name,=20 Scrap2.member_number FROM Scrap2 GROUP BY Scrap2.first_name, Scrap2.last_name,=20 Scrap2.member_number PIVOT Scrap2.question_name; -----Original Message----- I have two tables that are imported from a club=20 membership service. One is members (contact information) with one record per=20 member and several=20 fields of information for each record. The other is questions with 9 separate records per each=20 member. This one=20 has 9 questions, their names and answers. Both tables=20 have membership no,=20 and first and last names of the members. I want to=20 create a query that will=20 make all the answers to the 9 questions available tied=20 to each related member=20 for use in forms and reports. I think I used to know=20 how to do this by=20 creating a relationship (link) between tables but I can=E2? Tt seem to make it=20 work so that each member has the answers to his 9=20 questions appear with his=20 record in the main members file. Should I look at some=20 how combining the two=20 tables into one with all the information, this is how I=20 had it designed=20 before we started using the membership service. I want=20 to get back to where=20 I can use the existing forms, reports and sorts/queries=20 by just changing the=20 field names. Thanks in advance. =20 . |
#5
|
|||
|
|||
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 "ACG" wrote: 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. . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking tables to spreadsheets | W M | General Discussion | 2 | February 8th, 2005 03:31 PM |
Linking Split Tables | Chris | General Discussion | 2 | February 7th, 2005 05:54 PM |
Access 2002 Linking Oracle Tables | Donna Ferreira | General Discussion | 1 | January 7th, 2005 02:08 AM |
Question about linking tables | Heather | Database Design | 2 | August 10th, 2004 07:21 PM |
Linking Tables in External Database - Programatically | Karen B | Database Design | 1 | June 9th, 2004 12:41 AM |