filtering for groups of products

[ChuckW]

Hi,

I have a transaction table that has fields such as
transactionID, Customer, ItemNumber, ItemName, Qty, and
Amount. I am trying to find out instances when a
specific group of products are sold during the same
transaction. For instance, the item numbers are 13020,
12040, 11050, 13340 and 12080. We have over 200 products
but I am trying to find when these five were sold on the
same transaction. I ran a query using Or to get
transaction when at least one of the five sold but I need
to somehow use the TransactionID and compare when all
five have the same transactionID. Can anyone help?

Thanks,

Chuck

 

[Guest]

Do it with 2 queries:

Query1: This query should pull the transaction ID and a value. That value
would be 1 for item 13020, 10 for item 12040, 100 for 11050, 1000 for 13340
and 10000 for 12080.

SELECT Table2.Field1,
IIf([Table2]![Field2]=13020,1,IIf([Table2]![Field2]=12040,10,IIf([Table2]![Field2]=11050,100,IIf([Table2]![Field2]=13340,1000,IIf([Table2]![Field2]=12080,10000))))) AS Expr1
FROM Table2;

This will not only indicate that a transaction ID has a product, but which
one.

Query 2: Now run a query against the first query that sums the values of
Expr1.

SELECT Query1.Field1, Sum(Query1.Expr1) AS SumOfExpr1
FROM Query1
GROUP BY Query1.Field1
HAVING (((Sum(Query1.Expr1))=11111));

This will return only the transactions that have all the products you're
looking for. If you want, you can create more queries that return, say, only
those that have products 11110.

Bob Tulk
MOUS(XP/97)