Union Query Issure

[jdashm]

I am trying to create a union query that will combine data from 3 tables. They all use the same type of unique identifier (i.e. StudentID). I keep getting a "Data Type Mismatch in Criteria Expression" when I try to execute it. I have tried running each of the queries separately without issues as well as in pairs. I only get the error when I try to run all 3 together. I think I may have narrowed down the issue to one field (TRS_Scores) which happens to be a calculated field in its query. Here is the sql I have written...

SELECT tblGateStatus_504_FR_CEDLT.[Student ID]
, tblGateStatus_504_FR_CEDLT.[Last Name] as Last_Name
, tblGateStatus_504_FR_CEDLT.[First Name] as First_Name
, tblGateStatus_504_FR_CEDLT.FR_504_CELDT_Score
, NULL as TRS_Scores
, NULL as SBAC_Math
, Null as SBAC_ELA
FROM tblGateStatus_504_FR_CEDLT

Union

SELECT tblPowerSchoolData.[Student ID]


, tblPowerSchoolData.[Last Name] as Last_Name
, tblPowerSchoolData.[First Name] as First_Name
, NULL as FR_504_CELDT_Score
, tblTeacherScoringSheets.[Average Score] AS TRS_Scores
, NULL as SBAC_Math
, Null as SBAC_ELA
FROM tblPowerSchoolData Right JOIN tblTeacherScoringSheets ON tblPowerSchoolData.[Student ID] = tblTeacherScoringSheets.[Student ID]

UNION
SELECT tblPowerSchoolData.[Student ID]
, tblPowerSchoolData.[Last Name] as Last_Name
, tblPowerSchoolData.[First Name] as First_Name
, NULL as FR_504_CELDT_Score
, NULL as TRS_Scores
, tblSBACScores.[SBAC Math 2016] as SBAC_Math
, tblSBACScores.[SBAC ELA 2016] as SBAC_ELA
FROM tblPowerSchoolData INNER JOIN tblSBACScores ON tblPowerSchoolData.[Student ID] = tblSBACScores.[Student ID];


Appreciate any help I can get.
Thanks.

[Micron]

Pretty sure that the first select query in a UNION clause 'dictates' what follows, to put it one way. So while the 3 may run independently, the subsequent ones may not conform to the first when you try to meld them. I'd check if all the data types of the fields involved in the subsequent queries are the same. The error is telling you that one of these don't conform to the first set of data types. You are probably trying to put numbers and text into the same union field, for example.