find best match in collection

[BLTibbs]

win xp and access 2003:
I am still learning VBA and need help with the following:

I have table "InventoryTbl" with a fields [ISBN] and [Condition]
I have table "TempCompetitorTbl" with a fields [ISBN] and [condition]

I need to loop through every record in "InventoryTbl" and match each record
up with all of the records in "TempCompetitorTbl" (there could be up to 6
matches per IntentoryTbl record). I have to pick one and only one of the <up
to> 6 competitors based on a comparison of [condition] in "InventoryTbl" and
[condition] in "TempCompetitorTbl". Once I select my competitor, I then have
to insert data from that competitor into two fields in the "InventoryTbl" as
I later discard the data in the "TempCompetitorTbl". How would I set up that
data collection and loop code?

Thanks in advance - this group is a lifesaver.

 

[Alex Dybenko]

Hi,
as I understand you - you can build a query to select Competitors, you have
to join both table, group by necessary records from InventoryTbl and count
records in TempCompetitorTbl. here sql to show you idea-I count number of
keywords for each order:

SELECT tblOrders.ID, Count(*) AS CountOfKeyword
FROM tblOrders INNER JOIN tblRecordKeywordsO ON tblOrders.ID =
tblRecordKeywordsO.RecordNumber
GROUP BY tblOrders.ID
HAVING (((Count(*))<6));