Query: 3 one-to-many relationships

[sear100]

Dear All,

This is a query about Joins.

Linking 3 tables one-to-many relationship query

Can't get my head around this problem....It's pretty fundamental to the functioning of my database so I'd really appreciate some help. I'm trying to build a query that has a main table with data drawn from 3 related tables. The purpose is to allow independed expansion of the tables (adding of Roles, Departments and related Data about Members in 3 tables tblRole, tblDepartments and tblMemberStatus respectively). I imagine the answer is really simple....

The end of the SQL looks like this:

[FONT=&quot]FROM ((tblMainTable INNER JOIN tblDepartment ON tblMainTable.intIDDepartment = tblDepartment.intIDDepartment) INNER JOIN tblMemberStatus ON tblMainTable.intMemberStatusID = tblMemberStatus.intMemberStatusID) INNER JOIN tblRole ON tblMainTable.intIDRole = tblRole.intIDRole;

I've attached a zipped version of the query that I'm trying to build. With one Join the query Runs. With 2 or More the query returns no results. I've tried many different methods but cannot get this to work. I've previously used "lookup" fields that query from other tables to draw data to the main table to get it to work but this doesn't seem like an ideal solution.

Can someone please walk me through this?

Thanks In advance for your Help!

Seb[/FONT]

 

[plog]

An INNER JOIN only returns recrods between the data sources where the linking field matches. If there are no matches, then no records will be returned. You have 3 tables linked to Main, so to make it through to the results a record in Main has to match a record in each of those 3 other tables. If it only matches in 2 other tables, that's no good, it won't show up because it can't make a match on that 3rd table.

That's what's happening here (actually its only 1 for 3). Your only record in Main has intMemberStatusID = 5 and there is no matching intMemberStatusID value in tblMemberStatus so the whole record gets kicked out. It has the same issue with its intIDRole value.

If that record should be returned despite there being no matching data, you should change your joing types from INNER JOIN to LEFT JOIN so that all records of Main get through no matter the other tables.

And just because I'm here: you need a new table for your Rotation fields in Main. When you numerate field names (i.e. txt1stRotation, txt2ndRotation), that's a sign you need a new table with a 1 to many relationship. The rotation data should be a new table.

 

[sear100]

Thanks for your quick reply! I can't believe I missed this! Assiging the proper values pulls the data as you've rightly pointed out. I thought that I'd checked that...

However.... I now have a new issue that seems to have arisen. If I leave the Joins as Inner Joins I am unable to add any new Records. The attached form that would I would be using to front load the data shows no record when I link the "record source" property is set to this query.

Any ideas about this?

Please see the ammended database.


Re LEFT JOIN I have set the properties now that the Value list of the related data is a required field.
Thanks for your suggestion about the Rotations being a seperate table.

 

[plog]

Use tblMainTable as the forms source.

 

[sear100]

Hi Plog,

Thanks again.
Switching the source to the tblMainTable allows data entry but on reloading the form shows no records. This data is assigned to the tblMainTable however....

Your solution avoids using the query above.... presumably I can use the Query to drive reports later? Is there not a way to make the form based on the query?

 

[plog]

Yes, you can use the query to drive reports later on. You already had the form's control source set to the query, so you know how to do that. The thing is, setting it to the query does nothing for you: all the inputs on the form go back to tblMain.

As for your form not showing the existing records--I don't know what you need to do--forms aren't my strong suit. Post in that section of this forum for an answer. I opened your database, created a new form and set the control source to tblMain and was able to see all the existing records. I also set the control source of the existing form to your query and it didn't show existing records, so the issue with your form isn't tblMain, its a property of the table.

 

[sear100]

Many thanks plog

Never found the answer to the original form issue....but creating a new form solved the problem....

Case closed on this one.

 

[ricoriv]

I had a similar problem and found a work around. refer to my post to see it. I would like some details for how you solved it. Thank you,