Is it possible to create clickable links to a different Record based on a Relationship

[paul61]

This is my first post, so please be gentle with me 😊. FYI, I am an Access ‘intermediate beginner’. And I would very much appreciate some guidance before I go any further with my project to make sure I am heading in the right direction (and even if it is possible)? Thank you very much in advance to anyone that can suggest the best way forward?

My project is to create a database with details of approx. 2,000 Manufacturers and Distributors.



My biggest problem is:-
I want the Record of the Manufacturer to include clickable Links to any/all of it’s Distributors (a manufacturer may have more than one Distributor).
And I want the Record of the Distributor to include clickable Links to any/all the Manufacturers that it deals with.

So I have created Table 1 with approx. 2,000 rows containing all the Manufacturers and Distributors details as below.
Manufacturer Name (with address, sales person, emails etc etc).
Distributor Name (with address, sales person, emails etc etc).

But I do not if/how it is possible to create these Links? Do I need more than one Table to separate Manufacturers and Distributors? Or what else do I need to do.

Any help, advice or guidance will be very much appreciated.

Thank you in advance.
Paul

[Minty]

I would have thought you would need two tables tblManufacturers and tblDistributors.

You would then need a third Junction table tblManufacturesDisties

This would simply store the Manufactures ID an a single Distributor ID code.
You would have as many records for that Manufacturer as you have distributors.

If a manufacturer can also be a distributor I would add then to the distributor table as well.

The alternative would be stick them all in one table and have two flags IsDistriutor and IsManufacturer to indicate if they are one or the other, or both.
You would still need a junction table to store who distributed what but it would be based on the same source table in this version.

[paul61]

Thanks for the suggestion. I have tried to follow as below....

So, I have started again and created a new tblManufacturers (and I have inserted 2 fields named Distributor 1 and Distributor 2). I inserted the name(s) of the Distributor in these fields. So each Manufacturer Row now has either none, 1 or 2 Distributors?

I also created tblDistributors (and simply inserted the company name of the distributor eg. no mention of a manufacturer in this table).

I then created a joint table (using a Query). But the Query returns too many Rows (eg. it returns the number of Manufacturers x number of Distributors. I guess there should be fewer rows than the maximum. What would I be doing wrong?

[Micron]

IMO you need to research and understand database normalization. Repeating fields of the same 'thing' is thinking with a spreadsheet brain, which will only forever cause difficulty as you are already experiencing. Since you're a novice I think it's best if I post all my usual links. Do follow each one before you start over. They will save you a lot of grief. If you don't grasp normalization from these, do more research and find something that you like better.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Naming conventions
http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[Minty]

Thanks for the suggestion. I have tried to follow as below....

So, I have started again and created a new tblManufacturers (and I have inserted 2 fields named Distributor 1 and Distributor 2)

As Micron has stated you have gone all spreadsheet on us.

This type of design is doomed to fail, what happens if a manufacturer has a third distributor?
Add a Distributer 3 Field?? Then how do you work out who all the active distributors are or even quickly add up how many you have?

The above are rhetorical questions as in a database both are not how you do things.

See the attached super basic example of what was suggested.
Then read up on what Micron has linked to.

[paul61]

Thank you for making me smile by saying I was going all spreadsheet on you!! I assure you that I did not even know

Thank you, Micron. I am going to burn the midnight oil tonight and read as much as I can from the links that you have sent. I last did a database about 5-6 years ago, so that will be useful to refresh a lot of my knowledge.

Thank you also, Minty. The sample database you sent is actually very useful for me and I think you have managed to enlighten me regarding the 'spreadsheet' point. If I have got this right then:-
Should I be adding one row with only one Manufacturer and one Distributor?
And then add a different row with (say) the same Manufacturer with the second Distributor? Etc etc.
And what I have been doing is adding one row with one Manufacturer and two Distributors?

Thank you very much for all your feedback. I love technology and software - it is so logical - but it certainly gives me a headache often.
Paul

[Micron]

And then add a different row with (say) the same Manufacturer with the second Distributor? Etc etc.

In your main table? No.
The normalization links should contain topics on junction tables. Suggest you read that stuff first and then come back and ask questions because the answer and explanation to that question is there.

[Minty]

Thank you for making me smile by saying I was going all spreadsheet on you!! I assure you that I did not even know
Should I be adding one row with only one Manufacturer and one Distributor?
And then add a different row with (say) the same Manufacturer with the second Distributor? Etc etc.
Paul

Yes, the Junction table only lets you add a manufacturer and a single distributor per row.
It is also set up so that you can't duplicate the data in that table - e.g. you can't have a duplicate manf & disti in the table, this is done by having a primary key set on the two foreign key fields in that table.

Any manufacturer can have 1 or many of the available distributors. If you need 3 or twenty three, it makes no difference if you had 100 distributors to pick from.

[Micron]

If I ever found or was given a reason to use a composite index instead of a composite pk field I can't recall it even though I asked. Everybody just seems to say "don't use a composite key". However, now that I see it in your sample, I think I get it. No single field has a unique value but is also a pk. That might cause issues where say, a query operation won't work because there's no single pk value to work with. No idea if Access creates a hidden pk behind a composite primary. I think I will continue to use only composite indexes when such a thing is required.

[Minty]

If I ever found or was given a reason to use a composite index instead of a composite pk field I can't recall it even though I asked. Everybody just seems to say "don't use a composite key". However, now that I see it in your sample, I think I get it. No single field has a unique value but is also a pk. That might cause issues where say, a query operation won't work because there's no single pk value to work with. No idea if Access creates a hidden pk behind a composite primary. I think I will continue to use only composite indexes when such a thing is required.

I have only really used them maybe 1/2 a dozen times in the last 15 years. Normally in this type of situation to ensure no duplicate entries without using a ton of code.
I've mainly found them handy in child tables.

You can also have a unique index on the PK_ID field although some might argue that even having that is unnecessary.

[Micron]

For fun I removed your composite PK and added a composite index. Regardless of which method you use, you will get the error message for 3022, so I think a bit of code is still warranted unless you're comfortable with the built in message and no option to do anything but leave the record in edit mode. If not, perhaps something like this, which should show the built in message for anything but 3022

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String

If DataErr = 3022 Then
   strMsg = "Your combination of manufacturer/distributor already exists." & vbCrLf
   strMsg = strMsg & "Please create a unique combination or click Cancel to undo."
   Response = MsgBox(strMsg, vbOKCancel)
   If Response = 1 Then
      Response = acDataErrContinue
   Else
      Me.Undo
   End If
End If

End Sub

[Minty]

@Micron - I agree it should be an informative message if the error is raised.

What I have found handy - at least with an Access table - is that you can use a composite PK to prevent VBA query inserts from duplications.
This is handy when you simply wan to "fill" a time table planner or something similar without having to work out what the missing entries are.
You won't over-write existing entries, but will create any new or missing ones.

It's quicker and more efficient than trying to join to the existing records and check for Null values.
In Access simply don't add the dbFailOnError switch

SQL Server is a lot more fussy, and needs to be coerced into a similar action a little more gently...

[paul61]

I have read through the links that Micron kindly provided. I thought it would be a long, heavy read, but actually it was fairly easy to absorb. Thank you.

I have attached the latest update of my database. I hope (think) I have followed all the advice so far???? And, if so, I might be ready to move onto the next stage? Thank you very much to Minty for giving me a great start with your test db.

On frmUseThisOne, I go back to my original question to “Is it possible to create clickable links to a different record based on a relationship?”.
What I am trying to do is create some kind of “button”? which will allow the User to go directly to the relevant Manufacturer or Distributor details (which are shown in frmManDetails and frmDistDetails).

Please could you advise what code I need so that the Button will open at the correct Record and without asking the User for more information?

Thank you in advance.
Paul

[Minty]

See the attached for the simple method.
Minty3.zip

Next stage - Add a table called contacts and create a FK field to link it back to your manufacturers.
Now you can have has many contacts for a manufacturer as you need, rather than Contact 1 , contact 2 etc.

Now this is where things might get interesting, and designing it first carefully helps - do you want a second table DistributorContacts or maybe this is where a single table with a "contact belongs to type" might come in useful??

Or Manufacturers and Distributors all in one table with a flag to indicate they are one or the other or both...

Questions questions

I've got paying work to crack on with now so won't revisit until tomorrow earliest or possibly next week. Enjoy.

[Micron]

Is it possible to create clickable links to a different record based on a relationship

Rather than suggest something you want to create it's often better to say what you want to have happen in a scenario. "What can I do so that a user can get to (view) a related record?". The answer could be as simple as "double click on the record - no clickable feature needed" but that would only be one possibility. It is entirely possible that your related record should already be visible, e.g. in a subform, but we usually must ask questions in order to figure it out.