Combining Two Pivot Tables (or Data Tables) into One Pivot Table

[screech]

I have been searching all over for most of the day on the best way to do this, but I am stumped.

I have a worksheet of data in the workbook. It contains fields for "Project", "Month", "Drawings", and a bunch of other columns. I made a single pivot table from this worksheet that will count the number of drawings per project and month.
I have a second worksheet of data in the workbook. It contains fields for "Project", "Code", "Month", "Hours", and a bunch of other columns. I made a single pivot table from this worksheet that will sum the number of hours per project per month per code.
The data are unique between the two worksheets except for the common "Project" and "Month". Sometimes there are no drawings and some hours in a month, and sometimes there are no hours but drawings in a month.

What I really want is one pivot table that includes the "Project", "Month", "Drawings", and "Hours". I thought it would be easy to create a pivot table based on two other pivot tables (Drawings and Hours) or a single pivot table from data in two different worksheets... but I have been unsuccessful in doing this. I am trying not to edit the source data in the worksheets because this data is regularly updated over time and I don't want to recreate columns or formulas each time. I've even tried to make up a SQL data connection in VBA to do this. Can anyone please help?

 

[Trevor G]

Which version of MS Office are you using as you could use PowerPivot to join the 2 tables or if necessary consider moving the data to Access and you can use a Query to do the same thing then maybe export the results back to Excel if you want it back in Excel.

 

[screech]

I am using Excel 2010. I would really like to keep the data in the same sheet as the pivot table, and to have it on two different sheets. Is there any way to make a SQL command that will perform database query on tables in the same Excel workbook in order to combine the tables?