Written by Joe Plant

With a tiny bit of help from the IT department, I have managed to create a report that links directly up to Access. So instead of exporting transaction data from Access and copy and pasting it into our Month end Analysis sheets, we should be able to simply refresh the report after each journal etc.

 

Start as normal, by creating a new tab for the new month’s data.

 

Before the next step, it Is important that cell A1 is selected, this is where the report will start.

Then choose the following:

Access > Templates > Setup Update Template

 

 

Then choose Existing Template and the Report ‘Project Analysis Data – Draft’.

Then click Load.

 

 

 

 

 

Do not get confused by the next page, just click Next.

 

 

 

 

 

You will need to add/adjust some criteria on this step.


Firstly, click the Project Code parameter, this will be pre-filled out with MC17030H to provide an example. You need to Delete this line, then insert the Project code you are working on, then Add.

 

 

 

Now select the parameter for the Period. Simply change to the period you are working on. Then click update.
 (We will need to change the Year to ‘N’ during the Year-end period).

 

 

 

 

 


Once you are happy you have updated these, click Next.

Now simply click the Save Template box and rename to the Project code you are working on. This way, when you run the report the following month, you will only need to update the period, not the project code.

Or if you do not wish to create loads of reports on your drop down, untick and just repeat the above each month.

Do not click ‘Enable changes to be written back to Access accounts’ – no body knows what horrors this could cause in Access.


Click Insert.


 

If this bad boy pops up, do not worry, just say yes.


 


A report will run, you will see it looks slightly different from the previous report. It still includes all the information we need and has got rid of some of the data we didn’t use.


 






However, there is one more thing you need to remember to do.

On the Actuals tab, when you are copying over the formula. You need to adjust the SUMIF formula to look up columns F and R like so:

=SUMIF('Access Data May 20'!$F:$F,Actuals!$A7,'Access Data May 20'!$R:$R)

Column F being the Cost Centre code that is being looked up and column R being the Net Value.

You can also use this on the sales line! No need to look up the Sales separately anymore.



You can now post your journals as normal and all you need to do is go back to the tab that has the report on and click refresh. Simples.