Grouping in select query 1

[eadiem]

I have 3 tables I want to use in a query:

1. Concerned Parties (includes name, phone, etc.)
2. Complaint Records (includes ID, date, name of complainant, BusinessID complained about)
3. Business Addresses (includes BusinessID, Business Address, Region)

There are some complainants who make more than one complaint.
I want to know:
For each region, how many unique complainants called in 2004?
This is my query so far (using annoying design wizard so sorry it is messy).

SELECT Count([Concerned Parties].DBName) AS CountOfDBName, [Business Addresses].Region
FROM ([Complaint Records] INNER JOIN [Busness Addresses] ON [Complaint Records].[DBBusinessSite] = [Business Address].[ID String]) INNER JOIN [Concerned Parties] ON [Complaint Records].DBName = [Concerned Parties].DBName
WHERE ((([Complaint Records].DBDate)>#1/1/2004#))
GROUP BY [Business Addresses].Region;
[\code]
But this query only returns the number of complaints for each region, not the number of unique complainants.

Any ideas?

Thanks,
Eadie

 

[PHV]

You may try this:
SELECT Count(*) AS CountOfDBName, A.Region FROM (
SELECT DISTINCT CP.DBName, BA.Region
FROM ([Complaint Records] CR INNER JOIN [Business Addresses] BA ON CR.DBBusinessSite = BA.[ID String])
INNER JOIN [Concerned Parties] CP ON CR.DBName = CP.DBName
WHERE CR.DBDate>#1/1/2004#
) A
GROUP BY A.Region;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

This query ran without error but returned no results.

 

[PHV]

Does the embedded query returns something ?
SELECT DISTINCT CP.DBName, BA.Region
FROM ([Complaint Records] CR INNER JOIN [Business Addresses] BA ON CR.DBBusinessSite = BA.[ID String])
INNER JOIN [Concerned Parties] CP ON CR.DBName = CP.DBName
WHERE CR.DBDate>#1/1/2004#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

No, nothing

 

[PHV]

And the query in your original post returns something ?
I supposed mine was the same except the aggregate stuff, just adding the DISTINCT keyword.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

My original query returned something like:
RegionA 45
RegionB 23
RegionC 38
But these numbers are the total number of complaints in each region, not the number of complainants.

 

[PHV]

Does the following query returns something ?
SELECT Count(CP.DBName) AS CountOfDBName, BA.Region
FROM ([Complaint Records] CR INNER JOIN [Business Addresses] BA ON CR.DBBusinessSite = BA.[ID String])
INNER JOIN [Concerned Parties] CP ON CR.DBName = CP.DBName
WHERE CR.DBDate>#1/1/2004#
GROUP BY BA.Region;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

Nope...
thanks for your patience, appreciate the help

 

[PHV]

Nope...
So, please repost your EXACT original query, as the above is supposed to be the same ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

I had edited some field & table names for this post, perhaps i hadn't changed them back correctly. Here is the original:

SELECT Count([Concerned Parties].DBName) AS CountOfDBName, [Address LUT].Region
FROM ([Complaint Records] INNER JOIN [Address LUT] ON [Complaint Records].[DBFarm Site] = [Address LUT].[ID String]) INNER JOIN [Concerned Parties] ON [Complaint Records].DBName = [Concerned Parties].DBName
WHERE ((([Complaint Records].DBDate)>#1/1/2004#))
GROUP BY [Address LUT].Region;

 

[PHV]

What about this ?
SELECT Count(*) AS CountOfDBName, A.Region FROM (
SELECT DISTINCT [Concerned Parties].DBName, [Address LUT].Region
FROM ([Complaint Records] INNER JOIN [Address LUT] ON [Complaint Records].[DBFarm Site] = [Address LUT].[ID String]) INNER JOIN [Concerned Parties] ON [Complaint Records].DBName = [Concerned Parties].DBName
WHERE ((([Complaint Records].DBDate)>#1/1/2004#))
) A
GROUP BY A.Region;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

It is very weird...
Some regions return the correct number, but some do not. I can't compare all briefly because some regions have large numbers.
Here are some examples: RegionA has 4 complainants (total of 6 complaints), and the query returned 2 instead of 4. In RegionB, with only 1 complainant (total of 1 complaint), the query correctly returned 1. In RegionC, with 2 complainants (total of 2 complaints), the query correctly returned 2. In RegionD, with 3 complainants (total of 3 complaints) the query returned 2 instead of 3.
???

 

[PHV]

You have to know if the following query returns correct results:
SELECT DISTINCT [Concerned Parties].DBName, [Address LUT].Region
FROM ([Complaint Records] INNER JOIN [Address LUT] ON [Complaint Records].[DBFarm Site] = [Address LUT].[ID String]) INNER JOIN [Concerned Parties] ON [Complaint Records].DBName = [Concerned Parties].DBName
WHERE ((([Complaint Records].DBDate)>#1/1/2004#))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

No it does not.
In RegionA I should see 4 different names, I only see 2.

 

[PHV]

If you get rid of the DISTINCT keyword you will see what exactly your original query counted.
You have to check the WHERE clause and wether or not you need outer joins to get all the records you want.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

The joins I need are:

[Address LUT].[ID String] <- [Complaint Records].[Farm Site]
Where ID String is a string of the IDNumber, which is primary key of Address LUT
I need the Address LUT table because it contains the region of the business site.

[Concerned Parties].Name -> [Complaint Records].Name
Where Name is the primary key of Concerned Parties
I need the number of distinct names from the complaint records table for each region.

The WHERE clause only specifies that the complaint date is in 2004.

I don't know if I need outer joins...these were created by the design wizard.

 

[PHV]

the complaint date is in 2004
Replace this:
WHERE ((([Complaint Records].DBDate)>#1/1/2004#))
By this:
WHERE ((([Complaint Records].DBDate)>=#1/1/2004#))

Have you checked the results without the DISTINCT keyword ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eadiem]

Ok...changed date (although complaints are always in summer so didn't change results).

The query without the distinct keyword only returned 43 records. But there were 65 complaints in 2004.

 

[eadiem]

It seems that the ones that are being left off have something in common: they do not complain about a specific site, so [Complaint Records].[Farm Site] is not an ID that will match to the Address LUT table.