Results 1 to 5 of 5

Thread: Year-to-date

  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Unhappy Unanswered: Year-to-date

    I am trying to insert a month-to-date and year-to-date function in my database.

    It is for figuring how many times a particular action occurred in the month and the past year.

    I have tried everything I can think of and I get nothing!
    Last edited by tashspicer; 09-07-04 at 17:01.

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    What do you mean by "a particular action"? More details on what the function is to calculate, please. Do you know about the criteria "between"?

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    Let me post a hypothetical situation and see if this is reasonably close to what you're trying to do.

    Let's say you have an EventOccurrences table which has two columns, EventID (an integer) and DateOccurred (a Date field). You want to know how often each event occurred in the last .. year.

    (1) Make a new query for EventOccurrences. Add EventID as a column, and =DatePart("y",EventID), a calculated-field, as the second column. {Caution: extemporaneous posting.. I'm doin' this from memory. Fix "tpyos" as necessary.} If you run this query you'll see a bunch of event-IDs and years.

    (2) Now press the "Sigma" (a funny-looking "E") on the toolbar. A new row appears with "Group By" in every field.

    (3) Under EventID change this to "Count."

    Run the query and, magically, you have the total number of events that occurred that year, for each and every year, all at once.

    (4) Want to see how many times each event occurred each year? Add EventID to the query a second time, and leave it as "Group By." Now you have: every unique combination of {eventID, Year} and a count of events for each one!

    When you use the "Sigma" button, you're creating GROUP BY queries and there are two important elements: the grouping fields and the statistics. Each unique combination of values found for the group-by field-set defines "a group," and the statistics are calculated for each group.

    (5) To break down by month, add another DatePart() which includes the month-number and make it a Group By!
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Aug 2004
    Posts
    5

    Red face

    Okay, I am a little slow today!

    What I need exactly is this:

    We have a group who goes out on certain types of calls, and I need to know how many calls they have gone out on for the year and for the month.

    Thanks for any and all help!

  5. #5
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

    I assume you have a table with data and date

    Sounds like you can build a query for that. Just create separate day, month, year fields so you can group by them. It might take two queries one for year grouping the other for month and year. Can you do that with little or no instruction? Tell us about the pertainent fields in your table, please.

Posting Permissions

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