No Duplicates?

[mlopes1]

I have a form where a user first selects a company from a combo box, and then selects a product from a seperate combo box. I would like to prevent duplicates of the same company/product combo. Of course, a company could have multiple products, or a product bought by multiple companies so I can not set their properties to not allow duplicates. How would I go about checking for this? Thank you!

Marco

 

[cogent1]

You can have more than one primary key. If there is a many-to-many relationship between companies and products, you establish a joining table between the Companies table and the Products table with companyID and ProductID as joint primary foreign keys. This allows only unique combinations of the two to be saved. The Northwind database gives a good example of how to do this.

 

[mlopes1]

I am a beginner with this so bare with me, but in the northwind db their joined tables assume you know all the suppliers for each product. In other words, they know ahead of time that Product X has suppliers a, b and c. In my situation, I have a table with 1000 products, and a seperate table with 100 companies. I have no way of linking the two because they share no fields. I don't know what products a customer buys until a sales manager goes on the form and fills out a sales report. I know this is simple question... thank you

 

[cogent1]

OK, you are saying the tables are not related. So what do you do with the selections in the combo box? You can't prevent duplications unless you store a record of transactions somewhere , or am I misunderstanding?

 

[mlopes1]

I am probably not being clear at all and apologize! ... I will start from the beginning.

I have a table with Product Information, a table with Company list and a table called 'Selected' that holds the records created on the form. Selected, I am guessing, is where I need to set up the foreign keys you are talking about. Selected holds the Company Name(Primary Key in Company Table), Product Code(Primary Key in Product table), Quantity and a few other user provided fields. It is in this table that I would like to prevent duplicates of Company-Product combos.

Between our last posts I was playing around with the relationships to try and set up the foreign keys, but here is the problem. My Products table is actually a linked table to an Excel worksheet and therefore it won't let me choose a Join Type? How would I go about implementing your suggestions? Should I create a regular table that imports, rather than links, the data from Excel and then do the relationships? Thanks a ton for your help!!

Marco

 

[cogent1]

It sounds as if you have already set up the third table you require- your tables are related after all!

If you have problems with linked tables, import the data as you suggest.

In the Company table, CompanyID should be the PK (Primary Key).

In the Products Table ProductID should be the PK.

In the "Selected" Table, you will have ProductID as a foreign key, i.e. it will be the 'many' side of a join to the PK in the products table. It must be Long Integer type and allow duplicates.

A similar scenario must be set up for the CompanyID field.

Then make BOTH fields primary keys for the table.

If you attempt to save a record that has the same ProductID AND the same CompanyID as an existing record, you will get an error message and the save will fail.

(The Order details table in Northwind corresponds to your Selected Table)

 

[mlopes1]

Perfect, thank you very much. I am about to post a new question too!