Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using the Count Function to create a Report (97 SR1)

    I've asked this question here before, probably a bit differently, but I'm still having problems with this one! The response I received last month, didn't work.

    I need to create a report using the following fields:

    1. Venue - is a text field
    2. ServedDate - is a date field
    3. DismissedDate - is a date field.

    The report I am asked to create is:

    1. Total # Cases Pending as of the end of the month. Which would be all the ServedDate - DismissedDate

    2. # Cases Dismissed - Which would be DismissedDate is Not Null; and

    3. Total # cases Served during the month, which would be >#11/30/01#

    My problem is I can't seem to get these numbers unless I create separate queries for each instance. When I create a query with all three, the Count function does not work correctly. I don't get the correct # of cases, etc.

    Any suggestions?

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

    Re: Using the Count Function to create a Report (97 SR1)

    <hr>1. Total # Cases Pending as of the end of the month. Which would be all the ServedDate - DismissedDate<hr>
    This one has me confused. ServedDate - DismissedDate will give you the difference between the two dates. Is that what you're looking for? I would think you would want those where DismissedDate was Null or greater than the end of the period in question.

    Are you including the detail of the items in this report or only a summary? What exactly do you mean by "create a report"--do you want help with the query behind a report or what? Without an active link to your previous thread on this question, we don't have any of the background and so you'll have to fill it in again.
    Charlotte

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using the Count Function to create a Report (97 SR1)

    I should have said that Total cases pending at the end of the month would be the # of cases where DismissedDate is null. I guess I need help with the query, not the report. Can I create one query, which will show the Count of all 3 of my requests? I don't need the detail, just the summary. Thanks. I think I'm confusing myself with this one.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using the Count Function to create a Report (97 SR1)

    Nadia,

    You could make a query with the following expressions:
    IIf(IsNull(DismissedDate),0,1)
    IIf(IsNull(ServedDate),0,1)
    Put a date condition, and sum the columns.

    Hope this help
    Francois

Posting Permissions

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