SQL query in Access - basic question
Posted on 2014-08-10
Dear experts -
I have a basic query that lists vendors, by vendor_ID. I have various criteria to select the vendors, based on fields in the vendors table.
I have a separate query that lists, by vendor_ID, all the 'brands' (by brand_ID) for all vendors, with a total in each line for the number of actions that vendor has performed.
Vendor_ID Brand_ID Count
1 100 10
1 101 2
NOW - I want users to be able to specify MULTIPLE BRAND_IDs (i'll have a pick list for this - it will essentially output a string - e.g., '100,101,102" and then I want to show the resulting vendors, with the SUM of the actions for ALL these brands.
THUS, in the example above, if the user wanted the results showing vendors who worked on brands 100 and 101, it would diplay vendor_ID with the count of 12.
Let me know your thoughts on an easy way to do this - I was thinking of building a SQL string, using either "OR" or "IN" to find the related records in the related brands table, and then SUM the count field...
Please remember this is in ACCESS.