SQL query in Access - basic question

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.

Example:
Vendor_ID
1
2
3
4
5


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
ETC ETC

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.

Thanks
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
An IN() clause is the way to go.  You will need to take the user criteria input and incorporate it into a dynamic SQL Select quert.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this query:
SELECT D2.Vendor_ID, SUM(D2.Qty)
FROM (SELECT D1.Vendor_ID, D1.Brand_ID, COUNT(1) Qty
		FROM MyTableName D1
		GROUP BY D1.Vendor_ID, D1.Brand_ID) AS D2
GROUP BY D2.Vendor_ID

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Forgot to add the search criteria (Brand_ID's).

SELECT D2.Vendor_ID, SUM(D2.Qty)
FROM (SELECT D1.Vendor_ID, D1.Brand_ID, COUNT(1) Qty
		FROM MyTableName D1
                WHERE D1.Brand_ID IN (100,101,102)
		GROUP BY D1.Vendor_ID, D1.Brand_ID) AS D2
GROUP BY D2.Vendor_ID

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

terpsichoreAuthor Commented:
thanks so much - one question, is a subquery the only (reasonable) way to do this?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
In Access it is. At least I don't know a better way to do it in Access.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
very thoughtful responses.
0
IrogSintaCommented:
There's no need to use a subquery.  The SQL you need could simply be:
SELECT Vendor_ID, Count("*") AS Qty
FROM Statements
WHERE Brand_ID In (101,102)
GROUP BY Vendor_ID

Open in new window

Ron
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.