wouter.vanmeert
New Member
Dear all
In order to create a flexible basis for multiple dashboards/reports... I have created a database in access 2007 with a number of linked excel 2007 tables. I have two basic input tables: one for actual costs and one for budget costs. Each table in excel has five columns:
> DATE: The date on which the entry has been done
> COSTCENTER: the department in my company for which costs have been made
> ACCOUNT: the specific account on which costs have been booked
> AMOUNT: the amount
> TYPE: A or B for actual or budget (depending on the input file)
I combine these two input tables in Access 2007 with a UNION ALL query (in order to combine the two tables without losing any data)and then use a select query to lookup some additional details to my basic data (such as the account name, responsible for the combination of costcenter and account, etc.).
I then create a pivot table in Excel 2003 from this database.
> Filter field: DATE
> Column field: COSTCENTER
> Row field: ACCOUNTS
> Data field: AMOUNT
The problem that occurs is that each cost center is entered twice in my pivot. For example, if I have a costcenter named "AB", my pivot table will show two columns with heading AB for the same account. I did some further analysis and I found out that the double occurence of the costcenters is exactly the same as the split between actual and budget.
Additional info:
- Tthe formatting in the input Excel files is the same in both input files;
- The formatting of each table (in input tables and queries) is the same in Access;
- When I create the same pivot in access, the problem does not occur.
Can anyone help me to point out mistakes in the way I created this system? I'm a bit of a newbie to Access so maybe I made a rookie mistake there...
Thanks!
In order to create a flexible basis for multiple dashboards/reports... I have created a database in access 2007 with a number of linked excel 2007 tables. I have two basic input tables: one for actual costs and one for budget costs. Each table in excel has five columns:
> DATE: The date on which the entry has been done
> COSTCENTER: the department in my company for which costs have been made
> ACCOUNT: the specific account on which costs have been booked
> AMOUNT: the amount
> TYPE: A or B for actual or budget (depending on the input file)
I combine these two input tables in Access 2007 with a UNION ALL query (in order to combine the two tables without losing any data)and then use a select query to lookup some additional details to my basic data (such as the account name, responsible for the combination of costcenter and account, etc.).
I then create a pivot table in Excel 2003 from this database.
> Filter field: DATE
> Column field: COSTCENTER
> Row field: ACCOUNTS
> Data field: AMOUNT
The problem that occurs is that each cost center is entered twice in my pivot. For example, if I have a costcenter named "AB", my pivot table will show two columns with heading AB for the same account. I did some further analysis and I found out that the double occurence of the costcenters is exactly the same as the split between actual and budget.
Additional info:
- Tthe formatting in the input Excel files is the same in both input files;
- The formatting of each table (in input tables and queries) is the same in Access;
- When I create the same pivot in access, the problem does not occur.
Can anyone help me to point out mistakes in the way I created this system? I'm a bit of a newbie to Access so maybe I made a rookie mistake there...
Thanks!