Calculating Across Pivot Tables - Access 2003

[Altin]

For a few years now Ive used an Excel spreadsheet with pivot tables to track our house finances. For each year I have 2 pivot tables, 1 for the various incomes per month & 1 for the various expenses per month. These work nicely in showing me the regularity of incomes/bills & monthly totals. I also have a 3rd table, per year, in which I calculate the total income in [Jan] minus the total expenses in [Jan], so I can very quickly see if we spent more than we paid into the account.

I am trying to upgrade the spreadsheet now to an Access 2003 database, & while I can get the pivot tables working, it is the calculated table that I am stuck on. Can you perform calculations between pivot tables in Access, & if so how would I do this?

I would also really like to be able to open just one form/report/etc to view all the income & expense pivot tables & calculated tables for all the years, as I have in Excel at the moment, so I can see patterns of spending, etc, rather than have them all as seperate forms. How could I do this?

Thanks

 

[Banana]

What you are asking should definitely be possible with Access PivotTable.

However, I have to confess I don't know for a fact whether you have to build similar data structure to get the same result in Excel as you would have with Access. Can you provide a bit more information on how you have designed your Access tables that acts as the source for the PivotTable? That will help a bit, I think.

FWIW, I also tend to think it's easier to use a query to base your PivotTable query so you can use Access expressions to do calculations as you need. It is certainly possible to write a custom calculation with PivotTable, but the catch is that you have to use MMX expressions and I have no idea how one would use MMX expressions. So, if you can build a query that gives you the sums per month as well the net amount of the two different sum, you have a great source for your PivotTable and design is much easier that way.

Does that help?

 

[Lightwave]

Right I've done something like this before

I had a list of housing sites and I had 2 sets of housing programmed on those sites and I wanted to subtract one set from the other but show the two sets of figures at the same time

I had to use pivot tables , union queries and the report writer I did it all in Access 2003

I suspect as you have just started using Access 2003 you won't have come across union.

The following site will give you more information about Union queries...

http://www.w3schools.com/sql/sql_union.asp

It should be noted that I think from memory you have to actually type union queries and you can't use the initial graphical display in Access 2003 to write them.

You switch between the textual query writer and the graphical writer in 2003 by using the View/SQLView option when in the Query builder

The opening paragraph in that link is important

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

This could potentially be a problem for you and I have seen people enter dummy records as unions will easily muck up as a result of this.

One tip which should help with this is that Pivot tables can be forced to produce columns by adding further lines in the SQL editor. I may be wrong but I think this has to be done in the SQL Editor.

The following is an example with a single query's SQL that shows the aggregate number of houses coming forward by town over a period between 1970 and 2011. Many of the towns may not have had housing in some of those years but given that I may want to produce a report on the pivot table if suddenly had columns added or subtracted as a result of data entry I would get an error in any reports based on the query as the report forces you to list the columns as fields in the design and does not add or subtract new columns dynamically as the pivot command does. Thus I force Access to show every year from 1970 to 2011 by using "In" after the Pivot.

TRANSFORM Sum(Q092HistoricalNonPropBase.Constructed) AS SumOfConstructed
SELECT Q092HistoricalNonPropBase.Town
FROM Q092HistoricalNonPropBase
GROUP BY Q092HistoricalNonPropBase.Town
ORDER BY Q092HistoricalNonPropBase.Town
PIVOT Q092HistoricalNonPropBase.Year In ("1970","1971","1972","1973","1974","1975","1976","1977","1978","1979","1980","1981","1982","1983","1984","1985","1986","1987","1988","1989","1990","1991","1992","1993","1994","1995","1996","1997","1998","1999","2000","2001","2002","2003","2004","2005","2006","2007","2008","2009","2010","2011");

My hunch is you Pivot your expenses PIvot your income ensure that you have the same number of columns by using the IN command and then maybe union the two Pivot queries. Calculations would then have to be done in the report writer where you might be able to group by a row the income and expenditure and deduct expenditure from income within each column.

That's how I did it a mixture of Pivot Queries / Union Queries and Report writer.

 

[Altin]

The tables I am using for this account are very simple, just 4 fields for Date, Category (of income/expense), Income Amount & Expense Amount. The resulting table is very similar to your standard bank statement in that it just lists all entries one after another.

I like the idea of just using a query rather than trying to work with the pivot tables, & it certainly sounds easier than going into MMX expressions or union queries, especially given my novice experience of Access. How would I write the query to get, the sum of incomes - sum of expenses, for each month in 2009? I would repeat this in another query then for 2010, 2011, etc. This is so that I could have seperate forms showing each years results.

Can this be made to produce a horizontally aligned table, where the months are listed along 1 row & the respective figures listed underneath each month?

Thanks for your help

 

[Banana]

Your query doesn't have to actually produce horizonally aligned anything. What you need to focus on is summing up the income & expenses and grouping by months

Something like this for a starter:

SELECT Month([Date]) As Month, SUM([Income]), SUM([Expense])
FROM tblIncome
LEFT JOIN tblExpenses
ON Month(tblIncome.Date) = Month(tblExpenses.Date)
GROUP BY Month([Date]);

(Note: untested! You may need to change names accordingly)

Then you have a query that you can then send to your pivottable and get it horizonally arranged automatically. One downside with the above sample is that if you have a record in expenses that doesn't have a matching record in income, it won't show in the output. For this reason, I tend to use a third table that stores all possible values and join my other tables to it, like this:

...
FROM (tblMonths
LEFT JOIN tblIncome
  ON ... )
LEFT JOIN tblExpenes
  ON ...

(note that the ()s are required by Access)

HTH.

 

[Altin]

Ive attached an example of the database tables Im working with. Would you mind showing me how the query would look as I dont fully follow the SQL statement & havent been able to get it work work for me. I get a 'syntax error in the JOIN operation' message.

Are there any good websites that you know of which explain SQL statements well?

Thanks