A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Broken query/relationship



 
 
Thread Tools Display Modes
  #1  
Old November 11th, 2009, 03:41 PM posted to microsoft.public.access
hannah
external usenet poster
 
Posts: 66
Default 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  
Old November 11th, 2009, 04:57 PM posted to microsoft.public.access
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old November 11th, 2009, 05:15 PM posted to microsoft.public.access
hannah
external usenet poster
 
Posts: 66
Default 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  
Old November 12th, 2009, 11:11 AM posted to microsoft.public.access
hannah
external usenet poster
 
Posts: 66
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.