Results 1 to 5 of 5
  1. #1
    Datamulcher is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26

    How to add a COUNTIF type field to a select query?

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yike. Think I'll ask a simple question first. Did you try a TOTALS query where you group by lat or whatever? The query wizard can help you create one.
    What's with the ?'s. What single character would you expect to find in its place?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Datamulcher is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    Haha. No, I haven't used the total function. The query is so large that I can't open it in design view- SQL only... The single char ?'s are for "," mainly with the "*" for Postal Codes. E.g. Like "?Leduc? AB*" would be for formats including " Leduc, AB, T5E". This query was emailed to me in SQL created by a colleague who is away from the office. My best idea so far is to export the datasheet into a table, maybe then run a TOTALS?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Looks like something other than Access SQL - like SQL*Plus or maybe T-SQL. You can use a query as a table for another query, but I don't know what to expect if you can't open that in design view. Maybe the columns would not be available. It appears there are only 6 columns involved, so my guess is because it's not native Access SQL.
    My best idea so far is to export the datasheet into a table...
    Maybe link the spreadsheet to Access as a table and try a Totals query (not function) against it. Given the number of columns you have, I'm thinking you won't be happy with the groupings of such a query - you may need a CROSSTAB query instead. Then again, Excel is better than Access for aggregate functions and pivots, so why not just use Excel?

  5. #5
    Datamulcher is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2016
    Location
    Victoria, Canada
    Posts
    26
    Thanks, I concur. I did it with a linked table (excel spreadsheet). It should be Access SQL, I think its just the maniacal application of criteria that makes it such a behemoth

Please reply to this thread with any new information or opinions.

Similar Threads

  1. OVER PARTITION type query, ranking, select top 10
    By kagoodwin13 in forum Queries
    Replies: 2
    Last Post: 02-10-2016, 03:35 AM
  2. Replies: 7
    Last Post: 05-28-2015, 11:43 AM
  3. Replies: 2
    Last Post: 05-21-2014, 11:10 AM
  4. Advanced CountIf Query
    By therzakid in forum Queries
    Replies: 2
    Last Post: 07-27-2011, 10:45 PM
  5. Overflow error - change field type in query?
    By Lady_Jane in forum Queries
    Replies: 6
    Last Post: 05-25-2011, 01:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums