• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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
0
terpsichore
Asked:
terpsichore
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now