Not -- I am working in MS Access, so the SQL is limited.
In my table, there are two relevant fields. F1 has a company name, and F2 has one of 8 text values, referred to here as 1-8.
Given a company name, I need to find how many of each particular text value there is.
For example, given the name 'company1,' there may be 50 records with 'company1' in F1. In F2, there may only be text values 1, 3, and 4 for those 50 records.
In a query (or multiple queries), I need to
1) retrieve every records where F1 = 'company1'
2) Find all the distinct values of F2 where F1 = 'company1.' For instance, for this condition, F2 = either 2, 3, 4, or 7
3) Find how many times each distinct value in F2 are repeated. Cont. above example, there may be ten 2s, ten 3s, ten 4s, and twenty 7s.
The process would look like this --
User inputs 'company1.' The query returns
Value | Count
2 | 10
3 | 10
4 | 10
7 | 20
My general idea was to have a query with a subquery to generate a list of every F2 value where F1 = company1, then a second subquery to generate all the distinct values in that list. Then, somehow, I need to search through the first subquery for the number of times each value from the second query is repeated.
Here is some test code. My issue here is that it returns a count of [Significance Level:] in (etc...) because it checks if the value is in that subquery, instead of doing the main query with each result in that subquery.
SELECT count(*)
FROM (
SELECT [Significance Level:]
FROM all_data_tester WHERE
[Manufacturer Audited:] = 'Airbus Space & Defense')
WHERE [Significance Level:] in (
SELECT distinct [Significance Level:]
FROM all_data_tester
WHERE [Manufacturer Audited:] = 'Airbus Space & Defense'
);
Sorry if this is confusing, it is difficult to explain. I can clarify anything
Use a GROUP BY-clause
SELECT [Significance Level:], Count(*) As Cnt
FROM all_data_tester
WHERE [Manufacturer Audited:] = 'Airbus Space & Defense'
GROUP BY [Significance Level:]
ORDER BY [Significance Level:]
This creates one row per significance level with the corresponding count.
You can also get this count per manufacturer with
SELECT [Manufacturer Audited:], [Significance Level:], Count(*) As Cnt
FROM all_data_tester
GROUP BY [Manufacturer Audited:], [Significance Level:]
ORDER BY [Manufacturer Audited:], [Significance Level:]
This creates one row per manufacturer and significance level with the corresponding count.