I am looking at the geocoding of locations, many of which have the same lat/long. I need to add a column that shows a count of that value.
This type of deal:
Value_ Count_
Fish 3
Fish 3
Fish 3
Apple 1
Cheese 2
Cheese 2
The query itself looks like this (if you REALLY want to know):
SELECT PersonID,P34_Loc1GeocodedText, Loc1LatLong, P26_Loc1Workplace, P25_Loc1EmployerName FROM 02_Persons
WHERE ((([02_Persons].AGEO_FIRST) Like "*Austr*" Or ([02_Persons].AGEO_FIRST) Like "?Edmonton? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Sherwood Park? AB*" Or ([02_Persons].AGEO_FIRST) Like "?St Albert? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Spruce Grove? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Fort Saskatchewan? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Leduc? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Morinville? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Stony Plain? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Jasper Ave NW? Edmonton? AB? Canada*" Or ([02_Persons].AGEO_FIRST) Like "?Alberta? Canada*" Or ([02_Persons].AGEO_FIRST) Like "?Beaumont? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Nisku? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Sherwood Dr? Sherwood Park*" Or ([02_Persons].AGEO_FIRST) Like "?Gibbons? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Saint Albert Trail? St. Albert? AB? Canada*" Or ([02_Persons].AGEO_FIRST) Like "?Box*" Or ([02_Persons].AGEO_FIRST) Like "?Devon? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Hinton? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Edson? AB*" Or ([02_Persons].AGEO_FIRST) Like "Belgravia? Edmonton? AB? Canada *" Or ([02_Persons].AGEO_FIRST) Like "Strathcona? Edmonton? AB? Canada*" Or ([02_Persons].AGEO_FIRST) Like "PO B*" Or ([02_Persons].AGEO_FIRST) Like "Leduc County*" Or ([02_Persons].AGEO_FIRST) Like "Hebert Rd? St Albert? AB *" Or ([02_Persons].AGEO_FIRST) Like "?Bon Accord? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Edmonton? 10303 Jasper Ave NW? Edmonton? AB T5J? Canada*" Or ([02_Persons].AGEO_FIRST) Like "?Division No. 11? AB*" Or ([02_Persons].AGEO_FIRST) Like "?Groat Rd NW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "?Baseline Rd? Sherwood Park*" Or ([02_Persons].AGEO_FIRST) Like "?Broadmoor Blvd? Sherwood Park*" Or ([02_Persons].AGEO_FIRST) Like "?Calgary Trail NW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "?Capital Line? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "Ellerslie Rd SW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "Fort Rd NW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "Division No. 11? AB*" Or ([02_Persons].AGEO_FIRST) Like "Groat Rd NW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "?Baseline Rd? Sherwood Park*" Or ([02_Persons].AGEO_FIRST) Like "?Broadmoor Blvd? Sherwood Park*" Or ([02_Persons].AGEO_FIRST) Like "?Calgary Trail NW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "?Capital Line? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "?Ellerslie Rd SW? Edmonton*" Or ([02_Persons].AGEO_FIRST) Like "?Fort Rd NW? Edmonton*")) OR (((([02_Persons].AGEO_FIRST) Like "?### Ave [!&][!&][!&][!&][!&][!&][!&]*" Or ([02_Persons].AGEO_FIRST) Like "?### st [!&][!&][!&][!&][!&][!&][!&]*" Or ([02_Persons].AGEO_FIRST) Like "?## Ave [!&][!&][!&][!&][!&][!&][!&]*" Or ([02_Persons].AGEO_FIRST) Like "?## st [!&][!&][!&][!&][!&][!&][!&]*") AND (([02_Persons].AGEO_FIRST) Not Like "?### st Albert*")));
I know its cumbersome, its the only way to do this task. I just want to add a count to the output as I would in Excel.
=COUNTIF(C$1:C$2000,C2)
gives me:
Loc1LatLong P26_Loc1Workplace P25_Loc1EmployerName Count 53.544389, -113.4909267 downtown TD Bank 306
Ideas??
Thanks