Dear all thank you so much for all your help and patience with me.

I have it working !!!

thank you so much.

HI There

I have being through this and shortened the sql but still get the same error.

SELECT PPD.PupilID, PPD.Forename, PPD.Surname, CAST(REPLACE(CM.ColumnTitle, 'Week ', '') AS INT) AS 'WeekNo', CAST(MSDN.Data AS INT)...

HI

I did follow what you taught me using this code

SELECT PPD.PupilID, PPD.Forename, PPD.Surname, FL.[Description] AS 'Form', HL.[Description] AS 'House', CAST(REPLACE(CM.ColumnTitle, 'Week ', '') AS INT) AS 'WeekNo', SUM(CAST(MSDN.Data AS...

i have one more question....

i have this select

SELECT PPD.PupilID, PPD.Forename, PPD.Surname, FL.[Description] AS 'Form', HL.[Description] AS 'House', CAST(REPLACE(CM.ColumnTitle, 'Week ', '') AS INT) AS 'WeekNo',CAST(MSDN.Data AS INT) AS 'Data' FROM...

Thank you so much for the help. really appreciate it.

Excellent.