Morning everyone, I'm trying to get a pivot query to work on the below data source. My aim is to get the CarId to be the column headers and the axle serial as the data source all on 1 row. the table name MTDN is actually a query that gets the information I need into 1 place.
Table: should be an attachment as the copy and paste I tried, didn't work!
Syntax:
SELECT [CarId],[Axle Serial]
FROM [MTDN] AS SourceTable
PIVOT (MAX([Axle Serial]) FOR [CarId]
IN([110**-1],[110**-2],[110**-3],[110**-4],[120**-1],[120**-2],[120**-3],[120**-4],[130**-1],[130**-2],[130**-3],[130**-4],[140**-1],[140**-2],[140**-3],[140**-4]))
AS Resultsheet;
I cant see what I've done wrong but it wont let me save the query, access keeps saying there id an error in the from clause and when I ok it, the word PIVOT is highlighted!
I am quite new to SQL so I've probably made a basic error somewhere but I cant see it amongst all the online examples. I can get the data to pivot in access but when I try to use the data in a report, it automatically reverts back to the table view!
any help will be appreciated.
Thanks
Fikus
Table: should be an attachment as the copy and paste I tried, didn't work!
Syntax:
SELECT [CarId],[Axle Serial]
FROM [MTDN] AS SourceTable
PIVOT (MAX([Axle Serial]) FOR [CarId]
IN([110**-1],[110**-2],[110**-3],[110**-4],[120**-1],[120**-2],[120**-3],[120**-4],[130**-1],[130**-2],[130**-3],[130**-4],[140**-1],[140**-2],[140**-3],[140**-4]))
AS Resultsheet;
I cant see what I've done wrong but it wont let me save the query, access keeps saying there id an error in the from clause and when I ok it, the word PIVOT is highlighted!
I am quite new to SQL so I've probably made a basic error somewhere but I cant see it amongst all the online examples. I can get the data to pivot in access but when I try to use the data in a report, it automatically reverts back to the table view!
any help will be appreciated.
Thanks
Fikus
Attachments
Last edited: