Company DB - how can I create 1 Main Form that will link all 3 categories?

[paul61]

I would be very grateful if anyone would be able to guide me to create a DB of Companies which fall into three different categories, as follows:-


1. Manufacturer (eg. a company that makes products).
2. Official Distributors (eg. a company that has an official agreement with the manufacturer to sell their products).
3. General Suppliers (eg. a company that sources and sells the manufacturers products but does not have any official agreement).


I think I can do the basics, however, my biggest challenge is how to create just ONE central Form that will allow the User to do all of the following

a) Create a new Record (for either a Manufacturer/Distributor/Supplier).
b) On the SAME Form/Record, to add the name of an Official Distributor(s) and/or General Supplier(s) using a Drop Down Box (eg. like a SUB FORM)?
Eg. I do NOT want to have one Form for Manufacturers details, another Form for Distributor details and another Form for Supplier details).



So I was wondering if I would actually need THREE different Tables before I would be able to create a Relationship between the Manufacturer/Distributor/Supplier? If yes, then how would I merge all 3 Tables into 1 Form - so that the User only needs to use 1 central Form to create say, a new Manufacturer and then add the Distributor(s)/Supplier(s) details on the same Form etc?


Or, is it possible to input the Manufacturer/Distributor/Supplier details on ONE Table and then create Relationships within that Table?


Apologies if I have not explained very clearly - but please feel free to ask any questions.


Thank you all in advance for your expertise.


Paul

[CJ_London]

they are all the same type of entity - companies. Include a field for what type of company they are

don't understand b but if you are talking about relationships to the same table, perfectly doable. Howe you do it depends on whether a manufacturer or supplier can have more than one distributor. If yes, you need a join table, in no include an extra field in the table

[paul61]

Thanks very much for your reply, Ajax. I haven't worked with Join before so I will do a bit of research on that. Yes, a manufacturer can have more than one distributor.

So, for example, if I will create Table1 which includes a field to identify if the company is a manufacturer/distributor/supplier.
If I create Company 1 (who is, say, Manufacturer 1).
Then I create Company 2 (who is, say Distributor 1 for Manufacturer 1).
Then I create Company 3 (who is, say Distributor 2 for Manufacturer 1).

So I am trying to understand if the Join will allow me to go to the record for Manufacturer 1, scroll to a SubForm on that same record, and add the name of Distributor 1 & Distributor 2 from a ComboBox?

I am just trying to get my head around how that will work and how the Join is related?

Thank you for your patience with me .
Paul

[paul61]

I have attached my very poor effort to try and show what I am trying to achieve.

Thanks again.
Paul

[orange]

This Translation techniques video by Brian Finnegan may be helpful. Approach is probably dependent on what attributes you record for each of these.

[CJ_London]

See the attached which will give you the flexibility you will need.

The relationship is mapped using a linking table - see the relationships window.

The relationship is mapped on the form and subform via the subform linkchild/master properties

This is only to demonstrate the principle

[CJ_London]

Made an error in the example db - revised version here

[paul61]

Thanks a lot, Ajax, for your very useful sample DB.

It is absolutely what I am looking for and it goes a long way to starting my complicated DB (well, it is complicated for me anyway!!).

I probably didn’t make it fully clear in my initial message that the functionality I am hoping for includes the following:
1. When a Manufacturer 1 record is created/viewed, the User can use a subform combobox to manually add a Distributor 1 (yes all OK, and this can be done on the currently DB).
2. And then, the User should be able to view the Distributor 1 record and see that the Manufacturer 1 details have been (automatically) populated in the Distributor 1 subform. Eg. the cross-link can be seen on both the Manufacturer and Distributor records.

Similarly, I am hoping for the reverse to happen, for example:
3. The User should be able to create/view Distributor 2 record and manually add the name of a Manufacturer (Distributor or Supplier) using a subform combobox to manually add a Manuf/Dist/Supp. However, no subform exists currently, unless the company is a Manufacturer.
4. And then, as point 2 above, the User should be able to view the Manufacturer record and see that Distributor details have been (automatically) populated in the Manufacturer subform. Eg. the cross-link can be seen on both the Distributor and records.

I am not sure if all this is above my level, but at the moment, I am still trying to understand where the qryCompany is used and what it is used for. So any guidance on that would be appreciated.

Thank you in advance for any inputs. Take care.
Paul

[CJ_London]

I explained it in my post - go to database tools>relationships to see how they link

If you want to have a distributor see what manufacturers they distribute, you don't need another form or table

I've pointed you in a direction to go based on your posts #1 and #3. It is a relatively simple matter to incorporate the new requirement in post #8.

Basically a change to the combo rowsource, a change to the linkchild/master properties of the subform and perhaps a label caption or two. All of which can be done with code.

However I'm not going to show you how because at this point, you are starting from the wrong position. You are thinking about form design and what the tables would need to look like. Where you should be starting is table and relationship design to map the process and design of your app. Once you have that down pat, then you can think about forms and reports.

So you have three categories of company - there must be something else - products from manufacturers, shipping orders, customers etc.

Take a look at the link Orange provided, google/bing 'database normalisation', get a pack of post it notes and a blank bit of wall.

As you think of the different entities, stick their name high up on the wall. As you think of what you need to know about each entity, put that below the appropriate post it note bearing in mind normalisation. What you need to know will broadly be based on three things - 1. what is needed for the app outputs (reports etc) 2. what is needed to link the entities together (relationships) 3. what is needed to control the app processes. I did this for a client a few years back - they had covered some 15 metres of wall by the time they had worked out what they actually wanted.

[paul61]

Thanks, Ajax.

I hope I will not need 15 metres of wall !!!!!!!!!!!!!!!

But thank you for your guidance - it will take a lot of research and learning for my 60yo brain to cope with. But I hope it keeps me active and youthful.

Thanks again - wish me luck.

Paul

[CJ_London]

good luck