Creating a query that groups and counts without duplicates

Topic

New Reply

[Susan Bradley]

I am trying to create a couple of queries that will group and count items, without counting the duplicates. For instance, say I have a table with the below data. I want to build queries that will tell me:

Query 1: 4 stores bought 4 products during FY21Qtr2;  2 stores bought 3 products during FY21Qtr3; and 2 stores bought 1 product during FY21Qtr4

Query 2: During FY21Qtr2, 123 Cafe bought Cheese 2 times , Lettuce 1 time, and tomatoes 1 time; ABC Market bought Cheese 1 time, Lettuce 2 times, and Tomatoes 1 time; and XYZ Hut bought Lettuce 2 times, and milk 1 time. During FY21Qtr3…and so on…

Query 3: Cheese was purchased by 2 stores in FY21Qtr2; 1 Store in FY21Qtr3; and 2 Stores in FY21Qtr4….and so on.

How would I build these queries? I saw videos that talked about using Distinct and select, but I was very confused by the videos. Below is my sample data table (I have extra columns that are necessary for the table, but not these particular queries that I want to build-just like in my real life table).

Store                Product         # of Boxes          Purchase Date           FY/Qtr

123 Cafe            Lettuce              8                         2/16/21                  F21Q2

123 Cafe            Tomato             9                          2/16/21                 F21Q2

123 Cafe            Cheese               4                         3/5/21                    F21Q2

123 Cafe            Cheese               6                         3/5/21                    F21Q2

123 Cafe            Cheese               2                         5/18/21                  F21Q3

123 Cafe            Cheese               3                         9/15/21                  F21Q4

ABC Market     Cheese               4                         2/3/21                    F21Q2

ABC Market     Lettuce               8                        2/3/21                    F21Q2

ABC Market     Lettuce              15                        2/3/21                   F21Q2

ABC Market    Tomato                2                        2/3/21                   F21Q2

ABC Market     Lettuce                1                        5/18/21                 F21Q3

ABC Market     Tomato               2                        5/18/21                 F21Q3

XYZ Hut            Lettuce               4                        2/16/21                F21Q2

XYZ Hut            Milk                     1                        2/16/21                F21Q2

XYZ Hut            Lettuce                2                       3/9/21                  F21Q2

XYZ Hut            Cheese                 3                       5/14/21                F21Q3

XYZ Hut            Tomato               2                        9/15/21                F21Q4

• This topic was modified 1 year, 11 months ago by Susan Bradley.
• This topic was modified 1 year, 11 months ago by b.

Reply | Quote

Replies

I saw this question earlier, but I thought that I’d leave it for 24 hours in case someone else came along to help, as I don’t have any experience with MS Access specifically. I do, however, have a lot of experience with SQL and databases in general, and MS Access largely implements SQL, as far as I’m aware, so here are the queries you want in SQL, as best as I can think to implement them:

Query 1:

SELECT [FY/Qtr],
COUNT(DISTINCT Store) as Store_count,
COUNT(DISTINCT Product) as Product_count
FROM table_name
GROUP BY [FY/Qtr]

Query 2:

SELECT [FY/Qtr],
Store,
Product,
COUNT(*) as Count
FROM table_name
GROUP BY [FY/Qtr],
Store,
Product

Query 3:

SELECT Product,
[FY/Qtr],
COUNT(DISTINCT Store) as Store_count
FROM table_name
GROUP BY Product,
[FY/Qtr]

The key to all of these queries is using GROUP BY. GROUP BY creates summary rows based on the specified columns, which can then be SELECTed from, both directly by column name and by using COUNT(), which does what you would expect.

You asked about DISTINCT – DISTINCT selects only different values, ignoring duplicates. This can be combined with COUNT, although I did want to check that this would work with MS Access, and it seems that Microsoft implements COUNT(DISTINCT) in a different way – click here to see how to convert it (I won’t do it myself because I don’t have access to Access).

As an aside, I note that one of your column names is FY/Qtr. Anything other than alphanumeric characters and underscores should be avoided if at all possible in database column names, so I suggest that you change it to FY_Qtr. You will note that, in the above queries, I have enclosed FY/Qtr in square brackets – this is to avoid problems and should be done if you are unable to change the column name.

A long post, but I do enjoy a coding challenge. I hope that this all helps!

Reply | Quote

Moderator apologies.  The Akismet spam filter clearly did not like your coding. I’ve unspammed everything, let me know which version(s) of the post you want me to keep and which one I can delete as duplicates.

Susan Bradley Patch Lady/Prudent patcher

1 user thanked author for this post.

Sky

Reply | Quote