compare subrecords

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have a table Products, and a subtable Components, which includes
several components of each product. I would like to compare the entries in
the Products tabel to find products that have identical sets of components.
How to do that ? Thanks.
 
Remember Set Theory and Venn Diagrams? Yeah, I know, climb off the ledge,
it's not that bad...

Anyway, this is the basis of what you'll need to infer. Unfortunately,
Access doesn't have a bitswitch or simple function to perform this. (Paradox
for DOS used to have a keyword that did this in one step, but Access is the
Prius to Paradox's Hemi.)

The following website, might get you there:
http://www.mvps.org/access/queries/qry0016.htm

Another way is to solve by the inverse. For each product, you need to
eliminate any other products that doesn't contain what it contains. This
can be done with some VBA, temp tables, and a few queries. More setup time,
but easier to debug and maintain IMHO.
 
Hello, thanks for the message. I thought I'll concatenate the records with a
couple of Do...Loops but there I was stuck with another problem, I am unable
to insert the where criteria to my query in VBA. It works just fine with a
given number, 112, for example, as given below. But I would like the where
clause to be: where ret.kood=kau.kood
I think I am missing some brackets or hyphens but I seem to be unable to
find a suitable combination, could you please help a bit. The field Kood is
in both tables numeric, long integer.

Set kau = db.OpenRecordset("products", DB_OPEN_TABLE)
kau.MoveFirst
Do While Not kau.EOF
conc = ""
Set ret = db.OpenRecordset("SELECT * FROM components WHERE (((
components.kood)=112)) ORDER BY components.kkood;", dbOpenDynaset)
ret.MoveFirst
Do While Not ret.EOF
conc = conc & " " & ret!kkood & " " & ret!q
ret.MoveNext
Loop
kau.Edit
kau!ret = conc
kau.Update
kau.MoveNext
Loop

Thank you,
 
it seems, I solved the mystery: WHERE ((( components.kood)= " & kau!kood & "
)) ORDER BY
 
You may be able to do it in a Query with an SQL String like:

****Untested SQL****
SELECT P1.ProductID, P2.ProductID
FROM [Products] AS P1, [Products] AS P2
WHERE (P1.ProductID > P2.ProductID)
AND NOT EXISTS
(
SELECT C1.ComponentID
FROM [Components] AS C1 LEFT JOIN
[Components] AS C2 ON C1.frg_PartTypeID = C2.frg_PartTypeID
WHERE (C1.frg_ProductID = P1.ProductID)
AND (C2.frg_ProductID = P2.ProductID)
AND (C2.ComponentID Is Null)

UNION

SELECT C4.ComponentID
FROM [Components] AS C3 RIGHT JOIN
[Components] AS C4 ON C4.frg_PartTypeID = C3.frg_PartTypeID
WHERE (C3.frg_ProductID = P1.ProductID)
AND (C4.frg_ProductID = P2.ProductID)
AND (C3.ComponentID Is Null)
)
********

I assumed you have a Many-to-Many relationship between Products (PK
ProductID and PartTypes (PK PartTypeID) and the Resolver Table is known as
Components with surrogate PK ComponentID and 2 FKs (ForeignKeys)
frg_ProductID and frg_PartTypeID.

All PKs & FKs should be indexed, of course, to speed up JET processing.

Unfortunately, the execution of this Query will probably be fairly slow due
to the Cross Join as well as the correlated Union SubQuery but it may still
be faster than using VBA.

Someone else may be able to come up with a more efficient Query / SQL
String.
 
Back
Top