Help with suming data from last year 1

[JCAA]

I have a field with dates that I formatted to YYYY/MM. I want to calculate how much I sold in the last year up to the previous month end which in this case would be 2003/01 and 2003/02. If it was Jun 11th 2004 I would need the data from 2003/01-2003/05 (not including June ’03).
I was thinking that I can use 2003/01 as my start date my end date could be last month entered -13 months as the previous example my last month is June ’04 and I would therefore get 2003/01 through 2003/05 does that make any sense? I am not sure how I would write this so I would really appreciate all the help I can get.

Thanks,

JCA

 

[JCAA]

Year: Year([MonthDate])
Set the criteria of that column to:
Year(DateAdd("yyyy",-1,Date()))

Someone told me that I should write it this way with a DateAdd function but when I do I get a parameter query asking me for the value of MonthDate. My MonthDate field is

MonthDate: Format([Date into Access],"yyyy/mm") and it is Group By.

Thanks!

 

[Golom]

Just to rephrase, you want data from all months before the current one in the current year.
[tt]
WHERE [SaleDate] < DateSerial ( Year(Date()), Month(Date()), 1)
AND Year([SaleDate]) = Year(Date())
[/tt]

 

[JCAA]

Golom,

Almost right, I want data from all months before the current one in the PREVIOUS year. I realize that I formulated that kinda awkward. How would I modify it for that?
Thanks!

 

[JCAA]

To reformulate it I want all the data from last year in the month that already passed so if it is October 2004 I want all the data including Jan1, 2003 -Sep30, 2003 if I am in March 2004 (right now) I want all the data between Jan1, 2003 - Feb 28, 2003
THANKS!
I am sorry for explaining this wrong.

 

[Golom]

See what this does for you
[tt]
WHERE [SaleDate] < DateSerial ( Year(Date())-1, Month(Date()), 1)
AND Year([SaleDate]) = Year(Date())-1
[/tt]

 

[JCAA]

Hmm... I am still getting a parameter query asking me to put in the value for [MonthDate] or as you called it [SaleDate]. Do I put the code in the Criteria on design view?

Thanks again!

 

[JCAA]

This is my SQL right now:

SELECT [BBB - Historical Invoice File].Salesrep, [XXX - Customer Master File (USE THIS ONE)].Customer, [XXX - Customer Master File (USE THIS ONE)].[Customer Name], [XXX - Customer Master File (USE THIS ONE)].[Street Address], [XXX - Customer Master File (USE THIS ONE)].City, [XXX - Customer Master File (USE THIS ONE)].State, [XXX - Customer Master File (USE THIS ONE)].[ZIP CODE], [XXX - Customer Master File (USE THIS ONE)].Region, Format([Date into Access],"yyyy/mm") AS MonthDate
FROM [BBB - Historical Invoice File] LEFT JOIN [XXX - Customer Master File (USE THIS ONE)] ON [BBB - Historical Invoice File].Customer = [XXX - Customer Master File (USE THIS ONE)].Customer
GROUP BY [BBB - Historical Invoice File].Salesrep, [XXX - Customer Master File (USE THIS ONE)].Customer, [XXX - Customer Master File (USE THIS ONE)].[Customer Name], [XXX - Customer Master File (USE THIS ONE)].[Street Address], [XXX - Customer Master File (USE THIS ONE)].City, [XXX - Customer Master File (USE THIS ONE)].State, [XXX - Customer Master File (USE THIS ONE)].[ZIP CODE], [XXX - Customer Master File (USE THIS ONE)].Region, Format([Date into Access],"yyyy/mm")
HAVING ((([XXX - Customer Master File (USE THIS ONE)].Region)<80));

How would I modify this in the way you describe? I am using [MonthDate] as my date field to be used.

thanks,

 

[Golom]

I would just be guessing. Can you switch to SQL view and paste your SQL here so we can see what you are working with? It's difficult to offer good advice on query designer because the things that you may need to know are scattered all over the place.

 

[JCAA]

I think I did on the same time as you replied

 

[Golom]

Minor modification ... used table aliases for readability
[tt]
SELECT B.Salesrep, X.Customer, X.[Customer Name], X.[Street Address],
X.City, X.State, X.[ZIP CODE], X.Region,
Format([Date into Access],"yyyy/mm") AS MonthDate

FROM [BBB - Historical Invoice File] B
LEFT JOIN [XXX - Customer Master File (USE THIS ONE)] X
ON B.Customer = X.Customer

WHERE [Date into Access] < DateSerial ( Year(Date())-1, Month(Date()), 1)
AND Year([Date into Access]) = Year(Date())-1
AND X.Region <80

GROUP BY B.Salesrep, X.Customer, X.[Customer Name], X.[Street Address],
X.City, X.State, X.[ZIP CODE], X.Region,
Format( [Date into Access] ,"yyyy/mm")
[/tt]
I have moved your HAVING condition to the WHERE clause because it does not involve an aggregate function.

Just a comment: You may not need the GROUP BY because you are not using any Aggregate functions. If the fields that you have selected include the primary key field(s) then the records are guaranteed to be unique even without Group By.

 

[JCAA]

Golom,

Wow that is very cool. It does work like a charm! I do need to add more fields as example in addition to the field summing up the data for Jan - Feb 2003 I also need to compare it to Jan - Feb 2004 but right now I am only getting the data for 2003. Can I modify it so that I get the data I have but still be able to have other fields with data from different time periods such as Jan - Feb '04, Jan 04 etc, etc.
I do appreciate your help so much!

 

[Golom]

I'm a bit fuzzy on what you are asking but here are a couple of overviews of how you might approach it.

Multiple Date Ranges
Change your Where Clause to include multiple date filters like this
[tt]
WHERE ([Date into Access] < DateSerial ( Year(Date())-1, Month(Date()), 1)
AND Year([Date into Access]) = Year(Date())-1)

OR

([Date into Access] < DateSerial ( Year(Date()), Month(Date()), 1)
AND Year([Date into Access]) = Year(Date()))
[/tt]
That gives you Last year Jan-Feb AND this year Jan-Feb (since this is March). Briefly, you can string out filter conditions separated by "OR" to include whatever different ranges you want.

Using UNION
You can retrieve the different sets of information that you want in different queries and then merge them using a UNION ALL query as in
[tt]
SELECT B.Salesrep, X.Customer, X.[Customer Name], X.[Street Address],
X.City, X.State, X.[ZIP CODE], X.Region,
Format([Date into Access],"yyyy/mm") AS MonthDate

FROM [BBB - Historical Invoice File] B
LEFT JOIN [XXX - Customer Master File (USE THIS ONE)] X
ON B.Customer = X.Customer

WHERE [Date into Access] < DateSerial ( Year(Date())-1, Month(Date()), 1)
AND Year([Date into Access]) = Year(Date())-1
AND X.Region <80

GROUP BY B.Salesrep, X.Customer, X.[Customer Name], X.[Street Address],
X.City, X.State, X.[ZIP CODE], X.Region,
Format( [Date into Access] ,"yyyy/mm")

UNION ALL

SELECT B.Salesrep, X.Customer, X.[Customer Name], X.[Street Address],
X.City, X.State, X.[ZIP CODE], X.Region,
Format([Date into Access],"yyyy/mm") AS MonthDate

FROM [BBB - Historical Invoice File] B
LEFT JOIN [XXX - Customer Master File (USE THIS ONE)] X
ON B.Customer = X.Customer

WHERE [Date into Access] < DateSerial ( Year(Date()), Month(Date()), 1)
AND Year([Date into Access]) = Year(Date())
AND X.Region <80

GROUP BY B.Salesrep, X.Customer, X.[Customer Name], X.[Street Address],
X.City, X.State, X.[ZIP CODE], X.Region,
Format( [Date into Access] ,"yyyy/mm")
[/tt]
which does the same thing as the previous example but places the retrieval of different sets of information in different SELECTs in the Union query. The advantage of UNION is that, if you had your individual retrievals for different selection criteria already stored as "qry1" and "qry2" (for example) then the query to combine them is a very compact
[tt]
(Select * From qry1)
UNION ALL
(Select * From qry2)
[/tt]