Broken query/relationship

[Hannah]

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!

 

[Roger Carlson]

You might want to compare your design to Duane Hookom's "At Your Survey"
database, which you can find here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3. Perhaps it
will give you some leads.

 

[Jerry Whittle]

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.

 

[Hannah]

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!

 

[Hannah]

Hi Roger,
Thanks for suggesting that - I've already been working with "At Your Survey"
as a reference point. As far as I can see, I have mine basically set up the
same as Duane's. The only difference I can think of is the set up of the
forms - could this be the issue? I don't have a form set up to record QID and
was just going to use defaults (as for each questionnaire table, the QID
would be the same in every case). The form I do have at the moment uses a
combo box and subform to allow the user to select which questionnaire (and
set of tables) they are going to fill in.
Thanks very much again for taking the time to help, I really appreciate it
and have learnt so much from these groups.

 

[Hannah]

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