We help IT Professionals succeed at work.

Rolling 12 Month Query

IEHP1
IEHP1 asked
on
I used the following function to pull the records from October of 2010:

POST DATE: DateAdd("m",-13,Left(Date(),2) & "/" & Right(Date(),4))

But I need to pull a Rolling 12 Month query (actually, I need November 2010 through October 2011). I tried putting And and then repeating the function with changing the -13 to a -12 and I was going to do that until -1, but it only returned all records as -1?

I need to know how I can pull this information please.

Thankyou in advance for helping me.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:

assuming you have a DateTime field named dteDate,
place this as the criteria of your query

where dteDate between dateserial(year(Date()),Month(Date())+1,0) and  dateserial(year(Date()),Month(Date())-11,0)
CERTIFIED EXPERT
Top Expert 2016

Commented:
correction, use this

where dteDate between dateserial(year(Date()),Month(Date())+1,0) and  dateserial(year(Date()),Month(Date())-12,1)

Commented:

Public Function getMonth(intNumber%)

Dim FirstDate As Date    ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
Dim Msg
IntervalType = "m"    ' "m" specifies months as interval.

FirstDate = Date
Number = intNumber * -1
Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg
 

End Function

Commented:
call it from immediate window (CTRL+G)

?getMonth(12)

Author

Commented:
I appreciate all of the comments.

I am trying to pull a Rolling 12 Month date range meaning, for my report, I need it to show everything from the beginning of November 2010 until the end of October 2011.

I used your criteria, capricorn1. Unfortunately, it returns the same results (earliest records have 06 January 2011). I know that the records are there for 2010 in the table being queried and when I used DateAdd("m",-13,Left(Date(),2) & "/" & Right(Date(),4))  it pulled every record having 10/01/2010 in the date/time field.

Please help me to figure this out.

As for your comments Devtha, I am not that experienced in Visual Basic and I don't know really how I should respond to your comments. When I pressed CTRL+G it opend Visual Basic and do you want a print screen of that? I am not sure what to do after the VB screen opens?
CERTIFIED EXPERT
Top Expert 2016

Commented:
can you post the sql statement of the query you created...

better if you can upload a .mdb with the table..

Author

Commented:
okay wait I am building dummy records for you to use.

Commented:
Upload the sample MDB and then we can talk/chat here.

Author

Commented:
I have attached the Dummy Database and I hope this helps. Please let me know if you have any questions. I put in the structure of the real database so you can see what I am tryint to do.
Dummy-Database.mdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
can't open your db..
if you are using A2007 or A2010,

create a blank .mdb and import the dummy table you created

upload the new .mdb

Author

Commented:
No, it is a 2003 (.mdb) database. I can make it a 2007 (.accdb) if you would like??

Let me know please
CERTIFIED EXPERT
Top Expert 2016

Commented:
can you create  new .mdb and upload.. the file you uploaded is on "unrecognized format"

Author

Commented:
Okay I made a copy. I don't know why it says "unrecognized format"? You can change the file extension to .accdb if you would like (I don't think it matters).....
Copy-of-Dummy-Database.mdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
still it is on "unrecognized format"

read my post at http:#a37078055

Author

Commented:
Did you try changing the file extension? I am attaching the same database (not the copy) with it being in 2007 format now. I don't understand: if you have Access 2007 (or 2003), it should open without giving you that error?
Dummy-Database.accdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
i said  i need a .mdb not .accdb (don't have A2007 at the moment)

<Did you try changing the file extension? > DON"t just change the extension from .accdb to .mdb


again from your A2007, create a BLANK .MDB
then import the table

upload the create .mdb

Commented:
I am able to open the db what is next?

Do you need all the data that falls within last 11 months?

Author

Commented:
i don't have Access 2003? I searched for Microsoft Office Access and only Microsoft Office Access 2007 came up?

Commented:
IEHP

Use this query against your table but before you do that change your date field to claimdate and change the data type to date. A date cannot be a text field. This query wll return any data with date between yesterday and past 365 days.

SELECT [dbo_Details table].claimDate, [dbo_Details table].Claim, [dbo_Details table].Line, [dbo_Details table].Subline, [dbo_Details table].POSTDT, [dbo_Details table].NOTCOVRSN, [dbo_Details table].ADJUSTRSN, [dbo_Details table].ALLOWRSN, [dbo_Details table].updat, [dbo_Details table].updater
FROM [dbo_Details table]
WHERE ((([dbo_Details table].claimDate) Between (Date()-365) And Date()-1));

Author

Commented:
oh yes thank you devtha!!

I need a rolling 12 month meaning from now I want to pull all of the data from the first day of November 2010 to the last day of October 2011. So this would be an ongoing monthly report that would use these queries so that all of the months from 12 months and forward can be seen (in mm\yyyy) format (and summed for each month, but that is another issue that I can tackle if we can pull the right date range)

Commented:
yes this query will pull the right data for past 364 days. You can play with the numbers in the criteria.

Importantly you need to change the data type of dat column. Never name a column like 'date' as it is a reserved word in MS Access.
So I have named the column n the table here to claimDate.

This should work. Let me know if you need more help.

Author

Commented:
Ok, I need to use POSTDT, not the claimDate to base my results on. I tried that date range criteria in the query that runs from the dbo_Details table and it gives me a "Data Type Mismatch" error?

And I am trying to pull a rolling 12 month, not Date()-365 (what if it was a leap year or something)?

Please help me with this.
CERTIFIED EXPERT
Top Expert 2016

Commented:
use this query


SELECT [dbo_Details table].Date, [dbo_Details table].Claim, [dbo_Details table].Line, [dbo_Details table].Subline, [dbo_Details table].POSTDT, [dbo_Details table].NOTCOVRSN, [dbo_Details table].ADJUSTRSN, [dbo_Details table].ALLOWRSN, [dbo_Details table].updat, [dbo_Details table].updater, Left([POSTDT],6) AS Expr1
FROM [dbo_Details table]
WHERE (((Left([POSTDT],6)) Between Format(DateSerial(Year(Date()),Month(Date())+1,0),"yyyymm") And Format(DateSerial(Year(Date()),Month(Date())-12,1),"yyyymm")));

Commented:
Rename the postdt field to postdt_old
Create a new postdt field with date as datatype.

UPDATE [dbo_Details table] SET [dbo_Details table].POSTDT = Left([POSTDT_old],4) & "/" & Mid([POSTDT_old],5,2) & "/" & Right([POSTDT_old],2);

This will create a new field with proper data type.
Once you are done with the above use the following query, and this one accounts for leap years

SELECT [dbo_Details table].Claim, [dbo_Details table].Line, [dbo_Details table].Subline, [dbo_Details table].POSTDT, [dbo_Details table].NOTCOVRSN, [dbo_Details table].ADJUSTRSN, [dbo_Details table].ALLOWRSN, [dbo_Details table].updat, [dbo_Details table].updater
FROM [dbo_Details table]
WHERE ((([dbo_Details table].POSTDT) Between (DateAdd("m",-12,Date())) And Date()));

Commented:
capricorn if you use left, mid, right function it will slow the quey.

Author

Commented:
ummmm......i don't know why, but when i enter in that criteria, it just changes it back to the old criteria. I think it would be easier if you guys just told me which fields to modify in the database (since my upload was dummy data and I changed the field names).

Or you guys can post my dummy database with your modifications, right?

Commented:
The attached dB has the query that contains whtyouneed in the name.
Copy-of-Dummy-Database.accdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
test this
run query1


Dummy-Database.mdb

Commented:
IE, did that help?

Author

Commented:
I am not sure why you changed the _02 step to pull from the dbo_Details table? I was doing a count that is affected by the change?

And when I changed it to your specifications, it takes too long for the query to run??

Commented:
IE the attachment I gave you is functional.
CERTIFIED EXPERT
Top Expert 2016

Commented:
:IEHP1,

did you test the db i uploaded ? no need to create a new field

Commented:
Capri, your query would not account for leapyear. It is only accounting for 12 months.
CERTIFIED EXPERT
Top Expert 2016

Commented:
devtha,
what make you say so?


CERTIFIED EXPERT
Top Expert 2016

Commented:
IEHP1,

try this version

run query2


Dummy-DatabaseRev.accdb
CERTIFIED EXPERT
Top Expert 2016

Commented:
wrong file, use this version

run query2

Dummy-DatabaseRev.accdb

Commented:
Capri Please try abd tgeb let me know.

Author

Commented:
Capricorn1: I am still testing your Post Date function, but it returned no results?

I will try your 2nd one.
CERTIFIED EXPERT
Top Expert 2016

Commented:
IEHP1,

what are you doing...?

open the uploaded db and run query2

Author

Commented:
I Ran it.

I am sorry, I don't really understand.....I mean I provided a screen shot of the results so you can see.

What would you like me to do after running it?

New-Microsoft-Office-Word-Docume.docx
CERTIFIED EXPERT
Top Expert 2016

Commented:
you will see the result with POSTDT  filtered from  20101101 to 20111031

that is what you are asking  ?

     <Rolling 12 Month query (actually, I need November 2010 through October 2011).>

Commented:
IEHP the db i gave you is ready for you all you need to do is follow my instructions. If you do this it will help you in long run and would not impact the performance.
CERTIFIED EXPERT
Top Expert 2016
Commented:

this one will insert records to table "VE Claim Details Report"

NO NEED to ADD another field to your table that you need to update everytime you will get new records

run the query

         VE Report Details_02 Rolling 12 Months
Dummy-DatabaseRev.accdb

Commented:
so it seems that every time IE has to insert records.  
Luke ChungPresident
CERTIFIED EXPERT

Commented:
Please take a look at my paper on creating a 12 month rolling summary of sales. It creates a report based on a cross tab. You simply specify the starting date and it creates the results. Best of all, it requires no VBA code at all. A sample database is included:

Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query
http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html

Hope this helps.

Commented:
Luke IE is pretty new at this to run crosstab as of yet. He/she needs a simple solution.

Commented:
Is this question abondoned now?

Author

Commented:
Sorry for not getting back to you guys.....I figured it out and I appreciate all of the quick answers and eagerness to help me. I am learning surely enough.

Author

Commented:
Thank you Capricorn. I chose you because you led me to the answer.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.