Selasa, 24 Mei 2016

[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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar