Query based on multiple tables

[Kyp]

I've created a query based on 3 related tables. The goal is to create a form based on the query to view and add records.
I know my query is not correct because when I run the query, I can see the record however, I cannot add a record.
Can someone explain the theory behind the query I am trying to create.
Below is a screen shot of the relationship and the SQL is below that.
Much appreciated.

SELECT tbl_Contacts.*, tbl_Locations.*, tbl_Vendors.*
FROM (tbl_Vendors INNER JOIN tbl_Contacts ON tbl_Vendors.VendorID = tbl_Contacts.VendorID) INNER JOIN tbl_Locations ON tbl_Vendors.VendorID = tbl_Locations.VendorID;

 

[Gasman]

See if any of these apply.
Normally a form is based on one table or query, with subforms for related data.

Edit: Oops, missing link :-(

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

 

[cheekybuddha]

See if any of these apply.

Missing link?

 

[Gasman]

Missing link?

Thank you David.

 

[Kyp]

See if any of these apply.
Normally a form is based on one table or query, with subforms for related data.

Edit: Oops, missing link :-(

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

Okay, I understand.
Would the proper method of accomplishing this be to add another table containing fields from the 3 tables, or a form (Vendors) with a subform datasheet (Vendor Contacts)?
Thinking out loud...adding another table would create duplicate data, so maybe form/subfrm would be the correct method??

Appreciate the help.

 

[Gasman]

TBH I am not that great on query joins.
I have to experiment to see what works.

 

[Kyp]

TBH I am not that great on query joins.
I have to experiment to see what works.

Gotcha, neither am I...
I'm looking for some kind of example on the web.

 

[cheekybuddha]

You ought to be able to create a main form based on table tbl_Vendors

Add two subforms based on tbl_Locations and tbl_Contacts.

Link each using LinkMasterFields and LinkChildFields on VendorID.

Add a new vendor.

Then, add a location and the VendorID field should be picked up from the main form.

Repeat for a new contact.

As you navigate through Vendors, the matching Locations and Contacts should appear in the subforms.

 

[Pat Hartman]

There is no logical problem with using a single form that updates a hierarchical set of tables although using subforms is ALWAYS the best option. But you do not have a hierarchical relationship. You have two parallel relationships and that is why your query is not updateable. Locations is related to Vendor. Contacts is related to Vendor but they are not related to each other. In fact they have nothing to do with each other. They just happen to both be related to the vendor table. Perhaps the relationship should be Vendor - Location - contacts. That implies that a contact is only associated with a single location. Your relationship allows a contact to be associated with multiple locations.

Think of a different table that is related to Vendor such as products. Would a query that joined vendor to products and vendor to contacts make sense? I don't think so. Essentially with these parallel relationships, you end up with a Cartesian Product where Location is multiplied by contact.

Vendor1, Location1, contact1
Vendor1, Location1, contact2
Vendor1, Location1, contact3
Vendor1, Location2, contact1
Vendor1, Location2, Contact2
Vendor1, Location2, Contact3
Vendor2, Location1, contact1
Vendor2, Location1, contact2
Vendor2, Location1, contact3

For every instance of location, you repeat all instances of contacts.

 

[cheekybuddha]

The model should follow the reality of the data.

If you have the hierarchical structure, then a contact would be tied to a single location - if it's possible that a contact can work out of more than one location at once then the model doesn't work.

Conversely, as Pat points out, with the parallel structure, you are not able to see at which location a contact is based.

Probably the best solution would then be to have a relationship from Company to Location, and then a m:m relationship from Location to Contact.

Data modelling is never easy!

 

[The_Doc_Man]

Pat gave you the detailed answer. I'll offer an overview. Your problem is ambiguity of destination.

A form that shows a single value wants to save that single value to a single place. Because of what Pat demonstrated, you have multiple possible places to write that single record from the form. Access won't do that because it needs to be certain about where to write the result - and without proper qualifiers (WHERE clauses) to constrain the potential Contacts and Locations, you have that ambiguity of destination. But the query you showed us had no WHERE clauses. Therefore, Access makes that query read-only.

 

[Kyp]

There is no logical problem with using a single form that updates a hierarchical set of tables although using subforms is ALWAYS the best option. But you do not have a hierarchical relationship. You have two parallel relationships and that is why your query is not updateable. Locations is related to Vendor. Contacts is related to Vendor but they are not related to each other. In fact they have nothing to do with each other. They just happen to both be related to the vendor table. Perhaps the relationship should be Vendor - Location - contacts. That implies that a contact is only associated with a single location. Your relationship allows a contact to be associated with multiple locations.

Think of a different table that is related to Vendor such as products. Would a query that joined vendor to products and vendor to contacts make sense? I don't think so. Essentially with these parallel relationships, you end up with a Cartesian Product where Location is multiplied by contact.

Vendor1, Location1, contact1
Vendor1, Location1, contact2
Vendor1, Location1, contact3
Vendor1, Location2, contact1
Vendor1, Location2, Contact2
Vendor1, Location2, Contact3
Vendor2, Location1, contact1
Vendor2, Location1, contact2
Vendor2, Location1, contact3

For every instance of location, you repeat all instances of contacts.

Good morning and Happy New Year!

I understand what you are saying however, I do not understand the structure of a "Hierarchical Relationship".
I have made a couple of attempts to create the hierarchical relationship but once created, I still could not update the tables using a form.

I am not asking to be spoon fed, some literature or an example if you happen to have a link. My search results were astronomical and I just confused myself. lol

The goal is to have the 3 tables that can be updated from a single form.

Below is the relationship I now have however, I do not know if it is correct (hierarchical)
Was not sure if I needed to add FK's from the upstream tables to tbl_Contacts.

(Edit 1: added question)
Should the relationship be created in a query rather than the "Relationship Design" tab?

(Edit 2: Relationship in query)

I added another table and created this relationship in a query.
It seems to work but is it correct? I know that just because it works, doesn't mean it is correct as it could cause problems later on...
(SQL below image)

SELECT tbl_Vendors.VendorID, tbl_Company.Company, tbl_Company.WebSite, tbl_Locations.Address, tbl_Locations.City, tbl_Locations.State, tbl_Locations.Country, tbl_Locations.ZipCode, tbl_Contacts.FirstName, tbl_Contacts.LastName, tbl_Contacts.Title, tbl_Contacts.Email, tbl_Contacts.Office, tbl_Contacts.Ext, tbl_Contacts.Mobile, tbl_Contacts.Fax, tbl_Contacts.Notes, tbl_Contacts.Attachments
FROM ((tbl_Vendors LEFT JOIN tbl_Company ON tbl_Vendors.CompanyID = tbl_Company.CompanyID) LEFT JOIN tbl_Contacts ON tbl_Vendors.ContactID = tbl_Contacts.ContactID) LEFT JOIN tbl_Locations ON tbl_Vendors.LocationID = tbl_Locations.LocationID;

 

[cheekybuddha]

It seems to me that you would want to have companies, then Contacts and Locations with a FK to CompanyID, since contacts and locations are elements of a company.

Then, a M:M table linking companies and their types (Vendor, Supplier, other ...) to a lookup table of company types.

Something like:

                           CompanyTypes
lkpCompanyType             ------------
--------------             ID                       Companies
ID             <-- 1:M --> TypeID                   -----------             Contacts
Type                       CompanyID    <-- M:1 --> ID          <-- \       ---------
                                                    CompanyName     |       ID
                                                                    |       FirstName
                                                                    |       LastName
                                                                    |       Title
                                                                   1:M ---> CompanyID
                                                                    |
                                                                    |
                                                                    |       Locations
                                                                    |       ---------
                                                                    |       ID
                                                                    |       Location
                                                                   1:M ---> CompanyID

If contacts are tied to specific company locations then the Contacts table would link to the Locations table instead of the Companies table.

 

[cheekybuddha]

I still would advocate using subforms for the data entry

 

[mike60smart]

Good morning and Happy New Year!

I understand what you are saying however, I do not understand the structure of a "Hierarchical Relationship".
I have made a couple of attempts to create the hierarchical relationship but once created, I still could not update the tables using a form.

I am not asking to be spoon fed, some literature or an example if you happen to have a link. My search results were astronomical and I just confused myself. lol

The goal is to have the 3 tables that can be updated from a single form.

Below is the relationship I now have however, I do not know if it is correct (hierarchical)
Was not sure if I needed to add FK's from the upstream tables to tbl_Contacts.

(Edit 1: added question)
Should the relationship be created in a query rather than the "Relationship Design" tab?


View attachment 111684

(Edit 2: Relationship in query)

I added another table and created this relationship in a query.
It seems to work but is it correct? I know that just because it works, doesn't mean it is correct as it could cause problems later on...
(SQL below image)

View attachment 111685

SELECT tbl_Vendors.VendorID, tbl_Company.Company, tbl_Company.WebSite, tbl_Locations.Address, tbl_Locations.City, tbl_Locations.State, tbl_Locations.Country, tbl_Locations.ZipCode, tbl_Contacts.FirstName, tbl_Contacts.LastName, tbl_Contacts.Title, tbl_Contacts.Email, tbl_Contacts.Office, tbl_Contacts.Ext, tbl_Contacts.Mobile, tbl_Contacts.Fax, tbl_Contacts.Notes, tbl_Contacts.Attachments
FROM ((tbl_Vendors LEFT JOIN tbl_Company ON tbl_Vendors.CompanyID = tbl_Company.CompanyID) LEFT JOIN tbl_Contacts ON tbl_Vendors.ContactID = tbl_Contacts.ContactID) LEFT JOIN tbl_Locations ON tbl_Vendors.LocationID = tbl_Locations.LocationID;

Are you able to upload the db with no confidential data?

 

[The_Doc_Man]

Regarding the diagrams in your post #12 of this thread, let's call them top and bottom diagrams:

The top diagram includes three tables in linear hierarchical relationship but the leftmost relationship still potentially leads to an issue of ambiguity. The left-side "arrow" points from a prime key (PK) to a non-PK field, so that is clearly 1/many. I.e. due to this part of the relationship, you could already get multiple records. The right-side "arrow" points from the Location to the Contacts, but because the left-side arrow wasn't pointing to a unique member, you still have multiple places where an update can go. Then, because the right-side ALSO points to a non-PK field, it could also return multiple records even not counting the left-hand part. Since you "anchored" that relationship in the Vendors table, what that relationship says IN ENGLISH is that you have multiple vendors and that they have multiple agents who are scattered in multiple locations. So there is no uniqueness here and the concept I mentioned earlier (ambiguity of update target) hasn't been addressed.

The bottom diagram is very different. You are pointing the relational arrows to three PKs, so there would indeed be uniqueness in any query that drags those attributes together (vendor, company, contact, location). But what THAT says IN ENGLISH is vendors have only one location, one contact, and one company. Is that what you have?

You are trying to make a query that will, in a single operation, perform updates for multiple tables with (apparently) non-unique common fields. The JOIN clauses in the top diagram would allow that IF you had constraining WHERE clauses to go along with them. The JOIN implications in the bottom diagram disallow creation of multiple locations for a given vendor ID in the first place.

This isn't meant to be a "dig" but at the moment it looks like you are designing by dart board. There is an old rule that applies here and I have to unlimber it. Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

What that means in practical terms is that first you have to get the relationships and their reality CLEARLY understood and delineated. I often tell folks to get a dry-erase board, proper dry-erase markers, and some sticky-note pads. You start with one note for each of Company, Vendor, Location, and Contact (representing tables). Then draw lines representing how you see their relationship to each other. Which ones are unique? Which ones are NOT unique? Are any of those relationships redundant? (Allows you to collapse two tables into one.) For example, if a contact can only represent one company at a time, or one LOCATION at a time, perhaps "Contact" is an attribute of "Company" OR is an attribute of "Location." Perhaps "Vendor" and "Company" are the same thing, one being an attribute of the other. Clarify the relationships and mentally imagine how to start with one piece of info to identify something like, I'm in location X, what vendors or what companies do I call?

Until you can see the unique and non-unique relationships, you aren't ready to put anything into the computer. And you will bang your head against the "one query to update multiple tables" conundrum in frustration. But if you have a diagram in front of you, perhaps you will be able to SEE which relationships don't support your goal. (And I suspect that in some cases, it is possible that what you want can't be done.)

 

[Pat Hartman]

The goal is to have the 3 tables that can be updated from a single form.

Why? The user is going to be very confused about the "duplication". Take a look at #9 again.

Below is the relationship I now have however, I do not know if it is correct (hierarchical)
Was not sure if I needed to add FK's from the upstream tables to tbl_Contacts.

Absolutely not. Each child contains a FK which points to its immediate parent. In image 1, you need to remove the VendorID from the contacts table.

I added another table and created this relationship in a query.
It seems to work but is it correct? I know that just because it works, doesn't mean it is correct as it could cause problems later on...

This is a very different construction. It limits the number of contacts for a vendor to 1. It limits the number of locations for a vendor to 1. It limits the number of companies for a vendor (whatever that means) to 1. This design makes no sense at all.

 

[Kyp]

@The_Doc_Man
@Pat Hartman

Just not getting it….

What I do know is that in my list of vendors, one vendor can have multiple location(s) each location will have multiple contacts.

The vendor “Table” displays the correct data in the correct relations.
I just want to be able to add, remove, update from a single form, preferably not a split form.

What am I not understanding?

I attached the file.

 

[mike60smart]

@The_Doc_Man
@Pat Hartman

Just not getting it….

What I do know is that in my list of vendors, one vendor can have multiple location(s) each location will have multiple contacts.

The vendor “Table” displays the correct data in the correct relations.
I just want to be able to add, remove, update from a single form, preferably not a split form.

What am I not understanding?

I attached the file.

Hi
You can lay out the Data Input Form as shown in the attached.

 

[Pat Hartman]

Your data is hiding the duplication from you. You also swapped the location with the contact. Is that what you actually want because it is duplicating the location data if there is more than one contact at a location?

Looks like @mike60smart fixed the relationship and built a rational form with two subforms for you to use.