Results 1 to 8 of 8
  1. #1
    drjabear
    Guest

    Count Function Returning Zero in Query

    I am using Access 2000. My information flows througout my DB as follows.
    1. Source Table 2. Count Append Query to a master table.

    Often my Count Append Query counts zero records and returns no append record for output. I need the query to output a count of 0 instead of nothing at all. I am sure there is an easy solution. Does anyone have any suggetions??

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Count Function Returning Zero in Query

    Hi,
    Can you post the SQL of your query?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Function Returning Zero in Query

    Maybe you could use the NZ() function. It converts a null value to Zero (or any other specified value). You can place it in your query as follows:

    Field1: NZ([FieldName])
    OR
    Field1: NZ([FieldName],0)

    The on-line help in Access will give you more information about the syntax and arguments.

    HTH[img]/w3timages/icons/thinks.gif[/img]
    -MarkJ-

  4. #4
    drjabear
    Guest

    Re: Count Function Returning Zero in Query

    Thanks for the help. I will try the NZ function and let you know how that works.

    Jason

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Function Returning Zero in Query

    I second Rory's motion that you post the SQL. If there are no records to count, Nz probably won't do the trick for you, since it works on a null field, but not if there is no record at all.
    Charlotte

  6. #6
    drjabear
    Guest

    Re: Count Function Returning Zero in Query

    Here is the SQL. Thanks!!

    SELECT [S-A07].BrokerProvNum AS Region, Count([S-A07].RecipientMedicaidID) AS [MCaid ID Count]
    FROM [S-A07]
    WHERE ((([S-A07].RecipientMedicaidID) Is Null Or ([S-A07].RecipientMedicaidID)="0"))
    GROUP BY [S-A07].BrokerProvNum;

    Any help is apprecitated!!

  7. #7
    drjabear
    Guest

    Re: Count Function Returning Zero in Query

    Rory,
    I have posted the SQL out on the message board. Please take a look at it on your next convenience.
    Thanks,
    Jason

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Function Returning Zero in Query

    This is a single table query so joins aren't the issue. Is it possible to have a record with a BrokerProvNum but no RecipientMedicaidID? If not, you'll never get any records returned unless there's one with a RecipientMedicaidID of zero. If you want to return zero counts for all BrokerProvNum values that don't have an valid RecipientMedicaidID, you can do it with a join to a table that will return every BrokerProvNum, assuming you have such a thing. For example, is your BrokerProvNum is actually a field from a tblBrokerRegion, you could change your query to this:

    SELECT tblBrokerRegion.BrokerProvNum AS Region, Count([S-A07].RecipientMedicaidID) AS [MCaid ID Count]
    FROM tblBrokerRegion LEFT JOIN [S-A07] ON tblBrokerRegion.BrokerProvNum = [S-A07].BrokerProvNum
    WHERE ((([S-A07].RecipientMedicaidID) Is Null Or ([S-A07].RecipientMedicaidID)="0"))
    GROUP BY tblBrokerRegion.BrokerProvNum

    That will give you all the BrokerProvNum values, which you're filtering down to those that have a ReciptientMedicaidID value of 0 or that have no related records.
    Charlotte

Posting Permissions

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