Relate one field to multiple fields (2 Viewers)

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:42
Joined
Oct 17, 2012
Messages
3,276
In English, it pulls in the ID's for all parts that are in tblAssemblyItems that are not in tblParts. I believe it's one you can find in the query wizard as a 'find unmatched query'.

And it perfectly shows the reason why relationships are so important. Had the relationship been set up before the data was loaded, that assembly record would have been rejected.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Jan 23, 2006
Messages
15,364
The query is looking at

tblParts and tblAssemblyItems and matching on PartID

the criteria is saying Show me the AssemblyItems that have Part(s) that are not in tblParts. That is an unmatched query.

MS Access has a Find Unmatched query wizard

Note:

tblEndItems is 1 : M with tblAssemblyItems also
tblParts is 1:M with tblAssemblyItems

or

tblEndItems --> tblAssemblyItems <--tblParts

OOoops: I seem to be a couple of minutes behind Frothy
 
Last edited:

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Had the relationship been set up before the data was loaded, that assembly record would have been rejected.

Another great tip. Yeah - I had my parts and assembly tables in Excel so I could be sure I matched numbers but obviously that wasn't the greatest idea. I should have compared fields then.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
The query is looking at

tblParts and tblAssemblyItems and matching on PartID

the criteria is saying Show me the AssemblyItems that have Part(s) that are not in tblParts. That is an unmatched query.

MS Access has a Find Unmatched query wizard

Note:

tblEndItems is 1 : M with tblAssemblyItems also
tblParts is 1:M with tblAssemblyItems

or

tblEndItems --> tblAssemblyItems <--tblParts

OOoops: I seem to be a couple of minutes behind Frothy

:) And still helpful. I benefit greatly from having good advice delivered from a variety of ways!

Thank You
 

Dreamweaver

Well-known member
Local time
Today, 17:42
Joined
Nov 28, 2005
Messages
2,466
The only thing is that the "fit" of the two pieces together is often a critical feature for inspection and typically has unique dimensional data that requires recording that only exists when the hat and plate are fit together. I don't know if I actually need to represent that relationship in the db or not but intuition leans me towards "yes."


Have not had a chance to read all the replys but is it possible that there are x number of plates each with a unique set of Dimentions so you would have 6 plates instead of just one.?
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Had the relationship been set up before the data was loaded, that assembly record would have been rejected.

Just to educate myself, I'll try an experiment later with unmatched data on purpose so I can see how that condition resolves using the unmatched data query.

What should I expect?
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Have not had a chance to read all the replys but is it possible that there are x number of plates each with a unique set of Dimentions so you would have 6 plates instead of just one.?

if I am reading you correctly - no. Technically the answer might be yes but each unique assembly has a unique hat and unique plate and each has its own unique ID.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Please tell us more.
Consider, you have a bin of Parts, some of the Parts are not "part" of an Assembly (pardon the pun), but such Part(s) could very well be in a new Assembly. I don't see this as an issue, but maybe I'm missing some business fact(s) that would make this a problem.

JDraw - that is correct....and ALL parts in the bin could be sold as final products at any given point in time. This happens routinely.

I need to play with my new - and working (thank you everybody) - model to make sure I understand how to accommodate those parts that are not part of an assembly but pass through the rest of the process and are sold as final products. Managing assemblies is pretty clear to me now with this new junction table.

The most current db model is attached.

Thanks!

Tim
 

Attachments

  • PartsAssemblies.zip
    27.7 KB · Views: 139
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Jan 23, 2006
Messages
15,364
RE post #26

If you have your relationships and referential integrity set before you add any records, then the DBMS will use those rules to accept or reject records.

Referential Integrity (wikipedia)
Referential integrity is a property of data stating that all of its references are valid. In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
RE post #26

If you have your relationships and referential integrity set before you add any records, then the DBMS will use those rules to accept or reject records.

Referential Integrity (wikipedia)
Referential integrity is a property of data stating that all of its references are valid. In the context of relational databases, it requires that if a value of one attribute of a relation references a value of another attribute, then the referenced value must exist.

Thank you. As I am migrating a variety of old Excel files that have been the company's primary repository of records and data for far too long, that unmatched data query is really going to be helpful. I'm really glad that came up in this post. It's an old family run company where "the way we've always done it" just ain't cutting it anymore. :) Imagine. I just started here last year - so no - I take no responsibility for the "timeliness." :)
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:42
Joined
Oct 17, 2012
Messages
3,276
Just to educate myself, I'll try an experiment later with unmatched data on purpose so I can see how that condition resolves using the unmatched data query.

What should I expect?

Not really sure where your confusion is.

If you create an actual relationship between two tables (on the ribbon: Database Tools -> Relationships, then drag the parent field (tblParts.PartID, for example) to the child/many table over the field you want to link it to (tblAssemblyItems.PartID), drop, and the relationship will be created. Then double-click the link itself make sure 'enforce referential integrity' is on when you select the relationship itself), Access literally prevents you from adding records to the child/many side without a matching entry in the parent/one side.

You can also change how the link works when used in queries by double clicking the join, selecting 'Join Options', then choosing from three options:
  • 'Only include rows where both sides are equal' does just what it says. This is called an INNER JOIN.
  • You can show all records from the table listed on the left and only matching ones from the table on the right. This is a LEFT OUTER JOIN, or LEFT JOIN. (They mean the same thing.)
  • You can show all records from the table listed on the right and only matching ones from the table on the left. This is a RIGHT OUTER JOIN, or RIGHT JOIN.
Note that your find unmatched query uses a right join to do its thing. The two outer joins are used to show 'optional' data. One example: I work with medical claims being reviewed to see if they should have been paid by Medicare instead of my company. Most claims are done correctly, but a number turn out to have been sent to us incorrectly. For those, we generate what's called an 'A/R File' and send it to the Claims department, and we need to track when those files were created and, if necessary, followed up on. So you can create a query that joins the claims table we use with the ARFileHistory table. If I want to see only the claims that had an AR sent, I would use an INNER join. If I want to see all claims, along with the last AR we sent (if any), I would use an OUTER JOIN (I typically use LEFT JOINS; it's a personal preference, but most people do seem to use that convention).

Also in that screen when you double click a relationship are three options: enable referential integrity, cascade update joined fields, and cascade delete joined fields.

ENFORCE REFERENTIAL INTEGRITY
'Enforce referential integrity' makes Access reject any inserts or updates that would result in an invalid join, meaning a value on the MANY end that doesn't have a matching value on the ONE end.

As an example related to your application, if you have 'enforce referential integrity' turned on for the relationship and try to create an Assembly item with PartID 8765301, but tblParts has no part with that ID, you'll get an error message and the record won't be created. The same thing happens if you try to change PartID in tblAssemblyItems to one not in tblParts.

CASCADE UPDATE RELATED FIELDS
This one is pretty straightforward. If you update the related field on the ONE side, then all matching fields on the MANY side are updated to the same value. So if you edit tblParts.PartID from 41 to 141, then all records in tblAssemblyItems that have PartID 41 will ALSO have PartID changed to 141. This keeps the data good (data integrity) and prevents records from being accidentally orphaned. I always use this one.

CASCADE DELETE RELATED RECORDS
Again, straightforward. With this on, if you delete a record from the ONE side, then all related records from the MANY side are also deleted. I always leave this one off to help minimize the damage from accidental deletions. Instead, if I want to delete something and all related records, I do them in two separate deletes. There are others here who keep it enabled. Both approaches have uses and risks, and in the end really should be selected between based on the needs of the application and your data integrity requirements.

If you use relationships wherever possible and always enforce referential integrity (and, if necessary, the cascade options), you should find that you won't often have use for find unmatched queries to find orphaned records. It's still a useful concept to know, though, in case you ever need to create, say, a list of all parts that aren't used in any of your assemblies.
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:42
Joined
Oct 17, 2012
Messages
3,276
Bah, jdraw beat me to part of it while I was typing that beast up. :(
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Not really sure where your confusion is.

Mine is less "confusion" than it is inexperience. :)

Frothy, you - and all the folks here - continue to give me really great info and advice.

....and again - I am really grateful for the time you took with this post. All of that info is and will be very helpful.

This dang thing is finally beginning to come together in a way that makes sense and is becoming more streamlined in the process.

Thank You.

Tim.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Jan 23, 2006
Messages
15,364
Wow!

1 for me and 9 for Frothy ----he's 9 times quicker than me.

Zydeceltico,
Here's a graphic that may be useful (at some time) re getting data from tables.

 

Attachments

  • VariousJoinExamples.png
    VariousJoinExamples.png
    41.5 KB · Views: 365

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
Wow!

1 for me and 9 for Frothy ----he's 9 times quicker than me.

Zydeceltico,
Here's a graphic that may be useful (at some time) re getting data from tables.


HA Jdraw! And you're BOTH super appreciated.

Damn - - - thanks for that graph! YES - that is and will be helpful! :) LOL
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:42
Joined
Oct 17, 2012
Messages
3,276
Here's a graphic that may be useful (at some time) re getting data from tables.

You know, that is an amazing graphic to have when explaining joins.

I'm totally ganking that thing.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:42
Joined
Oct 17, 2012
Messages
3,276
If you look closely, it also gives an example of aliasing.

That's something you don't usually have to deal with in Access, since most queries are handled through the QBE grid, but if you ever start working with SQL Server, it's a godsend.
 

Zydeceltico

Registered User.
Local time
Today, 13:42
Joined
Dec 5, 2017
Messages
843
So here's a question.

Given the table structure I am using now, what is the best way to add a new Part that will be sold as a single product?

Also what is the best way to approach adding a new assembly comprised of two or more parts?

For adding a new assembly w/ x number of parts, would I enter all of the various parts in tblParts first and then go to tblEndItems and enter the new assembly details? I'm wondering how I would approach updating tblAssemblyItems so the entry in tblEndItems (the new assembly) is related to its component parts in tblParts? How do I functionally connect Parts and an Assembly in tblAssemblyItems?

It's likely important to note that new assemblies will likely be created from new parts as new parts are introduced. What is important to understand in light of my question above is that a new part does not necessarily mean there will be a new assembly and usually doesn't. Basically, for the current question, assemblies follow parts in the "real" world.

Should I be thinking about forms, subforms, and update queries?

This is where my inexperience rears its ugly head - again. But it does help me "comprehend" when I can envision the later functionality and understand the roadmap to get there. In other words, I'm not focused on forms. I just want to know that the structure I'm creating will function as I envision later on.

I mean I know right now I can manually enter a new part in tblParts; note the autogenerated Part_PK; go to tblEndItems and enter a new assembly number which will give me an Assembly_PK; then open tblAssemblyItems and make a new entry with both ID numbers - but obviously that is not what it will look like in practice.

Like I said - my inexperience.

Thanks again for any and all insight!

Tim
 

Attachments

  • PartsAssemblies.zip
    37.8 KB · Views: 153

jdraw

Super Moderator
Staff member
Local time
Today, 13:42
Joined
Jan 23, 2006
Messages
15,364
I see 62 EndItems but only 61 Assemblies which I don't quite understand.
It's possible that EndItem (Assembly_PK 62) is planned, but doesn't yet exist.
My thinking is create an EndItem and then assign Parts ---completing the EndItems as you go so numbers are aligned (all enditems are assemblyItems).


My first thought was to copy the AssemblyItems to XXX, then delete all records from AssemblyItems, then restructure AssemblyItems to have composite PK(Assembly_PK + Part_PK). Then set up referential integrity, then repopulate AssemblyItems from XXX...
the idea is to let the database do the referential integrity checking.

Just some thoughts for consideration.
 

Users who are viewing this thread

Top Bottom