multiple lookup percentage match

[ali32uk]

Hi

I m looking for some help, I have 2 tables which hold list of items. table 1 as below

ReqID Item
A001 acb11
A001 200011
A001 10011
A001 a1221
A002 1-11122
A002 2266859
A002 ab2112
A002 R441
A002 1-33244
A002 ACB12

Table 2 as below

QuoteID Item
X01 acb11
X01 200011
X01 10011
X01 a1222
X01 b3321
X02 2266859
X02 ab2112
X02 R441
X02 1-33244
X03 ACB12
X03 w3432
X03 r-22-1
X03 xyz


the above is just sampe set of data, principle is I m looking to group together all "item" against the reqID so in the above

A001 acb11
A001 200011
A001 10011
A001 a1221

and then check in table to is any of the groups have a percentage match, so in the above the sample set the below quoteID group
X01 acb11
X01 200011
X01 10011
X01 a1222
X01 b3321

shows 100% match, all items against reqID "A001" are held in all "X01" quote ID, does anyone know of the best way to go about this in access? datasets will likely have 1000's of lines...

the return would show similar to below
A001 X01 100%
A001 X02 0%
A001 X03 0%
A002 X01 0%
A002 X02 70%
A002 X03 0%

Let me know if this is not clear..

Thanks

Alastair

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

BTW, may an item have more than one ReqID or QuoteID ?

 

[PHV]

What about this ?

SELECT A.ReqID,B.QuoteID,Format(Count(*)/C.Count1,"PERCENT") AS Percentage
FROM  (Table1 A
INNER JOIN Table2 B ON A.Item=B.Item)
INNER JOIN (SELECT ReqID,Count(*) AS Count1 FROM Table1 GROUP BY ReqID) C ON A.ReqID=C.ReqID
GROUP BY A.ReqID,B.QuoteID,C.Count1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886