Duplicates query assistance

[Michael Abrams]

I have a table called UPDATED PCP LIST, that contains a listing of PCPs by PCP Number, PCP Name and PCP Location.

The other table is COMBINED CURRENT MONTH MEMBER COUNT that has a PCP Number and PCP Name.

I successfully obtain the counts of duplicates by location except where the PCP # = 99999999 and the PCP Name = *** UNASSIGNED ***

Code: Select all

SELECT [UPDATED PCP LIST].LOCATION, Count([COMBINED CURRENT MONTH MEMBER COUNT].[PCP NAME]) AS [CountOfPCP Name]
FROM [UPDATED PCP LIST] INNER JOIN [COMBINED CURRENT MONTH MEMBER COUNT] ON [UPDATED PCP LIST].[PCP NUMBER] = [COMBINED CURRENT MONTH MEMBER COUNT].[PCP NUMBER]
WHERE ((([COMBINED CURRENT MONTH MEMBER COUNT].[PCP NAME])>="1"))
GROUP BY [UPDATED PCP LIST].LOCATION
ORDER BY [UPDATED PCP LIST].LOCATION;

How can I modify this code to include the count of PCP *** UNASSIGNED *** ?

Thanks !

Michael Abrams

[HansV]

I don't understand the condition

WHERE ((([COMBINED CURRENT MONTH MEMBER COUNT].[PCP NAME])>="1"))

This selects names that are lexicographically greater than or equal to the string "1". This will include all "real" names that begin with a letter, and will only exclude names that begin with characters such as # or $.

Don't you want the following?

SELECT [UPDATED PCP LIST].LOCATION, Count([COMBINED CURRENT MONTH MEMBER COUNT].[PCP NAME]) AS [CountOfPCP Name]
FROM [UPDATED PCP LIST] INNER JOIN [COMBINED CURRENT MONTH MEMBER COUNT] ON [UPDATED PCP LIST].[PCP NUMBER] = [COMBINED CURRENT MONTH MEMBER COUNT].[PCP NUMBER]
GROUP BY [UPDATED PCP LIST].LOCATION
HAVING Count([COMBINED CURRENT MONTH MEMBER COUNT].[PCP NAME])>1

[Michael Abrams]

The query was inherited, & worked except for the **UNASSIGNED** which I just used a work-around and added it
to the proper Location.

Of course your modification works exactly as it is supposed to.

Once again Hans, thank you for your help (and the invite to this board).

It is much appreciated.

Michael Abrams