Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoiding Excessive Query Results (Access 2000)

    I have a peculiar problem. I use the following query and receive the following result

    QUERY:

    SELECT dbo_PMSTAT.PHONE_NUMBER, count(dbo_PMSTAT.fe_f_cv) AS CountOfFE_F_CV, count(dbo_PMSTAT.fe_i_cv) AS CountOfFE_I_CV
    FROM dbo_PMSTAT
    WHERE ((dbo_PMSTAT.TIME)>#5/19/2005#) and (((dbo_PMSTAT.FE_F_CV)>500) OR ((dbo_PMSTAT.FE_I_CV)>500))
    GROUP BY dbo_PMSTAT.PHONE_NUMBER
    ORDER BY dbo_PMSTAT.PHONE_NUMBER DESC;

    RESULT:

    PHONE_NUMBER CountOfFE_F_CV CountOfFE_I_CV
    170-XXX-9760 58 58
    070-XXX-9305 22 22
    670-YYY-9032 1 1
    804-ZZZ-0219 11 11
    004-ZZZ-3210 59 59

    As you can see, I get the same numbers for the counts under each column. I KNOW this is incorrect. The proper result should be something like:

    PHONE_NUMBER CountOfFE_F_CV CountOfFE_I_CV
    170-XXX-9760 58 8
    070-XXX-9305 22 0
    670-YYY-9032 1 12
    804-ZZZ-0219 0 11
    004-ZZZ-3210 59 4

    What's wrong with my query? Can any of you help? THANKS IN ADVANCE!

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

    Re: Avoiding Excessive Query Results (Access 2000)

    Welcome to Woody's Lounge!

    The Count function in a Totals query counts the number of non-null values in the specified field. If FE_F_CV and FE_I_CV contain non-null values, both Count fields only return the number of records within each group.

    If you want to count how many records there are with FE_F_CV > 500 and also how many records there are with FE_I_CV > 500, you must create two separate queries, and combine the results in a third one.

    If that doesn't do what you want, could you explain what you want to accomplish?

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Excessive Query Results (Access 2000)

    Thank you for responding, but...I know about two different queries. I wanted to do it in one. I thought a UNION would do the job, but it did not.

    Any advice is appreciated.

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

    Re: Avoiding Excessive Query Results (Access 2000)

    I'm sorry, I don't think you can do this in one query.

Posting Permissions

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