Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Headache? Take Anvil. (97 SR2)

    That's right, Anvil, or a healthy dose of Woody's Lounge... good for me I prefer the latter.

    Here's the scenario. I've got a list of 3,000 projects. Each project is assigned a status, Complete, Pending, or Unknown.

    here's where it gets hairy... Read carefully... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    I want the query to return the values for each of the seven days in the week in the first column, as dates. So the first 'row' would show todays date, the second would show yesterdays date, etc.. until we get to this point last week.

    Between Date()-1 and Date()-8 Simple enough, right?

    Now I want the second column to display the total entered projects for that day.

    So far, my query in SQL view looks like this:

    <pre> SELECT tblprojectmain.datestarted, Count(tblprojectmain.datestarted) as Started
    From tblprojectmain
    GROUP BY tblprojectmain.datestarted
    HAVING (((tblprojectmain.datestarted) Between Date()-1 And Date()-8;</pre>


    This gives me a neat list of the dates, along with the number of entries made on each day.

    Now I want to add the count of those pending based on the [pending] field, and a count of those complete, based on a [complete] field.

    Please help, I don't want to invest in an Anvil at this point... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Thanks!
    Attached Images Attached Images
    • File Type: jpg x.jpg (12.0 KB, 0 views)
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

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

    Re: Query Headache? Take Anvil. (97 SR2)

    I assume the Anvil is for beating your head on? <img src=/S/grin.gif border=0 alt=grin width=15 height=15> If you're actually using separate fields, start shopping for anvils. If you're simply putting your pending, complete or unknown value in a single status field, you're home free.

    If you're using a status field, all you have to do is add an expression like Abs(Sum([Status]="Pending")). That will give you give you the sum of the True values returned for rows where the Status has been set to Pending. You can do the same thing for the other two values, and you'll have your 3 status values in the query.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Headache? Take Anvil. (97 SR2)

    Anvil. Stronger than Pain.

    That worked like a charm. Thanks Millions Charlotte! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Attached Images Attached Images
    • File Type: jpg x.jpg (4.9 KB, 0 views)
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Posting Permissions

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