This is building upon an existing thread; 'inquire split data base with pivot table'
I'm trying to replicate those suggestions but coming across a couple different problems. Each month I enter a new data sheet to track downtime; that data always has the same headings and is pulled using a query from a server at our site. From that I'm making the pivot table in a seperate file to consolodate everything using MSQuery:
<data><pivot table>....Check: External data...Click [Next]
Click [Get Data]....Database: Excel files
(Browse to the file...Click [OK])
Then I've gone into the query and edited the SQL code to look like:
SELECT Year, Week, Downtime
FROM `C:\Pivot table`.`NOV-07$` `tbl`
UNION ALL
SELECT Year, Week, Downtime
FROM `C:\Pivot table`.`DEC-07$` `tbl`
UNION ALL
SELECT Year, Week, Downtime
FROM `C:\Pivot table`.`JAN-08$` `tbl`
And that seems to give me a pivot table that looks familiar; the trouble is when I try to set the table to show a 'sum' instead of 'count' it shows nothing but zeros.
Another problem is the pivot table is in a seperate file from the downtime query; when I move the sheet holding the pivot table back to the original file I'm not able to get back into the SQL option. I go into the wizard and get an error message when I click on the 'get data' button.
Hopefully this is making sense - thanks for any help!
Bookmarks