Relationships for Dummies (1 Viewer)

[Winterwolf73]

HOLY CRAP guys. When you give information, you GIVE information. I love it. All of the info y'all have given me is great. However, it still does not answer my question. Let me try asking it a different way.

If I am understanding this correctly, the Join Type button in the Edit Relationships really only applies to queries. Though if this was the case, I do see why the developers would put that option here.

In this case, I have one customer that has many orders. I am looking to link the Invoice Number (from tblCustInfo) to the Invoice Number (in tblInvoices). I have a good grasp on how to get this far. However, what I am not sure about is the Join Type. Let me give you what I think the different properties mean, and you can tell me if I am completely off base.

Option 1 - this would pull only the records where both fields are equal. (pretty self explanatory).

Option 2 - this option would include all records from tblCustInfo that matched the field from tblInvoices.
Option 3 - this option would be the same as Option 2 but in reverse order.

So if I wanted to pull all invoices from Customer 1, the easiest way to do this would be to add a field in tblCustInfo (called CustomerNumber) and use Option 1.... correct?

What is confusing me with all of this is exactly how Options 2 & 3 work.

 

[MajP]

I think you are both correct. The confusion is that the "Relationship window" provides two distinct functionality: a relationship builder and a default join for future queries. It makes it confusing since you can think the two are the same or are related.

I think you got it. What is important all the join type here does is set the DEFAULT join when you build a query. It is just a helper. You may need to do a different type of join in two different queries so you can always change the default when working with a query.

If you have referential integrity set then you cannot have a record in one table if the parent does not exist in the other. So 2 and 3 are meaningless options. But assume your tables are related but referrential integrity does not exist. You have a list of vehicles and drivers. Some vehicles are assigned and some are not some drivers are not assigned a vehicles. Then you can make a query showing all vehicles and related drivers some vehicles will not have drivers and another query showing all drivers and assigned vehicles some drivers will not have vehicles. An inner join (choice 1) will only show assigned.

This is what DBGuy is getting at. You really only need relationships for referential integrity, so they are really inner joins. You can build default joins that do not have referential integrity though and that is what Isladogs is saying. They are both agreeing IMO but saying it different.

 

[theDBguy]

HOLY CRAP guys. When you give information, you GIVE information. I love it. All of the info y'all have given me is great. However, it still does not answer my question. Let me try asking it a different way.

If I am understanding this correctly, the Join Type button in the Edit Relationships really only applies to queries. Though if this was the case, I do see why the developers would put that option here.

In this case, I have one customer that has many orders. I am looking to link the Invoice Number (from tblCustInfo) to the Invoice Number (in tblInvoices). I have a good grasp on how to get this far. However, what I am not sure about is the Join Type. Let me give you what I think the different properties mean, and you can tell me if I am completely off base.

Option 1 - this would pull only the records where both fields are equal. (pretty self explanatory).

Option 2 - this option would include all records from tblCustInfo that matched the field from tblInvoices.
Option 3 - this option would be the same as Option 2 but in reverse order.

So if I wanted to pull all invoices from Customer 1, the easiest way to do this would be to add a field in tblCustInfo (called CustomerNumber) and use Option 1.... correct?

What is confusing me with all of this is exactly how Options 2 & 3 work.

View attachment 84222

Hi. If you ask me, my humble opinion is to ignore that button when establishing table relationships. You used the term "pulling records." To me, you can only do that in queries, where join types make sense. In any case, both article links provided should explain what options 2 and 3 mean.

For example, if you wanted to see all customers info, whether they have a matching invoice or not, you might use option 2. But, if you want to see all invoices info, whether they have a matching customer info or nit, you might use option 3.

 

[theDBguy]

If you have referential integrity set then you cannot have a record in one table if the parent does not exist in the other.

Pardon me, but I'll say that a little differently. If you have RI enabled, then you cannot have a foreign key value in the child table without a matching primary key value from the parent table. If the FK field is not required, you can leave it blank and have an orphaned record- so, you can have a child record that does not have an existing parent. Cheers!

 

[isladogs]

Just realised that I forgot to attach the example database to post #14. I've done so now

We seem to be going round in circles here but at the risk of repetition:
1. The main reason for setting relationships is so that you can enforce referential integrity
2. All types of join are permitted in relationships though inner joins are the default.
3. Whichever type of join is used will then automatically be displayed when those tables are used in a query.
However, the join type can be over-ridden in the query.
4. Although it is almost always the case that an inner join would be used when setting R.I. that doesn't have to be the case.
It is also possible to set referential integrity including cascade update/delete in a relationship with an outer join as shown in this example from the attached database (same screenshot as in post #12)

With this arrangement you CAN still add unmatched records to the parent table (tblJSONFiles).
However you CANNOT add unmatched records to any of the child tables. No orphaned children are possible

In other words, this behaviour is EXACTLY the same as if the relationship used an inner join

 

[plog]

I have one customer that has many orders. I am looking to link the Invoice Number (from tblCustInfo) to the Invoice Number (in tblInvoices).

1. No synonyms. We don't know your data, we have no concept of what an "order" is because that is the first time you used that term and there is no table in your relationships screenshots with a similar name. Unexplained synonyms make it hard for us.

2. The primary key value of one table goes into the foreign table to link them. Both tblCustInfo and tblInvoices have a primary key field called [ID] (most likely they are both autonumbers, which is correct). Those are the values you would put in other tables to relate them, not the values in any other fields. So, using [Invoice Number] from tblInvoices as the value to link tblInvoices to other tables is incorrect. You should use tblInvoices.ID to achieve that.

3. In a 1-many relationship the many side of the relationship gets the foreign key. If a record in tblCustInfo can be attached to many records in tblInvoices then tblInvoices should have a field to accomodate a value from tblCustInfo, not the other way around.

 

[Cronk]

@Winterwolf73
I'm surprised nobody has picked up on issues with your table structure. The Invoice Number is not a property of tblCustInfo. It's the other way around. Also name your primary keys something meaningful ie don't use ID for every PK. Have CustID, InvoiceID etc instead. An experienced developer will do this to make it easier to determine which ID relates to which table.

In tblInvoices, have a field CustID which is the foreign key. Base the relationship on CustID so an invoice is linked to a particular customer.

 

[Winterwolf73]

To everyone that responded to this post, I want to say thank you. All of you have been of great help. Even though my question was not directly answered, through both the discussion and the information provided I was able to figure it out.

 

[Pat Hartman]

Access allows you to specify the join type on the relationship as a matter of convenience. That way when you create a query, Access can automatically create the default join type. The join type has nothing to do with enforcing RI.

The use of inner and outer joins is dictated by whether the relationship is required or optional. For example, in your Order table, a CustomerID is required and you set the required property to true. That prevents you from adding an order without a customer. The normal join between Order and Customer would always be an inner join because there should always be a customer for each order. Just because inner is "normal" does not prevent you from using a left or right join if you are doing data analysis and looking for patterns for example. An optional relationship would be something like stateID to the state table. Optional doesn't mean that you can put in a StateID that doesn't exist in the state table, it only means that in some situations you might not have to enter a stateID at all so the field can be can be null. In an optional relationship, null is allowed but bad (meaning no match to a PK) is not allowed. In order to not loose records that don't have a StateID in the ShipToState field, you would use a left join. In this example, BillToState would probably be required but ShipTo state would be optional since the order might not be shipped.

I've attached a sample database I created for a class I taught. If you send it to someone else, please send it in unmodified format. Otherwise use it for your own learning experience.

 

[theDBguy]

My extended article covers all of this and more, with examples of each relationship discussed.
The link to the first part is http://www.mendipdatasystems.co.uk/relationships1/4594533224

@isladogs. I finally got a chance to read your extensive article on table relationships. You have done a good job; however, I failed to see any of the explanations that the OP was seeking in this thread from your articles. The third part of your article is where I saw where you mentioned the different join types, but only in the context of comparing what the value shows up in the RelValue field in the MSysRelationships table. I didn't see any explanation that answers the OP's original question of which join type to use.

However, in the second part of your article, I did see a section where it relates to my opinion about join types in table relationships. In that article, you have a sample VBA code on how to build a table relationship (it even includes how to add referential integrity and cascade deletes and updates). So, my question is, can you show how you would modify that VBA code to specify that the table relationship you're creating uses an OUTER JOIN (LEFT or RIGHT)?

That was the point about me asking how you would do it in T-SQL in SQL Server. In other words, if the only purpose of creating a table relationship is really to enforce RI, then why bother with setting up the Join Types at this point? Although Access gives us a means to select a Join Type while establishing/creating table relationships, I have decided not use it. Rather, I specify Join Types in queries, where it makes sense to me.

Just my 2 cents...

 

[isladogs]

Hi @theDBguy

I finally got a chance to read your extensive article on table relationships. You have done a good job; however, I failed to see any of the explanations that the OP was seeking in this thread from your articles.

Apologies to the OP but I never looked at his original post in detail nor have I done since as he said it is now solved.

My point throughout was that other join types are equally possible in the relationships window and have no effect on the ability to set referential integrity. I have no problem with you not wishing to use outer joins in the relationships window - that's totally your choice.
But I didn't want the thread left with the statement that outer joins aren't possible as that isn't the case

In that article, you have a sample VBA code on how to build a table relationship (it even includes how to add referential integrity and cascade deletes and updates). So, my question is, can you show how you would modify that VBA code to specify that the table relationship you're creating uses an OUTER JOIN (LEFT or RIGHT)?

The code supplied was from Allen Browne's website. I've used it and it works well
However, I've no idea whether its possible to specify a relationship with an outer join using DAO as I've never had any reason to do so.
I'll have a look as and when I have time. Sorry but at the moment I'm still busy restoring files to replace those lost when 2 drives got corrupted.

I thought @Pat Hartman explained the point well in post #29 though I haven't looked at her example app
EDIT: I have now. The example app deals with query join types and not relationships.
I covered similar ground in my Query Join Types article

 

[theDBguy]

But I didn't want the thread left with the statement that outer joins aren't possible as that isn't the case

I hope I didn't make that statement, because that's not what I'm saying either. All I am saying is if the Access Team removes the Join Type button from the Edit Relationship window, I don't think it will have any negative impact at all. Cheers!

 

[isladogs]

From post #4

Basically, what I meant to say there was Join Types don't apply in table relationships. They should always be Inner Joins, just to use a name.

 

[theDBguy]

From post #4

But that's not the same as saying it's not possible, is it? What I'm saying there is they don't make sense in that context (table relationships). Sure, you can use/create them, but I am wondering why one would (because, to me, they don't apply in relationships - only in queries). Hope that clarifies what I said, didn't mean to cause any confusion.

 

[isladogs]

I think we are both repeating ourselves or perhaps beating a dead horse ...

 

[theDBguy]

I think we are both repeating ourselves or perhaps beating a dead horse ...

I agree. My apologies to everyone.

 

[Pat Hartman]

What I'm saying there is they don't make sense in that context (table relationships).

In addition to being a convenience when creating a query, they serve as documentation for optional relationships. I don't use the feature either, probably because I rarely define optional relationships.

 

[isladogs]

Pat
What do you mean by 'optional relationships'?

 

[Pat Hartman]

I described them in #29. Optional means that the FK is not required and so the field is defined as not required and that means that null is allowed. The example I gave is the difference between ShipToST and BillToST. BillToST would be required because you have to bill somebody and they have to have an address but the order might not be shipped so ShipToST is not required and so could be null.

There are also many situations when doing data entry that a piece of data might be missing temporarily. For example, someone fails to enter marital status on a form, do you want to allow the remaining data to be entered or do you want to prevent the record from being saved unless all the data is filled in? This is a business rule that crops up when working in particular with data entry from forms. Rather than assigning dummy values which some applications do, the client may prefer to allow the missing field to be null. The record may be marked "incomplete" so that it won't actually be used for anything except data entry until it is marked "complete". Frequent searches for records that are "incomplete" are run to keep people on the ball with trying to get the missing data or discard the partial record if it isn't ever going to be completed..

 

[isladogs]

I'd forgotten about your explanation in post #29 but still find the idea of 'optional' relationships unclear

I agree with everything in your second paragraph in post #39 though allowing certain fields to be left blank (null) is I think a separate matter.