I 2 of 5 check duplicates

[Databaseguy]

We're implementing an 1 2 of 5 system where I work. Basically, an I 2 of 5 is a unique number given to an assorment of product. For instance:

I 2 of 5 number 1 could consist of

1 ea product 1
2 ea priduct 2
2 ea product 3
1 ea product 4

When adding a new I 2 of 5, I want to make sure that the above listed combination of products and quantities does not already exsist in the DB. If it does I'll diplay a message saying that combo (assorment) already exsists for I 2 of 5 "X"

I thought of multiplying the product times the quantity, and summing the result, but different product combos and quantities could have the same sum.

I usually don't cross post but I'll put this in the MS SQL server area too.

 

[RDWilson2]

Let me see if I understand what you are trying to do with regard to an "I 2 of 5 number":

Let's assume that you have created an "I 2 of 5 number such that it consists of the following:
1 ea product 1
2 ea product 2
2 ea product 3
1 ea product 4

You do not want to allow the creation of ANOTHER "I 2 of 5 number" with exactly the same combination of products.

So, you COULD create an "I 2 of 5 number" with the following:
I 2 of 5 number 1 could consist of

1 ea product 1
1 ea product 2
2 ea product 3
1 ea product 4

or it could consist of:
1 ea product 1
2 ea product 2
2 ea product 3
1 ea product 4
1 ea product 5


Do I understand correctly?

 

[Databaseguy]

Exactly.

So far, I thinkng of creating a temp table to store the new assortment. Then, loop through all of the current I 2 of 5s using group by and inner join to the temp table. If I get a match, I'm trying to create a duplite.

Problem with that fix is if I have 25,000 I 2 of 5s, I'll have to open 25,000 group by record sets.....

 

[Zion7]

If you have all the I2of5's already stored in a table, all you simply have to do is, open a recordset, based on the new I2Of5, then search, you will get an immediate EOF or not.
eg.

Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset

rec.Open "SELECT * FROM tblI2Of5 WHERE txtI2Of5 = """ & Me.txtI2Of5 & """", acOpenDynaset, acLockOptimistic

If rec.EOF Then Me.txtI2Of5 = .... Else MsgBox "Now, Wuz up wi' dat?"

Of course, change the table & field names accordingly.

is this an option?
Good Luck!