Issues getting data in PowerPivot from Access

[Jarlisle]

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);

[Jan Karel Pieterse]

Does the query work when you pull it "in" using MSQuery?

[Jarlisle]

I haven't tried it using MSQuery, but I was actually able to work my way through it. Since I had some calculations in the query some of them ended up dividing by 0 and so it through an error. Access was able to handle this and just showed #Error in the field, but PowerPivot didn't like it and gave me an unknown error. As soon as I was able to adjust the query to account for those that would divide by 0 it worked.

[Jan Karel Pieterse]

Excellent, thanks for letting us know!