Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Question Unanswered: Please advise... New to Access. Needs help with Occurences

    I am working on a project that needs me to count occurences. I am not sure how to do so. I tried DCount but cannot get it to work. Below is a sample of what I am trying to do.

    Query

    Zip Returns
    11111 52
    22222 100
    33333 45
    11111 100
    11111 50
    22222 28
    33333 26

    I need to know how many times (occurences) each zipcode has had >0 and <50 returns

    the end result would look like this:

    Zip Returns >0 and <50
    11111 2
    22222 1
    33333 2

    I think I need to do an expression but am not certain.

    Like I said, this is a sample table of what I am trying to do. The real table has 222000 zipcodes, many repeated.

    Please advise!!!! also, please make it as simple as possible. I am not very familier with access.

    Thank you

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You're example is incorrect (partially) ... the RC 11111 should have 0 hits ... If a hit/occurance is >0 and <50 ... This is a simple SQL statement:

    SELECT ZipCode, SUM(IIF(Returns>0 AND Returns<50,1,0)) AS Occurance FROM WhatEverTable
    GROUP BY ZipCode

    Make is a stored query or SQL in VBA code, take your pick ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    M Owen,

    I owe you!!!! It worked perfectly!!! Thanks for making me the bosses hero....

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by ANYOD
    M Owen,

    I owe you!!!! It worked perfectly!!! Thanks for making me the bosses hero....
    You're welcome ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ANYOD
    M Owen,

    I owe you!!!! It worked perfectly!!! Thanks for making me the bosses hero....
    Not sure I like your use of taking the credit, but I laugh at the "bosses hero"... Just picture it for a moment
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •