Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Question (2k)

    I have a table of data that contains an End of the Week Date field, and a Doc Type field. The doc type field has 3 possibilities, DDC, EO, and ND. I can make a query that counts the number of each doc type per week, and I can find the sum of all the doc types over all the weeks (total DDC, total EO, total ND). but i also want a field that does a cumulative sum in another column. But i can't figure out how to get the query to do this using the built in functions. does anyone have any suggestions on how to find the cumulative value as you go progress through the weeks?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (2k)

    Hans, I tried that but when i put it in i get "the expression you entered has an invalid date value" error. any idea why? my table's dates are all fine
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Query Question (2k)

    Is the date field really a date/time field?
    Do you have records with a blank date?

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (2k)

    Yes and No, only 10 records all with real dates
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (2k)

    heres the basic database table im trying to run this query from
    Attached Files Attached Files
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Query Question (2k)

    Edited by HansV to add omitted quotes.

    You can use the DCount function for this. Say that your table is named tblData, and that the actual names of the fields you mention are EndOfWeek and DocType. If you want a cumulative count of all records up to and including a specific week, use

    <code>CumulativeCount: DCount("*", "tblData", "[EndOfWeek] <= #" & [EndOfWeek] & "#")</code>

    If you want a cumulative count for a specific doc type, expand this to

    <code>CumulativeCount: DCount("*", "tblData", "[EndOfWeek] <= #" & [EndOfWeek] & "# AND [DocType] = 'DDC'")</code>

    Note: the browser may mess up the "less than or equal to", it is a < followed by = without a space in between.

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

    Re: Query Question (2k)

    I see now that I omitted quotes at the beginning of the third argument of the DCount function. Sorry about that. I have corrected my earlier reply, and attached the database with a working (for me) query.

    Note: I used the Format function for the date value; this is necessary for me since I use a non-US date format. It shouldn't be necessary for you, but it won't do any harm either.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (2k)

    thanks Hans, i think i can make that work for me.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (2k)

    Hans, the values that output in the query come out as text, is there any way to keep them as a number? or change their format to number?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Query Question (2k)

    That is a peculiarity of the statistical domain functions DCount etc. You can use Val to convert the result to a number:

    CumulativeCount: Val(DCount(...,...,...))

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (2k)

    thanks hans [img]/forums/images/smilies/biggrin.gif[/img]
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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