Creating an "Associated Assets" subform

[PossiblySoup]

I'm building a database for tracking assets and wanted a way to associate assets with one another (like a sensor that is currently plugged into a detector would be associated with that detector). The way I'm currently doing this is by using a multi value field linking back to the same table: specifically, records in the AssetList table have a multi value field called LinkedAssets that lists the IDs of associated records in the same table.

I have built a form for viewing individual assets in detail and included a subform that is intended to display any associated assets, but so far I've only been able to get it to display assets that have the current record in their LinkedAsset field. Ideally, I want the subform to also include any assets that the current record has in its own LinkedAsset field, but after messing around with union queries and other things I got nowhere.

If anyone has guidance or can point me towards the next steps, that would be greatly appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

If you're still not set in using a MVF, I would encourage you to reconsider not using it. Otherwise, to properly help you, please consider sharing a sample copy of your db.

 

[Uncle Gizmo]

I'd say DON'T use MVF!

I reckon you need a many to many relationship. More info HERE:-

Many to Many Relationship - Nifty Access

Many to Many Relationship - 3 Video's In their simplest form Many to Many relationships are just two columns in a table. The first column representing one side of the Many to Many Relationship and the other column representing the other side of the many to many Relationship. In this particular...

www.niftyaccess.com

 

[PossiblySoup]

Thank you for the welcome, and for the lightning fast responses! The MVF isn't set in stone (I had seen in my research that a lot of people don't like it, so I'm not surprised with this response!) but I'm not sure how best to structure a many to many relationship when linking together records on the same table. I'll have a look at the link Uncle Gizmo provided and if I can't get things working with that I'll see about sharing a sample copy of the database. Thank you!

 

[The_Doc_Man]

Many-to-many, even within the same table, isn't that hard. You have to offer a "polarity" definition that says item X "belongs to" item Y or maybe "X depends on Y" or "X is an element of Y." Then you make a junction table that joins the two records together with two fields: Owned and Owner (or Belonging and Belonged or pick your favorite nomenclature for what owns what.) Then put the PK of the owned record in the Owned field and the PK of the owner record in the Owned field. Then all you need is a JOIN query that "goes through" the junction table.

For finding dependent elements, you would query the table with a LEFT (OUTER) JOIN that finds all of the elements and any records in the junction table that match through the Owner record. Then a second JOIN brings you every dependent record.

If you are not so familiar with junction tables, it is a topic you can look up in this forum using the Search function.

The skeleton of this type of JOIN would RESEMBLE this:

SELECT MAIN.Y, JNCT.YLINK, JNCT.XLINK FROM 
( table AS MAIN2 INNER JOIN junction-table AS JNCT ON MAIN2.X = JNCT.XLINK ) LEFT JOIN table AS MAIN ON MAIN.Y = JNCT.YLINK
WHERE MAIN.Y NOT IN (SELECT XLINK FROM junction-table)

This will give you every Y (i.e. "owner record") with nulls in the X-related fields for Y records that own no X records. It will fill in the X-field info where there are matching records. AND it will suppress showing the X-records (which are "owned") as an "owner." It will look SOMETHING like that but you might well need to play with it some.

 

[PossiblySoup]

I did see that during my research, but then I ran into the issue that assets aren't always owners or owned, just associated with one another.

Would it be bad practice (or even possible) to use the junction table to search for associated items both ways i.e., listing items Owned by an asset, as well as items that are Owners of the asset?

 

[Pat Hartman]

You can't do M-M with only a single table. You can do 1-M with ONE table or two tables but M-M required three tables.

I've attached an easy to understand m-m relationship sample. In addition to showing how the three table work (the junction table is normally the subform or a dependent popup form). In the sample, the perspective from classes to employees is shown with a popup for but the relationship from Employees to classes is shown with a subform. Both are correct and can be used interchangeably whichever makes sense in a particular situation.

 

[PossiblySoup]

I've done some more research based on all of your suggestions, and I might have a solution that works for me (assuming it would actually work at all):

• A junction table with three columns: junction table PK, and two other columns: Asset1 and Asset2 (these just contain PKs from the AssetList table)
• Whenever a record is added to the junction table, the smaller PK is put in the Asset1 column and the larger PK is put in Asset 2 (I don't think this is vital, but it should stop redundant duplicates, like one junction record that links PK 3 to PK 5, and another that links PK 5 to PK 3)
• Linked assets can be found by searching the Asset1 column in the junction table for the PK of the current record, then returning the record linked to the PK in the Asset2 column
• The previous step is repeated but with the Asset1 and Asset2 columns flipped
• Display both results from the previous two steps

I'm probably using the wrong terms, but hopefully it gets the gist across.

 

[Pat Hartman]

I don't like it. I don't have anything better at the moment though. Are you POSITIVE there isn't a dependency relationship between the items? Feels like if the relationship are equal, you are going to need to use a union query for the subform AND you are going to have to reorder the columns so that the one you searched for is ALWAYS on the left. That means that the subform will not be updateable. You would need to handle it as an unbound form. That means a lot of behind the scenes coding to manage everything.