Display connected data through duplicate data (1 Viewer)

Interninneed

New member
Local time
Today, 10:20
Joined
Jul 21, 2021
Messages
6
I am sorry if my title makes little sense, I am learning Access as I go and I do not have all of the vocabulary. I will try to explain but I think the pictures I have will work better.
I am trying to show that two pieces of equipment are connected by the same conduit/wire (see drawn pic).
I need to be able to :
-Input the two pieces of equipment and the conduit at the same time (from combo boxes from connected already populated tables)
ex. [equip 99 ]-[conduit 1002]-[equip100]
-Create a query that shows all of the equipment and their connected equipment.
-Create a report that shows all of the equipment, its information, and the connected equipment and its information.

Issues:
-Creating a to/from field does not allow all of the equipment to be displayed at the same time. ex I can only show the to but not the from on a report.

I am not afraid to use code if needed. I know I showed pics from excel but It was the best way I could think of showing what I wanted.
 

Attachments

  • 20210726_123007.jpg
    20210726_123007.jpg
    1.4 MB · Views: 208
  • AccessWhatIWant.PNG
    AccessWhatIWant.PNG
    10.5 KB · Views: 202
  • accessWhatIHave.PNG
    accessWhatIHave.PNG
    7.6 KB · Views: 203

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
You need a junction table to hold the relationship.

tblEquipment
Equipment (PK)
...

tblConduit
Conduit (PK)
FromEquipment (FK to tblEquipment)
ToEquipment (FK to tblEquipment)

I don't know if there are business rules to enforce but I'm guessing there are. For example Can there be two different Conduits for the same From/To pair? If not, tblCondiut also needs a unique index on FromEquipment and ToEquipment. You will need to use the Index dialog. You cannot build this in table design view.

In this case, I used natural keys as the PK's and FK's but it's up to you.
 

Interninneed

New member
Local time
Today, 10:20
Joined
Jul 21, 2021
Messages
6
Thank you for replying.
I do have Junction tables. The naming is different but Asset = Equipment and JT =Junction Table. Also, I know that the spaces need to be removed from the fields... long story.
There can only be two pieces of equipment to one conduit.
One Piece of equipment can have many conduits.
 

Attachments

  • Capture of Tables.PNG
    Capture of Tables.PNG
    76.3 KB · Views: 202

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
Look at my table. It is different from yours. I ASSUMED that Conduit was unique (you confirmed this) and so it can serve as the junction table. It is connecting TWO assets to a single conduit. It isn't common that the junction table has a natural PK but yours does which is why I structured my suggestion the way I did.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:20
Joined
May 7, 2009
Messages
19,175
on your relatation,
remove the Join from Assets IDFKto.
drag another Table Assets and you connect the Assets IDFKto to this table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:20
Joined
Feb 19, 2002
Messages
42,983
Arnel's suggestion to add a second instance of the asset table will solve the problem. However since the relationship between Table CW Conduit and JT Asset Conduits is technically 1-1, the extra table is not necessary. Since you have an additional piece of data - Size IDFK, you can add that to the "middle" table. You do have to change the PK to be just conduit.

If there can be multiple conduits connecting two assets, then the Unique index must include all three fields - conduit, asset1, and asset2. If there can be only a single connection between two assets, then the Unique index should be just asset1 + asset2.

Technically creating extraneous 1-side tables isn't wrong. There are only a few valid reasons for creating them and I don't see one here.

PS, I'm sure someone has already mentioned this but it is poor practice to use embedded spaces or special characters in your object names. Names should contain only letters (upper or lower case), numbers, and the underscore (_). All other characters cause a problem when the name must be used in code so Access forces you to encase the offending names in square brackets. But the problems go further. Access actually changes your name when you create forms and reports. Look at the event names for the control named [Room IDFK]. To amuse yourself to see how bad it can get, create a table with three fields - Amt#, Amt%, Amt$ and see what happens.

My preferred naming method is CamelCase where you upper case the first letter of each "word". Others prefer the_underscore. I don't like the look of those names and I've gotten lazy in my old age and prefer to type with all lower case and I can't do that if I use the_underscore. The nice thing about intellisense is as soon as I finish typing a name, Access cases it the way it was defined and if it doesn't, then I know immediately that I made a typo.
 

Interninneed

New member
Local time
Today, 10:20
Joined
Jul 21, 2021
Messages
6
I found the solution, but due to a time crunch I am only now able to post it.
I needed to write the SQL to create the solution not change the tables. Changing the table to have keys or to/from creates issues down the line for what we need the data for. Here is my solution:

The tables and relationships:
1628794671650.png

I created two queries of the same data (technically I added one extra field) and then combined them in a third Query. Reminder Assets=Equipment
1628794739762.png

Also, the previous example was a borrowed database where the creator had not used naming conventions. This is mine.

Here is the SQL for the combined query

SELECT QryTerminationA.ConduitFK, QryTerminationA.ControlTypeFK, QryTerminationA.SizeFK, QryTerminationA.WireNumber, QryTerminationA.ControlWireGaugeFK, QryTerminationA.ColorFK, QryTerminationA.ControlWireInsulationFK, QryTerminationA.EquipmentName, QryTerminationA.TerminalBlockandNumber, [QryTerminationA]![Expr1] & Chr(13) & Chr(10) & [QryTerminationB]![Expr1] AS Expr2, QryTerminationB.ConduitFK, QryTerminationB.WireNumber, QryTerminationB.EquipmentName, QryTerminationB.TerminalBlockandNumber, QryTerminationB.ControlWireVoltageFK, QryTerminationB.Description, QryTerminationA.Section, QryTerminationB.Section, [QryTerminationB]![Expr1] & Chr(13) & Chr(10) & [QryTerminationA]![Expr1] AS Expr3

FROM QryTerminationA, QryTerminationB

WHERE (((QryTerminationA.ConduitFK)=[QryTerminationB].[ConduitFK]) AND ((QryTerminationA.WireNumber)=[QryTerminationB].[WireNumber]) AND ((QryTerminationA.EquipmentFK)<>[QryTerminationB].[EquipmentFK]));

In order to create the setup I wanted I told Access to show the records where the conduits were the same in both A and B. I did the same for the wire numbers. I also had it only show the records where the equipment names were not equal (<>).

The result
I removed the extra data not needed for the answer. The conduit 16631 shows two equipment names and below it shows the same names in reverse.
1628797163351.png


I hope this is helpful to others who have similar questions.
 

Interninneed

New member
Local time
Today, 10:20
Joined
Jul 21, 2021
Messages
6
Because conduits hold multiple wires and each duplicate is a wire number (1,2,3,...). That is why I included this in the SQL
QryTerminationA.ConduitFK)=[QryTerminationB].[ConduitFK]) AND ((QryTerminationA.WireNumber)=[QryTerminationB].[WireNumber]
 

Users who are viewing this thread

Top Bottom