Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNT function if criteria matches (2002 SP3)

    I have a request run a report that shows the COUNT for every time criteria matches in a query. I've attached an incredibly stripped version of the db and an excel spreadsheet showing the criteria and what was requested (highlighted in pink). Basically when one code matches another, they would like the total number of occurances. I've never actually done a Count query where it needs to match criteria so am a little lost.

    Thanks in advance for any help!
    Pooja
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    1) It's impossible to do anything with the database since it contains only linked tables.

    2) I don't have the slightest idea what you want - please try to explain clearly what you want to accomplish.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Okay, let me try to describe it. If you look at the Excel spreadsheet I also attached, you'll see a VERY long list in two columns. They start off with Allocation Rate Code and Billing Rate Code on Row 18. Under that you'll see the start of the long column with 14 rows that contain WCA in Column A. Column B on the other hand has different codes such as WSA, WSB etc. What has been requested is that we run a report of some kind that shows the total Count when these two instances match in a query. So as you can see, when WCA=WSA, the total count for that is 2957. I can do this one at a time but that is going to take a very long time so thought I'd ask if there was a quicker way to do this.

    As background, the db is linked to our utility system and there is no way for me to extract data since it only grants read privileges for most circumstances. The Allocation Rate Code resides in a table called 'bif004' and shows the amount of water allocated to a specific type of residence. The Billing Rate Code resides in a table called 'bif005' and shows the size of the meter.

    What they would like to see is for example, all apartments with a 1" meter within the boundary etc.

    Hope that's enough info.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Does this do what you want? I have no way to test it.

    SELECT bif004.c_billcode, bif005.c_billcode, Count(*) AS Cnt
    FROM bif004, bif005
    GROUP BY bif004.c_billcode, bif005.c_billcode;

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Post deleted by pooja

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Create a new query in design view. Don't add any tables.
    Select View | SQL.
    Delete the text you see, then paste the SQL from my previous reply into the window.
    Switch to design view to see the design of the query, then switch to datasheet view to test if the query works, and if it does what you want.

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Thanks! That's just what I did! I'll post again after it's done (still running)...

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Didn't work unfortunately. I got an error code. Screen shot attached. Any other suggestions hopefully?
    Attached Images Attached Images

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    How many codes are there in each of the two tables in the real database?

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    Do you mean total? I have absolutely no idea (they are too numerous to count). If you mean simply on the billing and usage codes, billing has 14 and usage has 15.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    I fear that the number of records is simply too large to do this.

  12. #12
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNT function if criteria matches (2002 SP3)

    That's what I thought. Thank you for trying!

Posting Permissions

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