Not sure where to put this, so trying here 
Okay I have a query that combines information from two tables and want to make a Pivot Table with the data. Here is some example data:
The Pivot Table is relatively simple. Just comparing actual quantity ordered with the forecasted order quantity.
My problem is that once I get into dragging the fields into the Pivot Table, it will not give me the options to break down the date value to Quarter, Year, Month, Day, etc.
It does if I use either of the source tables, but when I combine the tables with a query I no longer have that option. What can I do to fix it?
To get the two tables joined, I simply am matching on the Part Code, Date, and CustomerID (I didn't list the customer above though). One table stores the actual ordered amounts which is imported from a report we get. The other table is where you enter the forecasted amounts. There dates are by month only really - they are entered on the first of the month and that's it.
The join is a left join - everything is taken from the actual orders, and only the forecasts which match up - not every transaction will have a forecast.
Any ideas?
Okay I have a query that combines information from two tables and want to make a Pivot Table with the data. Here is some example data:
Code:
PartCode ProductType TransDate ActualMT ForecastMT
503220 OTHER 10/1/2008 2.7255 3
503226 OTHER 3/1/2010 3.6806 3
503226 OTHER 6/1/2011 1.9138 2
503226 OTHER 10/1/2010 4.6162 4The Pivot Table is relatively simple. Just comparing actual quantity ordered with the forecasted order quantity.
My problem is that once I get into dragging the fields into the Pivot Table, it will not give me the options to break down the date value to Quarter, Year, Month, Day, etc.
It does if I use either of the source tables, but when I combine the tables with a query I no longer have that option. What can I do to fix it?
To get the two tables joined, I simply am matching on the Part Code, Date, and CustomerID (I didn't list the customer above though). One table stores the actual ordered amounts which is imported from a report we get. The other table is where you enter the forecasted amounts. There dates are by month only really - they are entered on the first of the month and that's it.
The join is a left join - everything is taken from the actual orders, and only the forecasts which match up - not every transaction will have a forecast.
Any ideas?
