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

 

[eadiem]

Which unfortunately means the Address LUT cannot be used to determine region.
Thanks for your help, I will have to rethink this whole thing.

 

[PHV]

You have to discover why.
Does all the Complaint Records have a valid DBFarm Site referenced in Address LUT ?
Does all the Complaint Records have a valid DBName referenced in Concerned Parties ?
BTW, I don't think that you need the Concerned Parties table.
What about this ?
SELECT Count(*) AS CountOfDBName, A.Region FROM (
SELECT DISTINCT [Complaint Records].DBName, [Address LUT].Region
FROM [Complaint Records] INNER JOIN [Address LUT] ON [Complaint Records].[DBFarm Site] = [Address LUT].[ID String]
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

 

[PHV]

You posted while I wrote my reply.
You may try an OUTER JOIN like this:
SELECT Count(*) As CountOfDBName, A.Site As Region FROM (
SELECT DISTINCT [Complaint Records].DBName, Nz([Address LUT].Region, "No site") As Site
FROM [Complaint Records] LEFT JOIN [Address LUT] ON [Complaint Records].[DBFarm Site] = [Address LUT].[ID String]
WHERE [Complaint Records].DBDate>=#1/1/2004#
) A
GROUP BY A.Site;

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

 

[eadiem]

Yikes...I hate to take up so much of your time with this but it seems like it is almost done...

No there is not always a valid DBFarm Site, sometimes it is "unidentified" or "around Main St & Elm Rd".

The DBName reference is always valid.

The query you posted above returns only those 43 records with valid Farm Sites.

Instead of using the Address LUT to get the region, I could also use the complaint ID. The complaint ID is set up like this:

RegionCode-Year-ComplaintID

So the 25th complaint of 2004, which is in RegionA, would have an ID of A-04-025.

So I am now trying to use this to determine the region of the complaint.

 

[PHV]

Something like this ?
SELECT Count(*) AS CountOfDBName, A.Region FROM (
SELECT DISTINCT DBName, Left([complaint ID], 1) As Region
FROM [Complaint Records]
WHERE 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]

Perfect!!! You are a genius, thank you!!
I checked to make sure all 65 records were returned without the DISTINCT, and it's exactly right. Thanks for your patience.