Count Matching records

[Justin Larson]

For clarity, I will try to be specific. Hopefully it will not come out long
winded.

I have a query drawing from several tables. Here's a sample of data (madeup):

RetailChain Store# ZipCode TotalScore Provider ProviderCount
Ace 1 98654 5500 ProviderA 3
Ace 1 98654 5500 ProviderB 3
Ace 1 98654 5500 ProviderC 3
Ace 1 98765 2100 ProviderA 2
Ace 1 98765 2100 ProviderB 2
Ace 1 98423 1500 ProviderA 1
Ace 2 87654 2000 ProviderA 1
Ace 2 87321 1400 ProviderA 2
Ace 2 87321 1400 ProviderB 2

the first two field together represent one retail location. The zip field
represents zip codes that people are driving from to shop at said retailer,
so there can be more than one zip per retailer. The score represents the
count of people driving from the zip code to the retail location.

Here's my problem. The provider represents providers that exist in each zip
code. There may be one or more providers for a zip code. The ProviderCount
field is what I'm having trouble with. The sample above is what I want it to
do, but I can't get it to work. I need the value in ProviderCount to be the
total number of providers related to the Zip Code. This count will be used
for further calculation, probably in other queries, or possibly a report, but
I can't seem to find a way to count the number of providers outside the
query.

Alternatively, in my other tables, I do have unique values for each
combination of Retailer/Zip/Score - so I could count the number of providers
related to each zipscore based on the zipcode. The difficulty has been
counting records inside the query, based on criteria inside one record.

The DCount function is the closest I've found, but I don't have a static
criteria, the criteria is the value of the ZipCode field in each record.

Does this make sense?

 

[Justin Larson]

In the style of me, I answered my own question about 10 seconds after posting
this.

I made a second query that does nothing but count # of providers per
zipcode, then used that field in my new query. Done.

I knew it should be simple.

-Liver

 

[KARL DEWEY]

Try this --
qryProviderCount --
SELECT ZipCode, Count([ZipCode]) AS ProviderCount
FROM YourTable
GROUP BY ProviderCount
WHERE ....same criteria as your other query...;

SELECT [RetailChain], [Store#], [ZipCode], [TotalScore], [Provider],
ProviderCount
FROM YourTable INNER JOIN qryProviderCount ON YourTable.ZipCode =
qryProviderCount.ZipCode
WHERE ....same criteria as before ...;