Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Post Unanswered: MTD, YTD on same Report in Access 97

    Can anybody help me how I can built a querry/report to display MTD and YTD data on the same report. Thank you

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what is the problem?
    creating MTD & YTD queries or getting the data into the report?

    izy

  3. #3
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Thanks, creating the query, after that I should not have a problem...


    Originally posted by izyrider
    what is the problem?
    creating MTD & YTD queries or getting the data into the report?

    izy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    date criteria in qryMTD with fields fldDate & fldNumber
    DatePart("M", [fldDate]) = DatePart("M", Now())

    date criteria for qryYTD with fields fldDate & fldNumber
    DatePart("YYYY", [fldDate]) = DatePart("YYYY", Now())


    ...and the simplest method to display on the report is to set up an unbound box with data = DSum("[fldNumber]", "qryMTD") and similarly for qryYTD

    izy

  5. #5
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126
    Thanks izy, I already started learning SQL language to put it together . It all works perfect!
    V

    Originally posted by izyrider
    date criteria in qryMTD with fields fldDate & fldNumber
    DatePart("M", [fldDate]) = DatePart("M", Now())

    date criteria for qryYTD with fields fldDate & fldNumber
    DatePart("YYYY", [fldDate]) = DatePart("YYYY", Now())


    ...and the simplest method to display on the report is to set up an unbound box with data = DSum("[fldNumber]", "qryMTD") and similarly for qryYTD

    izy

  6. #6
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    One more Q

    Is there a way to set the formula directly in the report. In other words I'm bulding formula that has MTD, YTD and many other criteria. How do I build that report for MTD, YTD without a query. Is there a way?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    $20 + $20 challenge

    my personal philosophy is to use queries wherever possible (...yes, even when the query is nothing more than * on a single table!!!).

    so: maybe you could get there from another route, but i'm not convinced it's worth thinking about.

    here comes the challenge:
    ------------------------------

    i'll mail a $20 bill to the first person to come up with a faster* method not using a query to solve your MTD/YTD question

    ...and a (second) $20 bill to the first person who comes up with a simpler* method (simpler = lower number of characters typed - no copy/paste - no select-from-list) to solve your MTD/YTD question

    ...and a "congratulations" e-mail to the first person who comes up with a method that takes _either_ less RAM _or_ less .MDE disk space (this challenge is at least possible!!).


    izy (willing to learn, willing to pay for learning)


    * - Access 2000 with no external assist, and excluding a Totals query to eliminate the DSum typing.
    Last edited by izyrider; 12-30-02 at 13:18.

Posting Permissions

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