[MS_AccessPros] Crosstab Error: "Database Engine does not recognize field name or expression."

 

I have a simple union query

SELECT 

qryItemTimeStatus.ItemID AS ItemID, 

qryItemTimeStatus.ItemName, 

qryItemTimeStatus.TimeSysStatus_FK AS TimeStatus, 

qryItemTimeStatus.TotalMinutes

FROM 

qryItemTimeStatus

UNION SELECT

qryItemTimeStatus_Missing.ItemID AS ItemID, 

qryItemTimeStatus_Missing.ItemName, 

qryItemTimeStatus_Missing.TimeStatus AS TimeStatus, 

qryItemTimeStatus_Missing.TotalMinutes

FROM 

qryItemTimeStatus_Missing;

In the original query TotalMinutes is a calculated field

I then want to use a crosstab to show the total minutes for each time category

TRANSFORM 

  First(qryItemTimeStatus_All.[TotalMinutes]) AS FirstOfTotalMinutes

SELECT 

 qryItemTimeStatus_All.[ItemID], 

 qryItemTimeStatus_All.[ItemName], 

 First(qryItemTimeStatus_All.[TotalMinutes]) AS [Total Of TotalMinutes]

FROM 

 qryItemTimeStatus_All

GROUP BY 

 qryItemTimeStatus_All.[ItemID], 

 qryItemTimeStatus_All.[ItemName]

PIVOT [TimeSysStatus_FK];

This gives me an error "The microsoft engine does not recognize the field or expression [itemID]".  If However i make a table from my union query the cross tab works fine.  I have googled this error and it only points to parameter references, which I have none.  Any help would be appreciated.

__._,_.___

---

Posted by: plpoppe@gmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___