Creating a complex report in access.

Question

Alright, this is going to be a fairly challenging request to explain, and I apologize if I'm coming off unclear at any point. I'm trying to convert a report that we normally update in Excel over to Access to allow for the information to update in real time (when the report is run) rather than in semi-real time (when I can copy and paste new data into a formatted Excel spreadsheet). The problem I am having is manipulating the data the way I want to in order to display this information the way it is presented on the current spreadsheet.

In this report, we have a dataset that contains values within it. What I am looking to do is display this information as follows:

Categories are my rows. Months are my columns.

An example would be if I had three rows, one that contained budgeted cans of soda sold, one that contained actual cans of soda sold, and one that contained the variance between those two numbers. These are separated, horizontally, across the page by month. If possible, having a grand total on the far right would be ideal.

No matter how I create these reports, it seems to always want to display the information vertically, and it's incredibly frustrating. I've googled quite a bit of information, but nothing tells me how to actually manipulate the data.

Once I've done this, I'm going to place this report as a sub report in a larger report that will repeat this process several times in order to compile information from five or so different categories.

I'm really open to suggestions, and would take any advice anyone has for resolving said report. If there's anything I can explain further, please let me know.

Thank you!

Answer 1

First, Access is not a spreadsheet. If you are moving data from Excel to Access you need to normalize your data.  

So your database should have several tables. A products table to list all your products. A Budgets table to list the budgeted amounts:

tblProductBudget

ProductBudgetID (PK Autonumber)

ProductID (FK)

BudgetPeriod

Amount

Then you would have an Actuals table

tblProductActual

ProductActualID (PK Autonumber)

ProductID (FK)

ActualPeriod

Amount

Next you create a query that joins the two tables to calculate a variance column.

Finally you create a crosstab query that will group by product, then Budget/actual.variance and show the period in the columns. The crosstab query becomes the source of your report.