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
|
|||
|
|||
Broken query/relationship
I've had a hunt around and couldn't find an answer to this particular query
but I'm really sorry if it's on here somewhere already! I'm setting up a database to store and analyse questionnaire data. I've got 11 different questionnaires so I want to have a table (call it QuestTable) which lists each questionnaire type and gives it an ID number. Each questionnaire will then be linked to this table using that ID number. So far, so good...except when I go to query tables linked to QuestTable, no records are returned for any query. I wondered if this was to do with the relationships but these seem okay as far as I can tell. Any advice hugely appreciated! |
#2
|
|||
|
|||
Broken query/relationship
It's the join. With an inner join, you need a matching record in both tables
or a record won't be returned even if there is a record in one or the tables. In query design view, double click on the line between the two tables. Select Option 2 which is a Left Join. See if that works. If not, try Option 3 which is a Right Join. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Hannah" wrote: I've had a hunt around and couldn't find an answer to this particular query but I'm really sorry if it's on here somewhere already! I'm setting up a database to store and analyse questionnaire data. I've got 11 different questionnaires so I want to have a table (call it QuestTable) which lists each questionnaire type and gives it an ID number. Each questionnaire will then be linked to this table using that ID number. So far, so good...except when I go to query tables linked to QuestTable, no records are returned for any query. I wondered if this was to do with the relationships but these seem okay as far as I can tell. Any advice hugely appreciated! |
#3
|
|||
|
|||
Broken query/relationship
Thanks for such a quick reply! I had a go at what you suggested and it does
mean that records show up now in the query but not really in the way I want. Option 1 is really what I need so I'll try to explain a bit better but I'm a bit of an access novice so bear with me! The tables involved are tblQuest QID QuestName tblLocal OrgID OrgName (includes OrgAddress, country, Population etc but don't think this is crucial) QID tblVessel VID VesselName (similarly home port, country, address, tel etc) QID tblLocal holds the contact/general information for the first questionnaire, tblVessel holds the contact/general info for the second questionnaire and so on. The link I've set up relies on QID and therefore all the records in tblLabel, for instance, have the same QID number (I'm wondering if this is where the problem is?) The relationship between tblQuest and each of the other two is a one-to-many relationship. When I query tblQuest and ONE of the other two tables, it works perfectly. When I try to query tblQuest and both the other tables, no records show up. What I really want to do with this query is find all the records relating to one country (e.g. Scotland) for all the questionnaires but at the moment I'm just concerned about trying to add more questionnaires if this link doesn't work! Any advice about where I'm going wrong with this would be very very much appreciated! "Jerry Whittle" wrote: It's the join. With an inner join, you need a matching record in both tables or a record won't be returned even if there is a record in one or the tables. In query design view, double click on the line between the two tables. Select Option 2 which is a Left Join. See if that works. If not, try Option 3 which is a Right Join. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Hannah" wrote: I've had a hunt around and couldn't find an answer to this particular query but I'm really sorry if it's on here somewhere already! I'm setting up a database to store and analyse questionnaire data. I've got 11 different questionnaires so I want to have a table (call it QuestTable) which lists each questionnaire type and gives it an ID number. Each questionnaire will then be linked to this table using that ID number. So far, so good...except when I go to query tables linked to QuestTable, no records are returned for any query. I wondered if this was to do with the relationships but these seem okay as far as I can tell. Any advice hugely appreciated! |
#4
|
|||
|
|||
Broken query/relationship
Hi Jerry,
You were right! Working with it a bit more, I understand that Option 2 is by best plan - thank you ever so much! The problem I now have is to make my query display records from each of my questionnaire tables based on a specific country (they both have country as a field). Any idea how I go about doing this? Thanks again for really helping me out. Hannah "Jerry Whittle" wrote: It's the join. With an inner join, you need a matching record in both tables or a record won't be returned even if there is a record in one or the tables. In query design view, double click on the line between the two tables. Select Option 2 which is a Left Join. See if that works. If not, try Option 3 which is a Right Join. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Hannah" wrote: I've had a hunt around and couldn't find an answer to this particular query but I'm really sorry if it's on here somewhere already! I'm setting up a database to store and analyse questionnaire data. I've got 11 different questionnaires so I want to have a table (call it QuestTable) which lists each questionnaire type and gives it an ID number. Each questionnaire will then be linked to this table using that ID number. So far, so good...except when I go to query tables linked to QuestTable, no records are returned for any query. I wondered if this was to do with the relationships but these seem okay as far as I can tell. Any advice hugely appreciated! |
Thread Tools | |
Display Modes | |
|
|