Rolling 12 Months Data

K

Kali

Hello

I need to create a database where I need the past 12 months data. For Jan
2009, I will need Feb-Dec 2008 and Jan 2009. And, so on... I have 3 columns,
year, period, and total. Each period has mulitiple line of totals. What
criteria would I need to retrieve the 12 month data.
I've read some other post, but none with period instead of date format.

thanks in advance.
 
T

Tom Wickerath

Hi Kali,

Perhaps something like this:

Between Date() And Month(Date()) & "/1/" & Year(Date())-1

Try the following query in the sample Northwind database. You'll need to
create a new query and dismiss the Add Table dialog. Then click on View | SQL
View, to open the SQL (Structured Query Language) view of the query. Copy the
SQL statement shown below, and paste it into the SQL View:

TRANSFORM Sum(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity])))
AS Sales
SELECT [Lastname] & ", " & [FirstName] AS Employee

FROM (Categories
INNER JOIN Products ON Categories.CategoryID=Products.CategoryID)
INNER JOIN ((Employees
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID)
ON Products.ProductID=[Order Details].ProductID

WHERE (((Orders.OrderDate) Between Date() And Month(Date()) & "/1/" &
Year(Date())-1))

GROUP BY [Lastname] & ", " & [FirstName]

ORDER BY [Lastname] & ", " & [FirstName],
Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1)) DESC

PIVOT Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1));


Of course, you may need to edit some of the records in the Orders table, so
that the OrderDate field has some records with dates in the past year.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
J

John W. Vinson

What
criteria would I need to retrieve the 12 month data.
Click to expand...
= DateSerial(Year(Date()), Month(Date()) - 12, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)
Click to expand...

will get the twelve calendar months prior to the current month.
 
D

Douglas J. Steele

You really should be using

Between Date() And DateSerial(Year(Date())-1, Month(Date()), 1)

Month(Date()) & "/1/" & Year(Date())-1 will result in a string, so you're
forcing Access to do variable coercion. If the user's ShortDate format is
set to dd/mm/yyyy, Month(Date()) & "/1/" & Year(Date())-1 will result in 1
Jan, 2008, 2 Jan, 2008, 3 Jan, 2008 and so on to 12 Jan, 2008, not the 1
Jan, 2008, 1 Feb, 2008, 1 Mar, 2008 and so on to 1 Dec, 2008 that you're
expecting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tom Wickerath said:
Hi Kali,

Perhaps something like this:

Between Date() And Month(Date()) & "/1/" & Year(Date())-1

Try the following query in the sample Northwind database. You'll need to
create a new query and dismiss the Add Table dialog. Then click on View |
SQL
View, to open the SQL (Structured Query Language) view of the query. Copy
the
SQL statement shown below, and paste it into the SQL View:

TRANSFORM Sum(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity])))
AS Sales
SELECT [Lastname] & ", " & [FirstName] AS Employee

FROM (Categories
INNER JOIN Products ON Categories.CategoryID=Products.CategoryID)
INNER JOIN ((Employees
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID)
ON Products.ProductID=[Order Details].ProductID

WHERE (((Orders.OrderDate) Between Date() And Month(Date()) & "/1/" &
Year(Date())-1))

GROUP BY [Lastname] & ", " & [FirstName]

ORDER BY [Lastname] & ", " & [FirstName],
Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1))
DESC

PIVOT Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1));


Of course, you may need to edit some of the records in the Orders table,
so
that the OrderDate field has some records with dates in the past year.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Kali said:
Hello

I need to create a database where I need the past 12 months data. For
Jan
2009, I will need Feb-Dec 2008 and Jan 2009. And, so on... I have 3
columns,
year, period, and total. Each period has mulitiple line of totals. What
criteria would I need to retrieve the 12 month data.
I've read some other post, but none with period instead of date format.

thanks in advance.
Click to expand...
Click to expand...
 
K

Kali

Thank you guys for your replies.
But, will this work if I have period instead of actual dates. For example,
for August, the period column has 8, instead of Aug 1 2008.

Thanks
 
K

Ken Sheridan

You'll need to use an expression to return a true date/time value instead of
referring to an OrderDate column, e.g.

WHERE DateSerial([Year], [Period],1) Between
DateSerial(Year(Date())-1, Month(Date()), 1) And Date()

BTW I'd avoid using Year as a column name. As you see its also the name of
a built in function, so could be misinterpreted. Use something more specific
such as OrderYear as the column name. If you do use it as a column name be
sure to wrap it in brackets [Year] when referencing it in a query or code.

Ken Sheridan
Stafford, England
 
T

Tom Wickerath

Hi Doug,

Thanks for the input. I agree that it's probably not good to force Access to
do the coersion on-the-fly, if you can avoid it. However, I just tried both
forms of the criteria in a test query in the sample Northwind database, and
I'm not seeing any difference in the results. I tried setting my Short Date
format to dd/mm/yyyy and to mm/dd/yyyy in the Control Panel Regional Options
applet. I exited Access and re-started it in-between each test, just in case
this setting is only read during startup.
...not the 1 Jan, 2008, 1 Feb, 2008, 1 Mar, 2008 and so on to 1 Dec, 2008
that you're expecting.
Click to expand...

I wasn't expecting only the first day of each month.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Douglas J. Steele said:
You really should be using

Between Date() And DateSerial(Year(Date())-1, Month(Date()), 1)

Month(Date()) & "/1/" & Year(Date())-1 will result in a string, so you're
forcing Access to do variable coercion. If the user's ShortDate format is
set to dd/mm/yyyy, Month(Date()) & "/1/" & Year(Date())-1 will result in 1
Jan, 2008, 2 Jan, 2008, 3 Jan, 2008 and so on to 12 Jan, 2008, not the 1
Jan, 2008, 1 Feb, 2008, 1 Mar, 2008 and so on to 1 Dec, 2008 that you're
expecting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tom Wickerath said:
Hi Kali,

Perhaps something like this:

Between Date() And Month(Date()) & "/1/" & Year(Date())-1

Try the following query in the sample Northwind database. You'll need to
create a new query and dismiss the Add Table dialog. Then click on View |
SQL
View, to open the SQL (Structured Query Language) view of the query. Copy
the
SQL statement shown below, and paste it into the SQL View:

TRANSFORM Sum(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity])))
AS Sales
SELECT [Lastname] & ", " & [FirstName] AS Employee

FROM (Categories
INNER JOIN Products ON Categories.CategoryID=Products.CategoryID)
INNER JOIN ((Employees
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID)
ON Products.ProductID=[Order Details].ProductID

WHERE (((Orders.OrderDate) Between Date() And Month(Date()) & "/1/" &
Year(Date())-1))

GROUP BY [Lastname] & ", " & [FirstName]

ORDER BY [Lastname] & ", " & [FirstName],
Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1))
DESC

PIVOT Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1));


Of course, you may need to edit some of the records in the Orders table,
so
that the OrderDate field has some records with dates in the past year.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Kali said:
Hello

I need to create a database where I need the past 12 months data. For
Jan
2009, I will need Feb-Dec 2008 and Jan 2009. And, so on... I have 3
columns,
year, period, and total. Each period has mulitiple line of totals. What
criteria would I need to retrieve the 12 month data.
I've read some other post, but none with period instead of date format.

thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Tom:

My experience differs. With our regional short date setting of dd/mm/yyyy
I'm getting the wrong rows returned with your expression (provided I change
the current month from January of course so the month and day values differ),
which is what I'd expect. If I transpose the month and day parts of the
expression it returns the correct rows, again as expected. Building a string
expression like this, however, I think the best bet would be to use the ISO
standard date format of YYYY-MM-DD: Year(Date())-1 & "-" & Month(Date()) &
"-1". This should work with any regional setting, and seems to do so as far
as I can tell, but even so I'd be a little wary about the possibility of it
being evaluated as an arithmetical expression: 2008 minus 1 minus 1; one
context in which that certainly does happen is when setting the DefaultValue
property of a control to a date, which is why when doing so its essential to
include literal quotes characters as part of the string expression. To be
bulletproof: CDate(Year(Date())-1 & "-" & Month(Date()) & "-1"). On the
whole though I'd be inclined to go along with Doug and opt for calling the
DateSerial function.

Ken Sheridan
Stafford, England

Tom Wickerath said:
Hi Doug,

Thanks for the input. I agree that it's probably not good to force Access to
do the coersion on-the-fly, if you can avoid it. However, I just tried both
forms of the criteria in a test query in the sample Northwind database, and
I'm not seeing any difference in the results. I tried setting my Short Date
format to dd/mm/yyyy and to mm/dd/yyyy in the Control Panel Regional Options
applet. I exited Access and re-started it in-between each test, just in case
this setting is only read during startup.
...not the 1 Jan, 2008, 1 Feb, 2008, 1 Mar, 2008 and so on to 1 Dec, 2008
that you're expecting.
Click to expand...

I wasn't expecting only the first day of each month.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Douglas J. Steele said:
You really should be using

Between Date() And DateSerial(Year(Date())-1, Month(Date()), 1)

Month(Date()) & "/1/" & Year(Date())-1 will result in a string, so you're
forcing Access to do variable coercion. If the user's ShortDate format is
set to dd/mm/yyyy, Month(Date()) & "/1/" & Year(Date())-1 will result in 1
Jan, 2008, 2 Jan, 2008, 3 Jan, 2008 and so on to 12 Jan, 2008, not the 1
Jan, 2008, 1 Feb, 2008, 1 Mar, 2008 and so on to 1 Dec, 2008 that you're
expecting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tom Wickerath said:
Hi Kali,

Perhaps something like this:

Between Date() And Month(Date()) & "/1/" & Year(Date())-1

Try the following query in the sample Northwind database. You'll need to
create a new query and dismiss the Add Table dialog. Then click on View |
SQL
View, to open the SQL (Structured Query Language) view of the query. Copy
the
SQL statement shown below, and paste it into the SQL View:

TRANSFORM Sum(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity])))
AS Sales
SELECT [Lastname] & ", " & [FirstName] AS Employee

FROM (Categories
INNER JOIN Products ON Categories.CategoryID=Products.CategoryID)
INNER JOIN ((Employees
INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID)
INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID)
ON Products.ProductID=[Order Details].ProductID

WHERE (((Orders.OrderDate) Between Date() And Month(Date()) & "/1/" &
Year(Date())-1))

GROUP BY [Lastname] & ", " & [FirstName]

ORDER BY [Lastname] & ", " & [FirstName],
Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1))
DESC

PIVOT Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1));


Of course, you may need to edit some of the records in the Orders table,
so
that the OrderDate field has some records with dates in the past year.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hello

I need to create a database where I need the past 12 months data. For
Jan
2009, I will need Feb-Dec 2008 and Jan 2009. And, so on... I have 3
columns,
year, period, and total. Each period has mulitiple line of totals. What
criteria would I need to retrieve the 12 month data.
I've read some other post, but none with period instead of date format.

thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Ken,

So, are you saying that because the current month is January, and I did not
first change my system date to some other month, that this is why I didn't
see any difference? Would you be able to post a sample that clearly
demonstrates an error (incorrect recordset returned) when building the
criteria as I first suggested? Its much more likely to make a permanent brain
print to me, if I can see a demonstrabable error.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
K

Ken Sheridan

Tom:

Yep, it’s the fact that this month the Month value is 1 which makes the
regional formatting irrelevant because the constant 1 is used for the day, so
whichever order they appear in makes no difference wherever you are. The
easiest way to illustrate it, rather than changing the system date to a
different month, is simply to put a month value in as a constant in the
expression rather than getting it with the Month function, so using Northwind
as an example and looking for rows from May 1998 onwards for instance:

SELECT*
FROM Orders
WHERE ShippedDate
BETWEEN "5/1/" & YEAR(DATE())-11 AND DATE()
ORDER BY ShippedDate;

This will work for you with your mm/dd/yyy regional format, but with my
European format of dd/mm/yyyy I get rows from 5 January 1998 onwards, not
1May onwards. I'd need to change it to:

SELECT*
FROM Orders
WHERE ShippedDate
BETWEEN "1/5/" & YEAR(DATE())-11 AND DATE()
ORDER BY ShippedDate;

Which would have the opposite effect and give you the wrong results of
course. But if the ISO date format of YYYY-MM-DD is used:

SELECT*
FROM Orders
WHERE ShippedDate
BETWEEN YEAR(DATE())-11 & "-05-01" AND DATE()
ORDER BY ShippedDate;

it will work for both of us. I'd prefer the following, however, to allow
for the possible misinterpretation of the expression when it evaluates to
1998-05-01 as an arithmetical expression:

SELECT*
FROM Orders
WHERE ShippedDate
BETWEEN CDATE(YEAR(DATE())-11 & "-05-01") AND DATE()
ORDER BY ShippedDate;

The only situation where I know for sure that this sort of misinterpretation
occurs is when setting the DefaultValue property, but I'm nevertheless not
completely confident with comparing a date/time value with a string
expression per se.

For us Yurpeans remembering to put date values in an internationally
unambiguous format has become second nature as a lot of the time we are
dealing with our friends across the pond (we also have to be bilingual with
words like color and colour!)

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Looking formula that gives total of remaining months 6
Rolling 12 month total 7
obtain data for 12 months rolling 1
How to chart months by financial year 0
Rolling 12 Month Report 4
Create a 12 month rolling average 15
Date Serial Problem 2
Rolling totals 1

Top