Jarlisle
03-24-2014, 10:15 AM
I'm trying to get data into PowerPivot from an Access db. I'm not very proficient with access and so I'm out of my league a little, but I created a query in the Access db and now I want that result data to be pulled into PowerPivot. I've tried to select the "table" (or in this case the query result) that I want, but I get the following error:
"The 'rev.Revenue' column does not exist in the rowset.
An error occurred while processing table 'MEA_Revenue'.
The current operation was cancelled because another operation in the transaction failed."
When I try to create a query to pull the data in (SELECT * FROM MEA_Revenue) I get the following error:
"An unknown error occurred converting to the data type table 'Query' column 'Discount'.
The current operation was cancelled because another operation in the transaction failed."
I've tried to go through the queries in Access and format the column 'Discount' to double using CDbl(Discount), but this doesn't seem to make any difference.
Does anyone have any insight?
Here are my access queries:
Rate_Card:
SELECT Team, Season, Department, Product, Item, CInt(MIN(Number)) AS Min_Number, CCur(MIN(Revenue_Rate)) AS Min_Revenue_Rate, CCur(MIN(Revenue_Rate)/MIN(Number)) AS Rate_Card
FROM Revenue
GROUP BY Team, Season, Department, Product, Item;
Contract_Rate:
SELECT rev.Team, rev.Season, rev.Department, rev.Product, rev.Item, rc.Rate_Card, rev.Contract, CCur(SUM(rev.Revenue)) AS Revenue, CInt(SUM(rev.Number)) AS [Number], CCur(IIF(SUM(rev.Revenue)/SUM(rev.Number)<0,SUM(rev.Revenue)/SUM(rev.Number),SUM(rev.Number)*IIF(rc.Rate_Card=0,SUM(rev.Revenue)/SUM(rev.Number),rc.Rate_Card))) AS Contract_Rate_Card
FROM Revenue AS rev LEFT JOIN Rate_Card AS rc ON (rev.Team=rc.Team) AND (rev.Season=rc.Season) AND (rev.Department=rc.Department) AND (rev.Product=rc.Product) AND (rev.Item=rc.Item);
Discount:
SELECT Contract, CDbl(SUM(Revenue)/SUM(Contract_Rate_Card)) AS Discount
FROM Contract_Rate
GROUP BY Contract;
MEA_Revenue:
SELECT rev.Revenue_Id, rev.Team, rev.Season, rev.Sponsor, rev.Contract, rev.Salesperson, rev.Serviceperson, rev.Account_Status, rev.Sales_Status, rev.Review_Status, rev.Approval_Status, rev.Start_Date, rev.End_Date, rev.Department, rev.Product, rev.Item, rev.Description, rev.Quantity, rev.Events, rev.Number, rev.Revenue, rev.Revenue_Rate, rev.Revenue_Variance, rev.Expense, rev.Expense_Rate, rev.Expense_Variance, rev.Profit, rev.Profit_Rate, rev.Profit_Variance, rev.Agency, rev.Trade, rev.Name, CDbl(dsc.Discount) AS Discount, CCur(cr.Contract_Rate_Card/cr.Number*rev.Number) AS Contract_Rate_Card, CCur(Round(IIf(rev.Department Not In ('Due to Rodeo','Due To Theatres','Theatre') And rev.Item Not In ('Concession Fee','Rodeo Sponsorship','Season Food Credit','Triple Play Picnic'),dsc.Discount*cr.Contract_Rate_Card/cr.Number*rev.Number,rev.Revenue),2)) AS Allocated_Rev
FROM (Revenue AS rev LEFT JOIN Discount AS dsc ON rev.Contract = dsc.Contract) LEFT JOIN Contract_Rate AS cr ON (rev.Contract = cr.Contract) AND (rev.Item = cr.Item) AND (rev.Product = cr.Product) AND (rev.Department = cr.Department) AND (rev.Season = cr.Season) AND (rev.Team = cr.Team);
"The 'rev.Revenue' column does not exist in the rowset.
An error occurred while processing table 'MEA_Revenue'.
The current operation was cancelled because another operation in the transaction failed."
When I try to create a query to pull the data in (SELECT * FROM MEA_Revenue) I get the following error:
"An unknown error occurred converting to the data type table 'Query' column 'Discount'.
The current operation was cancelled because another operation in the transaction failed."
I've tried to go through the queries in Access and format the column 'Discount' to double using CDbl(Discount), but this doesn't seem to make any difference.
Does anyone have any insight?
Here are my access queries:
Rate_Card:
SELECT Team, Season, Department, Product, Item, CInt(MIN(Number)) AS Min_Number, CCur(MIN(Revenue_Rate)) AS Min_Revenue_Rate, CCur(MIN(Revenue_Rate)/MIN(Number)) AS Rate_Card
FROM Revenue
GROUP BY Team, Season, Department, Product, Item;
Contract_Rate:
SELECT rev.Team, rev.Season, rev.Department, rev.Product, rev.Item, rc.Rate_Card, rev.Contract, CCur(SUM(rev.Revenue)) AS Revenue, CInt(SUM(rev.Number)) AS [Number], CCur(IIF(SUM(rev.Revenue)/SUM(rev.Number)<0,SUM(rev.Revenue)/SUM(rev.Number),SUM(rev.Number)*IIF(rc.Rate_Card=0,SUM(rev.Revenue)/SUM(rev.Number),rc.Rate_Card))) AS Contract_Rate_Card
FROM Revenue AS rev LEFT JOIN Rate_Card AS rc ON (rev.Team=rc.Team) AND (rev.Season=rc.Season) AND (rev.Department=rc.Department) AND (rev.Product=rc.Product) AND (rev.Item=rc.Item);
Discount:
SELECT Contract, CDbl(SUM(Revenue)/SUM(Contract_Rate_Card)) AS Discount
FROM Contract_Rate
GROUP BY Contract;
MEA_Revenue:
SELECT rev.Revenue_Id, rev.Team, rev.Season, rev.Sponsor, rev.Contract, rev.Salesperson, rev.Serviceperson, rev.Account_Status, rev.Sales_Status, rev.Review_Status, rev.Approval_Status, rev.Start_Date, rev.End_Date, rev.Department, rev.Product, rev.Item, rev.Description, rev.Quantity, rev.Events, rev.Number, rev.Revenue, rev.Revenue_Rate, rev.Revenue_Variance, rev.Expense, rev.Expense_Rate, rev.Expense_Variance, rev.Profit, rev.Profit_Rate, rev.Profit_Variance, rev.Agency, rev.Trade, rev.Name, CDbl(dsc.Discount) AS Discount, CCur(cr.Contract_Rate_Card/cr.Number*rev.Number) AS Contract_Rate_Card, CCur(Round(IIf(rev.Department Not In ('Due to Rodeo','Due To Theatres','Theatre') And rev.Item Not In ('Concession Fee','Rodeo Sponsorship','Season Food Credit','Triple Play Picnic'),dsc.Discount*cr.Contract_Rate_Card/cr.Number*rev.Number,rev.Revenue),2)) AS Allocated_Rev
FROM (Revenue AS rev LEFT JOIN Discount AS dsc ON rev.Contract = dsc.Contract) LEFT JOIN Contract_Rate AS cr ON (rev.Contract = cr.Contract) AND (rev.Item = cr.Item) AND (rev.Product = cr.Product) AND (rev.Department = cr.Department) AND (rev.Season = cr.Season) AND (rev.Team = cr.Team);