UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Rolling 12 Month Like Formula From Excel To Access, Access 2016    
 
   
lord_kaiser
post Aug 24 2019, 06:32 AM
Post#1



Posts: 138
Joined: 8-August 17
From: Australia


Hi Everyone,

I've recently migrated some data from an old excel file into an access database.

I am trying to replicate a formula on the spreadsheet (Annualised Advanced Column 'See Attached File') into an Accesss Query but not sure how to.

Ideally this formula calculates the monthly value for the next 12 months using a sumif (In essence like a rolling 12 month)

Any help is appreciated.

Thanks
This post has been edited by lord_kaiser: Aug 24 2019, 06:34 AM
Attached File(s)
Attached File  Files.zip ( 12.71K )Number of downloads: 6
 

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
GroverParkGeorge
post Aug 24 2019, 09:43 AM
Post#2


UA Admin
Posts: 35,680
Joined: 20-June 02
From: Newcastle, WA


Try this. I had to assume table and field names from your Excel sheet are the same.

SQL
SELECT [Trail].[Month], (Select Sum([temp].[Advanced]/12) FROM Trail as Temp WHERE Temp.Month Between DateAdd("m", -11, [Trail].[Month]) and [Trail].[Month] ) AS AnnualizedAdvanced, [Trail].[Advanced]/12 AS MonthlyAdvanced, [Trail].[Advanced]
FROM Trail GROUP BY [Trail].[Month], [Trail].[Advanced]/12, [Trail].[Advanced];


I would, however, recommend you rename "Month" to a non-reserved name, perhaps "AdvanceMonth" because it's unwise to use reserved words as names of other objects in Access.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Aug 24 2019, 10:11 AM
Post#3


UtterAccess VIP
Posts: 10,019
Joined: 25-October 10
From: Gulf South USA


Hi: As George pointed out, you should rename Month to a non-reserved word name. I did that, imported the two relevant columns (to a table called [TimeData) and used a DSum approach. Given the small amount of data, that seems efficient enough...

SELECT DataMonth, Advanced, CCur(DSum("[Advanced]/12","[Timedata]","[DataMonth] Between #" & DateAdd('m',-11,[DataMonth]) & "# And #" & [DataMonth] & "#")) AS Annualized
FROM TimeData;

See the demo attached, with your data...

HTH
Joe

Attached File(s)
Attached File  SampleData_lordkaiser.zip ( 18.9K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
lord_kaiser
post Aug 24 2019, 06:51 PM
Post#4



Posts: 138
Joined: 8-August 17
From: Australia


Hi Groverparkgeorge & RJD,

Thanks for your comments and feedback. Agreed, I will rename the field to something different to month.

I've had a look at your workings, however, the query isn't calculating correctly as per the excel sheet.

What i am after is:

Example:

Jan- 10/mn (120/annual)
Feb- 10/mn (120/annual)

What i need the query to do is:

Jan-dec: 10/mn =120
Feb-jan: 10/mn=120

So:
jan-18:10
feb-18:20
mar-18:20
apr-18:20
may-18:20
jun-18:20
jul-18:20
aug-18:20
sep-18:20
oct-18:20
nov-18:20
dec-18:20
jan-19:10

All in alll looking to carry the monthly trail and add to the next month for the next 12 months and then it goes off in the 13th month.

It's like a projection for the next 12 months depending on what was received in one month and adding it in a similar manner to subsequent months.

Thanks
This post has been edited by lord_kaiser: Aug 24 2019, 06:54 PM

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
GroverParkGeorge
post Aug 24 2019, 07:32 PM
Post#5


UA Admin
Posts: 35,680
Joined: 20-June 02
From: Newcastle, WA


Interesting. I imported the data from your Excel sample and used that to validate the query.

See the attached. Does this not meet your requirement? I think the output of the query does match the sample in your Excel sheet exactly, so where does it go off?

Attached File  Sample_Data_Revised.zip ( 34.45K )Number of downloads: 2

This post has been edited by GroverParkGeorge: Aug 24 2019, 07:36 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Aug 24 2019, 08:43 PM
Post#6


UtterAccess VIP
Posts: 10,019
Joined: 25-October 10
From: Gulf South USA


Hmmm ... I, too, am a bit confused. Both my and George's solutions match exactly the values shown in your spreadsheet. Could you give us more guidance, please? Or perhaps a new spreadsheet with the values you want? Are we matching the correct column?

Thanks,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
lord_kaiser
post Aug 24 2019, 10:17 PM
Post#7



Posts: 138
Joined: 8-August 17
From: Australia


Hi Groverparkgeorge & RJD,

Thank you for your feedback.

The 2nd upload query worked. Not sure what happened from the first one.

Much Appreciated.

--------------------
Regards,
lord_kaiser

Build it and they will come!!
Go to the top of the page
 
GroverParkGeorge
post Aug 24 2019, 11:21 PM
Post#8


UA Admin
Posts: 35,680
Joined: 20-June 02
From: Newcastle, WA


It's entirely possible I made an error in the first one.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 12:09 PM