Unique Counts Query

[msk7777]

Hello everyone,

I have this query:

 INSERT INTO [DN11 (AP) County Counts] ( [Data Month], [Data Year], State, County, [General Dentist], Endodontist, Orthodontist, [Oral Surgeon], [Pediatric Dentist], Periodontist, Prosthodontist, [Total Providers] ) IN '\\cifs04\NetworkDevelopment\Provider File Databases\Network Stats.accdb'
SELECT [DN11 TABLE (Access Points)].[Data Month], [DN11 TABLE (Access Points)].[Data Year], [DN11 TABLE (Access Points)].State, [DN11 TABLE (Access Points)].County, Count(IIf([Specialty]="General Dentist",1)) AS [General Dentist], Count(IIf([Specialty]="Endodontist",1)) AS Endodontist, Count(IIf([Specialty]="Orthodontist",1)) AS Orthodontist, Count(IIf([Specialty]="Oral Surgeon",1)) AS [Oral Surgeon], Count(IIf([Specialty]="Pediatric Dentist",1)) AS [Pediatric Dentist], Count(IIf([Specialty]="Periodontist",1)) AS Periodontist, Count(IIf([Specialty]="Prosthodontist",1)) AS Prosthodontist, Count([DN11 TABLE (Access Points)].County) AS [Total Providers]
FROM [DN11 TABLE (Access Points)]
GROUP BY [DN11 TABLE (Access Points)].[Data Month], [DN11 TABLE (Access Points)].[Data Year], [DN11 TABLE (Access Points)].State, [DN11 TABLE (Access Points)].County;

There is another field in the same table ([DN11 TABLE (Access Points)].Provider ID) that stores an ID number. I need to adjust the above query to do the same counts except now it needs to be based on unique ID's (or values) from the [DN11 TABLE (Access Points)].Provider ID field.

Any help would be appreciated!

Msk7777

 

[vbaInet]

Please refer to this link regarding saving calculated values:

http://allenbrowne.com/casu-14.html

 

[msk7777]

Thank you for responding! I apologize as I may not have made my need clear.

The above query currently counts the different types of providers in the table by county. In the table the query is pulling from there is another field that has a provider ID number. This ID is unique to each provider, however, can be listed multiple times depending on how many offices the provider has.

I need to adapt the above query to give me the same provider counts except add the criteria of being based off unique provider ID's within the [Provider ID] field.

I hope that better explains what I am needing to achieve. Thanks in advance!

msk7777

 

[vbaInet]

I pointed to that link because you're storing calculated values. These values can be calculated whenever you need them. Show some sample data of the before and after in a spreadsheet.

 

[msk7777]

I pointed to that link because you're storing calculated values. These values can be calculated whenever you need them. Show some sample data of the before and after in a spreadsheet.

I apologize but the link you sent me is about calculated values (my current query already calculates the values perfectly) and nothing I read on the page helps me. And you are correct, it does store them, but not in this database, it appends the results to another database where are stats are stored.

I am simply trying to adjust the current query to do exactly what it is doing except add a criteria of the [Provider ID] field being unique values. From what I have read up on what I am trying to achieve, I believe I need to just add a DISTINCT statement to my query, however, my attempts to do this have failed.

 

[vbaInet]

That's why I requested some sample data. Show me the before (i.e. the current data) and the after (what you would like to achieve).

 

[msk7777]

View attachment Sample1.xls

I have attached a sample of the current query results. The results I would like it to look like would be the same look but the values would be less since I need it to just pull unique values based off the [Provider ID] field.

Sorry to be a pain and thanks for you continued assistance!

Msk7777

 

[vbaInet]

You're not being a pain, I'm just trying to understand your requirement. Doing it is easy once I know what you're trying to achieve.

The data you provided doesn't include the ProviderID field. Can you include sample data with that field so I can see the duplicate records.

Fyi, this is how you use DISTINCT

SELECT DISTINCT [DN11 TABLE (Access Points)]... etc

 

[msk7777]

View attachment Sample2.xls

Thanks again! Attached is a sample of the table data. There are more fields but I can't share the data and since the query doesn't pull those fields it shouldn't matter.

Let me know what you think!

Msk7777

 

[vbaInet]

Using the following fictitious data, you want to go from this:

Provider ID	Specialty
-----------	---------------
108633		ORAL SURGEON
108633		ORAL SURGEON
108633		ORAL SURGEON
108633		GENERAL DENTIST
108633		GENERAL DENTIST
108633		GENERAL DENTIST
108633		ORTHODONTIST
108633		GENERAL DENTIST
108633		GENERAL DENTIST

to this:

Provider ID	CountGeneralDentist	CountOralSurgeon	CountOrthodontist
-----------	-------------------	----------------	-----------------
108633			5			3			1

?

 

[msk7777]

View attachment Sample3.xls

Your example is almost what I am looking for. See attached.

Perfect scenario would be that since this is a county search query, it would look at the Provider ID, County and State fields and make sure that the provider ID is only listed once per county. (also, the Provider ID field doesn't need to be displayed but can if necessary).

Our current problem is that I had previously taken this table and appended it to another table and set the Provider ID field to not accept duplicates so that only unique provider ID's were pulled from queries.

The issue with this is that when a client asks for Unique Providers by County (for example) we are giving numbers that aren't correct because the table removed all instances of the Provider ID except for 1. If a provider has multiple locations throughout several counties then they aren't being accounted for.

So I have been charged with resolving this issue and am finding it to be harder than I initially anticipated.

Hope that makes sense!

 

[vbaInet]

Ok, that's a much clearer example. It's the sort of example we hope to receive when we ask for one.

The first thing you need to do is create a SELECT query that returns unique records. You can do this by selecting Yes in the Unique Values property in the Property Sheet of the query.

 

[msk7777]

Maybe I did something incorrectly, but I tried that initially and it removed all additional provider id's regardless of whether the id was in a different county or not.

Maybe I did something wrong?

 

[vbaInet]

Going by the sample data you uploaded, you don't need the Provider ID present. You're grouping by County and State.

 

[msk7777]

Ok in the original query ( I changed it from an append to a select query), also the grouping already makes it show the state and county only once.

SELECT [DN11 TABLE (Access Points)].[Data Month], [DN11 TABLE (Access Points)].[Data Year], [DN11 TABLE (Access Points)].State, [DN11 TABLE (Access Points)].County, Count(IIf([Specialty]="General Dentist",1)) AS [General Dentist], Count(IIf([Specialty]="Endodontist",1)) AS Endodontist, Count(IIf([Specialty]="Orthodontist",1)) AS Orthodontist, Count(IIf([Specialty]="Oral Surgeon",1)) AS [Oral Surgeon], Count(IIf([Specialty]="Pediatric Dentist",1)) AS [Pediatric Dentist], Count(IIf([Specialty]="Periodontist",1)) AS Periodontist, Count(IIf([Specialty]="Prosthodontist",1)) AS Prosthodontist, Count([DN11 TABLE (Access Points)].County) AS [Total Providers]
FROM [DN11 TABLE (Access Points)]
GROUP BY [DN11 TABLE (Access Points)].[Data Month], [DN11 TABLE (Access Points)].[Data Year], [DN11 TABLE (Access Points)].State, [DN11 TABLE (Access Points)].County;

I then changed the properties to unique records:

 SELECT DISTINCTROW [DN11 TABLE (Access Points)].[Data Month], [DN11 TABLE (Access Points)].[Data Year], [DN11 TABLE (Access Points)].State, [DN11 TABLE (Access Points)].County, Count(IIf([Specialty]="General Dentist",1)) AS [General Dentist], Count(IIf([Specialty]="Endodontist",1)) AS Endodontist, Count(IIf([Specialty]="Orthodontist",1)) AS Orthodontist, Count(IIf([Specialty]="Oral Surgeon",1)) AS [Oral Surgeon], Count(IIf([Specialty]="Pediatric Dentist",1)) AS [Pediatric Dentist], Count(IIf([Specialty]="Periodontist",1)) AS Periodontist, Count(IIf([Specialty]="Prosthodontist",1)) AS Prosthodontist, Count([DN11 TABLE (Access Points)].County) AS [Total Providers]
FROM [DN11 TABLE (Access Points)]
GROUP BY [DN11 TABLE (Access Points)].[Data Month], [DN11 TABLE (Access Points)].[Data Year], [DN11 TABLE (Access Points)].State, [DN11 TABLE (Access Points)].County;

I tallied the results from both queries and both were identical.

 

[vbaInet]

The first thing you need to do is create a SELECT query that returns unique records. You can do this by selecting Yes in the Unique Values property in the Property Sheet of the query.

You misread this post.

So, create a new SELECT query - it won't have any grouping or summation - and get your unique records in that query. This is just the first step. There are more steps to follow.

 

[msk7777]

Ahhh. My mistake. Ok got it done. I created a new query and placed [Provider ID], [County], [State] & [Specialty]. I then changed properties for the query to Unique Values.

The results look good so far. I see that the provider ID's are listed multiple time but are unique to each county, good start!

 

[vbaInet]

Good! Now, I can jump to the final result but I think it's worthwhile you do the next step. Hit the Totals button and perform a count of the records grouped by County and State. You shouldn't have the Provider ID field in the query.

 

[msk7777]

I think I got it. I adjusted the original query to pull from the new query and its doing what I needed it to do. Is that where you were going?

 

[msk7777]

Sorry looks like we posted at the same time. Yea it looks like I did what you were going to suggest. Awesome! Works like a charm.

Thanks for all the patience and help!