Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MTD and YTD criteria (2002 sp 2)

    This is a basic question from a basic user: I want a report that shows sales for a specific department and specific salesman, grouped by customer. I need current month and year to date, and averages.

    I have set up a query and used criteria for the department and salesman. I also have a calculated field for Total: [quantity * price]. I can use [Invoice Date] and criteria to get the current month or the current year, but that's where I lose it! How do I get BOTH?

    Then, is it easier to calculate the averages in the report or could that be done in the query, too?

    TIA
    egghead

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: MTD and YTD criteria (2002 sp 2)

    Since the current month and current year data sets require grouping on dissimilar sets of data, you are unable to get both at the same time using one query. You will need to run two queries to do this and then use the results of the queries to drive your report. As to calculating the averages, it depends on preference, however, you can calculate them in either the report or query. I prefer using the query.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: MTD and YTD criteria (2002 sp 2)

    Here is an example of how to obtain year & month to date totals in query. This example uses the Orders and Orders Details tables in Northwind.mdb; the totals are grouped by Employee & Customer. You should be able to adapt this to your own database. (I used update query to modify OrderDates in Jan 1997 to Jan 2003 for test purposes). Query SQL:

    SELECT Year([OrderDate]) AS [Order Year], Orders.EmployeeID, Orders.CustomerID, Sum([UnitPrice]*[Quantity]) AS [Total Sales YTD], Avg([UnitPrice]*[Quantity]) AS [Avg Sales YTD], Sum(IIf(Month([OrderDate])=Month(Date()),[UnitPrice]*[Quantity],0)) AS [Total Sales MTD], Avg(IIf(Month([OrderDate])=Month(Date()),[UnitPrice]*[Quantity],0)) AS [Avg Sales MTD]
    FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
    GROUP BY Year([OrderDate]), Orders.EmployeeID, Orders.CustomerID
    HAVING (((Year([OrderDate]))=Year(Date())))
    ORDER BY Orders.EmployeeID, Orders.CustomerID;

    This query uses VBA Year, Month, and Date functions to select orders for both current year (Year function in WHERE or HAVING clause) & current month (conditional expression using IIf function). Since current month is Jan, the YTD & MTD totals are identical, but in subsequent months the MTD totals should reflect the current month only.

    HTH

  4. #4
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MTD and YTD criteria (2002 sp 2)

    Next problem then! I have two queries. They are identical except for the criteria in the date field. When I use the report wizard I am getting this message:

    <hr>You have chosen fields from records sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query. <hr>
    I used all the fields from one query and just the date with the different criteria from the second query.

    I tried using all the fields from both queries but that didn't work either.
    egghead

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MTD and YTD criteria (2002 sp 2)

    Thanks for the help. I get the gist of what your saying, but I don't even know where to put the "Query SQL" ! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    egghead

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MTD and YTD criteria (2002 sp 2)

    Excuse me for jumping in here, but the Query SQL is in fact the SQL of a query. A query has 2 design views, Design View and SQL.
    So if you wish to use Marks suggested query, copy it using CTRL/INSERT and paste it to a new query you have initiated in SQL view.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: MTD and YTD criteria (2002 sp 2)

    As Pat notes, you'd have to open a new, blank query in Design View, then switch to SQL View and paste sample SQL statement in the SQL window. Then you would have to replace the Field names and Table names I used in my example (based on Northwind.mdb sample database) with the actual fields and tables you are using in your query. Without seeing your database I cannot tell you exactly how your query SQL should read. When you switch from SQL View back to Design View, you should see the tables & fields displayed (assuming the names are spelled correctly, no typos, etc).

    HTH

Posting Permissions

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