Create a Field to Calculate Unique/Distinct Count

[MikeNewAccessUser]

Hi all! I'm an extremely new Microsoft Access user. I've created a new database that is linked to an Excel file. I have 2 tables of information similar to those listed below. In my query, I have a field for the unique descriptions and I want to add a field that will show the total store count of Unique or Distinct Descriptions. Based on the data below as an example, I would want the results of my query to be:



Description Store Count
Test Item A 5
Test Item B 1

Any thoughts on how this can be achieved? I appreciate and welcome any and all assistance. Thanks so much!

TBLdata Store # Item # Description 1 77400 Test Item A 2 77400 Test Item A 3 77400 Test Item A 3 128756087 Test Item A 4 77400 Test Item A 4 128756087 Test Item A 5 77400 Test Item A 1 55897 Test Item B 1 365476001 Test Item B
AND TBLiteminfo Item # Description 77400 Test Item A 128756087 Test Item A 55897 Test Item B 365476001 Test Item B

[June7]

Do a query that returns Distinct values then use that query in another query to do aggregate count.

Query1
SELECT DISTINCT [Test Item], [Store #] FROM TBLData;

Query2
SELECT [Test Item], Count([Store #]) AS CountStore FROM Query1 GROUP BY [Test Item];

As one nested query:
SELECT [Test Item], Count([Store #]) AS CountStore FROM (SELECT DISTINCT [Test Item], [Store #] FROM TBLData) AS Query1 GROUP BY [Test Item];


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[NTC]

You need to perform what is called an Aggregate Query - it is a feature in Access you can look up. In your lay out above it is showing the A and B on a separate line - - if that is the case with your excel sheet then you have a problem and need to get those onto the same line as their data.

[MikeNewAccessUser]

Great. Thanks June7!

Do a query that returns Distinct values then use that query in another query to do aggregate count.

Query1
SELECT DISTINCT [Test Item], [Store #] FROM TBLData;

Query2
SELECT [Test Item], Count([Store #]) AS CountStore FROM Query1 GROUP BY [Test Item];

As one nested query:
SELECT [Test Item], Count([Store #]) AS CountStore FROM (SELECT DISTINCT [Test Item], [Store #] FROM TBLData) AS Query1 GROUP BY [Test Item];


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.