0 votes
in Education by (1.7m points)
I have an access table that has in one column a list of groups and then another column that has a list of products. I would like to run a report to show how many groups are in all products, how many groups are at least 1 of the products. I have as many as 6 products in a given table and over 5,000 unique groups.

Below is an example of what I am looking for:

Table:

Group | Product  

AAAA  | 123456  

AAAA  | 234578  

AAAA  | 456789  

AAAA  | 789012  

BBBB  | 123456  

BBBB  | 234578  

BBBB  | 456789  

CCCC  | 123456  

CCCC  | 234578  

CCCC  | 456789  

CCCC  | 789012  

Expected Results:

Product 123456 and 234578 and 456789 and 789012 has 2 groups  

Product 123456 or 234578 or 456789 or 789012 or has 3 groups  

I tried the below query but it is only for when it is a product or another product not when in both. I am looking for when a group is in product x and product y and product z but not in product a.

  SELECT group  

  FROM GroupProducts  

  WHERE   

         product in (’123456’,’2345678’,’456789’)  

         and product not in (’789012’)

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
I would like to run a report to show how many groups are in all products

Assuming no duplicate records, the following query should return the set of all groups which are associated with all products in the dataset:

select t.group from YourTable t group by t.group

having count(t.group) =

(

    select count(*) from (select distinct u.product from YourTable u)

)

You can count these groups by simply enclosing the above with a select count(*) query:

select count(*) from

(

    select t.group from YourTable t group by t.group

    having count(t.group) =

    (

        select count(*) from (select distinct u.product from YourTable u)

    )

)

how many groups are at least 1 of the products

This one is significantly easier, since you can simply select the number of distinct groups in the dataset:

select distinct t.group from YourTable t

Which can then be counted in the same manner as previously described:

select count(*) from

(

    select distinct t.group from YourTable t

)

In all of the above examples, replace all instances of YourTable with the name of your table.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
...