year to date month to date

  • Thread starter Thread starter claudiam
  • Start date Start date
C

claudiam

I have a table with customer-sales-saleman - date paid fields
I made a query to sort by customer, sum sales,if date paid i
Feb2003...but I cannot figure out how to show current month and year t
date sales... tried expression in a query... tried text box in repor
... Any ideas out there? THANKS A BUNC
 
Hi Claudiam ,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

There are many ways to do this, from creating a function to nesting an
additional query. Nesting a query would work as follows:

First create a query that returns Monthly values

1-Create Query1
2-Click on the Totals button
3-Create one column where the Total line groups by SalesID (or whatever
would make the records unique) and set this to GroupBy
4-Create another column where the Total line Sums a value field
5-Create another column with the Total line set to Where and type on the
Criteria line Month(Date)
6-You can add any additional lines that you need information from, setting
the Total line to First or Last (will return the information found in the
first or last record).
7-Close and save Query1

Next create a query to return Yearly values

1-Create Query2
2-Click on the Totals button
3-Create one column where the Total line groups by SalesID (or whatever
would make the records unique) and set this to GroupBy
4-Create another column where the Total line Sums a value field
5-Create another column with the Total line set to Where and type on the
Criteria line Year(Date)
6-Close and save Query2

Now if you open Query1, add Query2 and join them on the unique field you
should have one query that returns values for both current month and
current year.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support
 
Create a query that totals by Customer. Create two calculated columns
MTDSales: Sum( Abs(Format(SalesDate,"yyyymm") = Format(Date(), "yyyymm")) *
[SalesAmt])
YTDSales: Sum( Abs(Year(SalesDate) = Year(Date())) * [SalesAmt])
You can set the criteria to the current year if you want.
 
Back
Top