many to many relationship

[george1111]

hi,
i'm having a problem on my many to many database. It has 3 tables: "suppliers" , "service(s) offered" and a linking table "supplier/service".
I created a form to display detailed information about each supplier and a subform to show which service(s) are offered by each supplier (because a service can have more than one supplier and a supplier can offer more than one service).
However a lot of companies have different departments for which kind of service offered and then different information about contacts, emails, etc.
Any help about how can i display different information on the form fields when i select the different services offered by one supplier?
Thank you a lot.

[sierra7]

Hi
At first glance it sounds like you need a new table, say "department s" which will be in the many-to-many construct (replacing"supp liers"), relegating the current "suppliers" to be a look-up via a SupplierID code.

Personally, I am not happy about your present choice of table names. They are probably 'legal' but if you are just starting your design I would avoid backslashes, brackets and spaces. The convention is to prefix tables with 'tbl' but there are many variations (tbl_, tbl__ etc)

I've attached a tentative entity diagram.
S7
[imgnothumb]http://bytes.com/attachments/attachment/5918d1326119941/entities.jpg[/imgnothumb]

[george1111]

sierra7 thank you for reply.
i already changed my table names as you suggested.
regarding the problem, i don't make it clear.
what i want to know is about the possibility of creating different forms for the same supplier if it supplies more than 1 service.
for example, a cleaning company can offer their services for cleaning floors and for cleaning specific machines. although the business is the same, the chiefs operators can be different and so their contacts. when it happens i need to somehow have 2 different forms with the specific contact field answers for the different services i'm looking for information (of the same supplier).
in the picture we can see a supplier form that supplies two kind of services ("Limpeza dos Sistemas de Extração" and "recolha de RSU").[IMG]C:\Documents and Settings\jffeli x\Desktop[/IMG]
thank you!

[george1111]

sierra7,
Don't bother with the preview question i made..
Can you just explain me how could i solve my problem with the suggested solution?
when i finished to design the tables how do i make the form display different information for the same supplier regarding different departments?
thank you.

[Rabbit]

That's what S7's design allows for.

[sierra7]

Sorry for the delay. I've attached a demo system that you will have to unzip.

I will talk this through tomorrow

S7

[george1111]

S7,
thank you a lot, that's exactly what i'm trying to do.
Do you think that is possible to compare records graphically BY SERVICE in parameters like price, total equipment, and so on? (other fields that i already added).
I tried that starting a new form, inserting a graph, choosing departments table and then "price", however it doesn't work because i can't choose price "none" and price "min" to make that comparison. So i'm thinking about a query looking for 1)"price" and 2)use "select min" function for "price" and then feed my graph with that query.
I need to try it to make sure that i can get specific graphs for each service.
Any different/easier suggestion?
Many thanks.

[sierra7]

OK, first I'll give a quick description of the system I posted yesterday.

The form frmSuppliers is basesd on tblSuppliers and has two sub-forms. The first is frm_SubDepartme nts, based on tblDepartments, and represents the one-to many aspect of the data. The second subform is frmServices, which is perhaps misleading because it is based on the joining table tblDept_Service s, represents the one-to-many situation. The ServiceName is 'looked-up' via a combo-box bound to ServiceID.

The second sub-form is kept synchronised by the On_Current event in frm_SubDepartme nts by writing the DepartmentID to a hidden field on the parent form. While this arrangement does not meet your requirement of showing ALL the services offered by a Supplier, it does allow you to ADD a new Supplier, ADD his Departments and contact details, then PICK services for each Department and add Price and any other fields you may have added. The Services must be set-up prior.

Form frmDepartments maybe irrelevant; it's based on Departments and shows the Services available in a sub-form. New Departments can be ADDED to an existing Supplier but services must be predefined.

The form which probably most meets your needs is frmBrowse_Suppl iers. This is based on Suppliers but the Subform is based on a query joining the Departments table with joining-table tblDept_Service s. All of the services for a company are shown at the expense of repeating the Department data. I called this form 'Browse' because it has limited INPUT capabilities.

You may want to note how much of the data is entered via combo-boxes which use two columns with the first (bound) column hidden. This is to help insure that you are inserting on the right side pof the joins.

You have added some additional requirement which I will have to consider. The Moderators may split these off into different threads.
S7

[sierra7]

Hi,
My first reaction is that if you have your data structured correctly then you should be able to display anything that you want. That was the purpose of getting the input screens correct.

However, my second observation would be "don't start with a Graph!" First you must design your query that produces the numbers that you want to see in the graph. You seem to have some issues about zero cost and no-cost. I'm not clear why this is a problem but you need to resolve it. If you want to know the Supplier with the lowest price for a service, why not just list the prices in ascending order? (I can appreciate that a graph could additionally show frequency and spread etc.)

Note that my prices are on the 'join' table because that is the only point specific to both the Department and a Service. This is where other data such as total equipment' should be added.

Having said this it should be a simple matter to design a new form based on Service, then embed separate sub-forms (or graphs) for Price or Equipment. You just have to set-up your queries correctly.
S7