Joining More Than Three Tables (1 Viewer)

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
A strataplan is a building
I have 4 tables
1.
Infosheet_T
(Infosheet_T is a record of services provided for the strataplan by contractors)

Field: kf_StrataPlan_ID, one to many relationship with kp_StrataPlan_ID : allows me to relate the infosheet with the strataplan record (this allows me to bring in fields from the StrataPlan_T, like the building name, or the address.
Join: 2: All of strataplan table records, only equal Infosheet (what is the equal field? - kp and kf keys?)
This is a combobox on the Infosheet record. A list of 14 building codes.

Field: kf_Services_ID, one to many relationship with kp_Services_ID : allows me to select the services the building needs, like window washing or garbage collection.
This is a combobox on the infosheet record. A list of 52 services.

Field: kf_Contractor_ID, one to many relationship with kp_Contractor_ID : allow me to selector a contractor and any related field in the contractor table, like an email, or telephone number.

So this requires joining 4 tables.
In the end I have a record that shows date but nothing is selectable. My comboboxes all show the correct list but none are selectable.

What is wrong.

Thinking that I might have 'ambiguous joins' I created joins and then used the to create joins. (the outer and inner concept makes sense but I am not sure when to choose which)

Anyhow I am seriously confused and nowhere on the internet can I find an example of joining 4 tables.

Please help.

PS: I am working on 04_Infosheet in the attached db
 

Attachments

  • MyKDatabaseHero (June 30Rebuild-Forum).accdb
    3.5 MB · Views: 145

June7

AWF VIP
Local time
Today, 08:29
Joined
Mar 9, 2014
Messages
5,423
Really should not need to bind form to a query for data entry. Form is perfectly editable bound to 04_Infosheet_T. Including 1 or more lookup table in form RecordSource should be possible, however, I cannot understand your table relationships and as you discovered, joining them fails.

Address can be included in combobox RowSource then reference appropriate combobox column by its index if you want to use address or display in a textbox. I don't see a 'building name' field.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:29
Joined
Feb 28, 2001
Messages
26,999
The question is whether your four joins are really a great-grandparent to grandparent to parent to child relationship (4-level hierarchy), or whether they might be two or three (2-level with 3 parallel) child or lookup or translation tables. I THINK you probably have the latter, which is far easier to manage. Looking at your relationships, I note you have a really bad issue.

Your 04_Infrasheet_T table links to 30_Contractors_T which links to 82_Services_T. But there is a second link directly from 04_Infrasheet_T to 82_Services_T. This amounts to an ambiguous pathway (with and without 30_Contractors_T in the linkage) and Access CANNOT allow an update of any query that contains the beginning and end tables of this linkage. It doesn't know how to form the relationship for SQL purposes because, in essence, the wizard doesn't know which of two table/field combinations for which to build an ON clause. So it can't build the JOIN.

I don't offhand see the real thrust of the DB. Can you tell us in words other than table names and field names what you are trying to do? I.e. describe the business process you are trying to support.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
Really should not need to bind form to a query for data entry. Form is perfectly editable bound to 04_Infosheet_T. Including 1 or more lookup table in form RecordSource should be possible, however, I cannot understand your table relationships and as you discovered, joining them fails.

Address can be included in combobox RowSource then reference appropriate combobox column by its index if you want to use address or display in a textbox. I don't see a 'building name' field.
Thanks June 7. I understand LookUps are to be avoided. The issue is not the ediability of the data but producing a form that marries 4 tables. A strata is a building
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
The question is whether your four joins are really a great-grandparent to grandparent to parent to child relationship (4-level hierarchy), or whether they might be two or three (2-level with 3 parallel) child or lookup or translation tables. I THINK you probably have the latter, which is far easier to manage. Looking at your relationships, I note you have a really bad issue.

Your 04_Infrasheet_T table links to 30_Contractors_T which links to 82_Services_T. But there is a second link directly from 04_Infrasheet_T to 82_Services_T. This amounts to an ambiguous pathway (with and without 30_Contractors_T in the linkage) and Access CANNOT allow an update of any query that contains the beginning and end tables of this linkage. It doesn't know how to form the relationship for SQL purposes because, in essence, the wizard doesn't know which of two table/field combinations for which to build an ON clause. So it can't build the JOIN.

I don't offhand see the real thrust of the DB. Can you tell us in words other than table names and field names what you are trying to do? I.e. describe the business process you are trying to support.
I have been struggling to produce the form 'Infosheet'. The business process: All stratas have a list of contractors that provide certain services. I have an Infosheet that conceptually should draw data from 01_StrataPlan_T (the StrataPlanNr), 30_Contractors_T (CompanyName), and 82_Services_T (Service). Ideally this would be an editable form, which could generate a report. More practically the Infosheet form helps me contact the right contractor given certain events.

First, yes I noticed the ambiguous relationship with 82_Service_T. One thought had been to provide access to the [82_Service_T].Service through [30_Contractors_T]. My query produced data up to the point where I created a relationship between the [82_Services_T].kp_82_Services_ID = [04_Infosheet_T].kf_82_Services_ID . I have deleted this relationship. But whichever way the Query did not produce a result. Moreover the Query did not produce selectable comboboxes. My comboboxes provide the StrataPlanNr, CompanyName and Service.

I hope my syntax is correct.

Thanks

PS: When you write the thrust of the DB, if you mean why I have a DB, it is to help me manage these buildings. I have a lot of different projects, about 1000 contractors, about 700 owners and other details I am constantly referencing. The DB is fast and rich.,
 
Last edited:

June7

AWF VIP
Local time
Today, 08:29
Joined
Mar 9, 2014
Messages
5,423
Editability does seem to be the issue as you want comboboxes to be changeable. Why do you want to include other tables in RecordSource?

I did not say Lookups are to be avoided, just including those tables in RecordSource is usually not necessary. What I do recommend is to not build Lookup fields in tables.

Trying to join 01_StrataPlan_T (as the lookup table, RIGHT or INNER join) to 04_Infosheet_T so that 04_Infosheet_T data is editable, fails with ambiguous outer join error. LEFT JOIN is accepted but dataset is not editable. So I think something is seriously wrong with that spiderweb of relationships. However, when I removed all Relationships, query still wants to do LEFT JOIN. I expect it is because of the Lookups built in table. Did you use the Lookup field wizard to build?

I changed all the lookup fields to just textboxes and made a copy of 04_Infosheet_T and built query with 01_StrataPlan_T. No problem with RIGHT JOIN and dataset is editable. That is just about the only way to remove relationships established by Lookup field wizard - delete the field and/or build new table.
 
Last edited:

mike60smart

Registered User.
Local time
Today, 16:29
Joined
Aug 6, 2017
Messages
1,899
I have been struggling to produce the form 'Infosheet'. The business process: All stratas have a list of contractors that provide certain services. I have an Infosheet that conceptually should draw data from 01_StrataPlan_T (the StrataPlanNr), 30_Contractors_T (CompanyName), and 82_Services_T (Service). Ideally this would be an editable form, which could generate a report. More practically the Infosheet form helps me contact the right contractor given certain events.

First, yes I noticed the ambiguous relationship with 82_Service_T. One thought had been to provide access to the [82_Service_T].Service through [30_Contractors_T]. My query produced data up to the point where I created a relationship between the [82_Services_T].kp_82_Services_ID = [04_Infosheet_T].kf_82_Services_ID . I have deleted this relationship. But whichever way the Query did not produce a result. Moreover the Query did not produce selectable comboboxes. My comboboxes provide the StrataPlanNr, CompanyName and Service.

I hope my syntax is correct.

Thanks

PS: When you write the thrust of the DB, if you mean why I have a DB, it is to help me manage these buildings. I have a lot of different projects, about 1000 contractors, about 700 owners and other details I am constantly referencing. The DB is fast and rich.,
Hi Mathew

The standard method of managing the process of Contractors and Services is via a Main Form / Subform layout as shown in
the attached screenshot.

I have left all of your Lookup Fields in Tables, but they should all be removed soonest.

The Main Form is based on your table "01_StrataPlan_T" and the Subform based on your table "04_Infosheet_T"
 

Attachments

  • MainSub.JPG
    MainSub.JPG
    193.7 KB · Views: 110

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
I bind ALL my forms to queries. Otherwise, I wouldn't be able to control the records that are returned.

Many of the queries use joins to other tables. There is nothing inherently wrong with this, nor will it automatically make your query not-updateable. You do however, need to be very careful when you do this because you don't want to allow the user to accidentally update any record in the join EXCEPT for the primary record. Typically, when we include ancillary tables, they are "lookups" so we can show additional data on a form, NOT so we can update it. For example, on the order header, we might want to join to the customer table so we can also show the contact information or sales rep.

I don't understand why these queries are nested nor do I understand why you would use all four of these tables/queries in a single query that you want to bind to a form, but I think I found the issue. the query below has a duplicate join. You can't see it in QBE view but you can see it in SQL view. Once I removed it, the query was updateable.

zzBadQiery.JPG


Now we get to the other stuff.

Numbering the tables is a seriously bad idea, especially when you are not leaving gaps. You have backed yourself into a corner. The numbers add nothing.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
The numbers allow me to organize the tables as I want. Otherwise the tables are organized alphabetically. My numbering has a logic. The strataplan is the root document. The other tables are levels down from the root. 02 through 08 is the next level - the documents these tables produce are all directly related to a strata. 30_Contractor is removed from the strataplan, table 80 to 90 are lists. Why are the numbers a seriously bad idea?

In as much as I understand a query, it is necessary to produce documents (forms) that hold fields from related tables? When I used Filemaker this was not necessary. You just related fields and this provided access.
I bind ALL my forms to queries. Otherwise, I wouldn't be able to control the records that are returned.

Many of the queries use joins to other tables. There is nothing inherently wrong with this, nor will it automatically make your query not-updateable. You do however, need to be very careful when you do this because you don't want to allow the user to accidentally update any record in the join EXCEPT for the primary record. Typically, when we include ancillary tables, they are "lookups" so we can show additional data on a form, NOT so we can update it. For example, on the order header, we might want to join to the customer table so we can also show the contact information or sales rep.

I don't understand why these queries are nested nor do I understand why you would use all four of these tables/queries in a single query that you want to bind to a form, but I think I found the issue. the query below has a duplicate join. You can't see it in QBE view but you can see it in SQL view. Once I removed it, the query was updateable.

View attachment 101654

Now we get to the other stuff.

Numbering the tables is a seriously bad idea, especially when you are not leaving gaps. You have backed yourself into a corner. The numbers add nothing.l
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
Did you read the part of my response that told you WHY your query was not updateable? Do you understand how to fix it? All it takes is removing the duplicate join.

Using complex queries that represent a hierarchy is almost never necessary. WHY? Because each level should be represented as a subform or subreport to facilitate break logic. For simple cases, you can use a single query but you are quite likely to run into problems if you need to count or summarize the data. The subforms/subreports provide a clean way to do this.

You would NEVER use this query as the RecordSource for a form where you intend to update data.

Numbering the tables as you have will lead you to breaking your scheme at some point. Once you have to deviate, the numbers become a liability rather than a help. Do what you want. I'm not going to try to convince you I am correct regarding your numbering scheme.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
Okay thanks. Yes I understood the Join issue you explained. I will try your proposal tonight. I have to say I do not quite understand yet the concepts like you explain. I thought Queries were how you joined tables in Access.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
If you simply open the query I pointed to in QBE view, it looks just fine - but it is NOT. Switch to SQL view and you will see the duplication. I have now idea how you managed to duplicate the join but somehow it happened. To fix the problem, get rid of the duplication by deleting the part in the purple brackets.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
Hi Mathew

The standard method of managing the process of Contractors and Services is via a Main Form / Subform layout as shown in
the attached screenshot.

I have left all of your Lookup Fields in Tables, but they should all be removed soonest.

The Main Form is based on your table "01_StrataPlan_T" and the Subform based on your table "04_Infosheet_T"
Can you post the file so I can see what you did to the DB? Thanks
 

mike60smart

Registered User.
Local time
Today, 16:29
Joined
Aug 6, 2017
Messages
1,899
Can you post the file so I can see what you did to the DB? Thanks
Hi Mathew

As I said previously, all of the Lookup Fields in the tables need to be changed to normal Field Types.
 

Attachments

  • MyKDatabaseHero (June 30Rebuild-Forum).zip
    411.9 KB · Views: 111

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
In the end I have a record that shows date but nothing is selectable. My comboboxes all show the correct list but none are selectable.
I told you how to fix the problem. But you keep ignoring my directions.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
I told you how to fix the problem. But you keep ignoring my directions.
Not sure why you say that. I am trying to reorganize my database and at the same time understand your advice, and the advice of others. I actually did what you suggested and think I had success but that was on a backup of the db. Now I need to redo this on the database proper.

I go to work at 8 am and get home at 5:30. I have a 12 year old who I like to spend some time with and then like tonight, I turn on my computer at 10:30 pm and try work on my database and implement the advice I am provided. You don't mind that I spend time with my 12 year old do you?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
And I'm helping you for free, taking my time that I could be spending doing something for fun or for profit. I gave you the answer Monday and it is now Thursday and you never even acknowledged seeing it. Good luck.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
And I'm helping you for free, taking my time that I could be spending doing something for fun or for profit. I gave you the answer Monday and it is now Thursday and you never even acknowledged seeing it. Good luck.
My apologies for that indeed. Do not doubt my appreciation for your generosity answering my question. But regardless, you are right I should acknowledge the reply no less.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
No problem. We all get cranky. Did it work? Is the query now updateable which was your original question? You have other problems but we can only fix them one at a time.
 

MatthewB

Member
Local time
Today, 09:29
Joined
Mar 30, 2022
Messages
85
Yes, thanks it work. It has taken a while to implement as how did someone put it, "there is a spiderweb of relationships. I am cleaning this up as well.

30 minutes later.

Interestingly, if I sort (order alphabetically) the subform using the filter feature it uses this sort result as the default sort thereafter. Even after the form is closed and then reopened. I wonder if it is possible to see the code access has applied to the form.
 
Last edited:

Users who are viewing this thread

Top Bottom