Test if a compound. (two or more joined fields) exists (1 Viewer)

[Bobp3114]

I have have two tables...tblProducts and tblCustomerPartNumbers. I need to record the part number a customer gives to a product that the manufacturer has given a part number, ie the manufacturer builds a product and sets the Part Number as R5G100, but the customer lists it as 000-123-00. I have a field on my entry form that combines fields so that I ensure it is not duplicated.... Me.txtRecordIdentifier = Me.txtCustomerPartNumber & " " & cboCustomer & " " & Me.cboManufacturer & " " & Me.txtDescription

I now need to be able to test if a product has been matched to a customer and recorded. To make things difficult, different companies using the same product give it different Part Numbers and others will use the manufacturers number.

 

[The_Doc_Man]

The trick is always to design your data layout ahead of time to be able to accomplish your goal. It sounds like some of your parts have some number of customer-originated aliases and you don't at the moment have a good supporting structure.

One man's opinion: For your tblCustomerPartNumbers you would have the customer's ID or number, the part number assigned by that customer, and a link (we would call this a foreign key=FK) to the record in tblProducts that shows the manufacturer's part number and all of the other critical data based on the part in question. The only thing you would ever put in the customer part number record is enough data to point to the correct record in the products table and an indicator of who assigned that name, and of course the assigned name.

You would always put a detailed description of the actual part next to the manufacturer's part number. If manufacturer's part numbers can ever be duplicated then you would use some arbitrary number as the index, which would probably become the prime key (PK) for that table.

If you want to know if a customer HAS assigned a number, you would just search for the record in the Products table that contains the FK that "points to" the PK for the selected part number in the "official" part number table.

Here's a tougher question for you: Since customers can assign an ID to something that seems almost arbitrary and that already has a manufacturer's ID, is it ever possible that two different customers will ever arbitrarily assign the same part number to two different parts? I.e. customer A will assign code 012-345 to headlight bulbs but customer B will assign code 012-345 to tulip bulbs?

 

[Bobp3114]

Thanks Doc man
Like I said...Difficult and you just made it more so!
The structure of the product table (tblProducts), is ProductID...PartNumber....Product etc etc (Product is a detailed description of the product)
The structure of the table recording the Customers associated part number is CustomerPartNumberID...CustomerPartNumber...ProductId (the link to the tblProducts)..and StationID (the link to the Customer tblStationData) an finally recently added RecordIdentifier. The products table has been in use for years, the need for the customer's part number matching is recent

 

[The_Doc_Man]

Sounds like your "ProductID" is the PK of the products table. As such, the immediate solution for the customer part ID seems to be your ProductID field. If you already have that then the way to know if a given product has been given an "alias" name is to search for that product's ID to be in the CustomerPart ID.

 

[plog]

I now need to be able to test if a product has been matched to a customer and recorded.

When, how and what is the context of the test? Do you need to test for this as the user is entering data on a form? Or is this a test you will personally run at some interval (daily, weekly, etc.)?

Please explain better what you envision from start to finish: User does a,b,c; User clicks a button and test is run; If duplicate do X, if not duplicate do Y, etc. etc.

Also, please give us some sample data. Show us a few records from both tables that demonstrate all cases of duplicates and non-duplicates so that we can better grasp the test needed.

 

[Bobp3114]

The need to test is to deny duplicate entries. When an order is received it notes both the manufacturer's part number and description as well as the part number the customer gives the product. We need to record both. The manufacturer's part number is usually in the system. There is an entry form to allow the customer's part number to be matched to both the manufacturer's number and the name of the customer, as different customers using the same product allocate different part numbers to the same product

 

[Mike Krailo]

I understand what your doing to be labeling parts with custom part numbers as requested by the customer. So each order will have a CustID, PartID, CustPartNum, and CustomerDescription. Is that right? Or is there only one description for the part?

So the design is essentially a standard four table design with the added detail of CustPartNum column for the customer to fill in their custom part number for each product ordered. I'm not sure where the different companies come in though. Are the different companies the customers?

 

[Pat Hartman]

I have a field on my entry form that combines fields so that I ensure it is not duplicated.... Me.txtRecordIdentifier = Me.txtCustomerPartNumber & " " & cboCustomer & " " & Me.cboManufacturer & " " & Me.txtDescription

That is not the correct method. Use a multi-field unique index. Open the indexes dialog to create it.

On the first fully blank line, add a name, pick the first field, check the unique property.
Go to the next fully blank line. Skip the name field, pick the second field
Go to the next fully blank line. Skip the name field, pick the third field.

You can have up to 10 fields in a compound index.

Each separate index starts with a name field and subsequent lines with blank names belong to the "above" index.

You can create multi-field primary keys directly in the GUI by using Shift-click or Control-click if the fields are not adjacent.