Here's my union query:
(SELECT [Campaign Summary].Gf_CnBio_ID AS ConstitID, [Campaign Summary].Gf_Fnds_1_01_Amount AS SplitAmount, [Campaign Summary].Gf_Cmps_1_01_Campaign_ID AS CampaignID, [Campaign Summary].Gf_Cmps_1_01_Description AS CampaignDescription, [Campaign Summary].Gf_Cmps_1_01_Cm_Overall_goal AS CampaignGoal, [Campaign Summary].Gf_Cmps_1_01_CmAtrCat_1_01_Description AS CampaignGroup, [Campaign Summary].Gf_Cmps_1_01_CM_Campaign_category AS CampaignCategory, ([Campaign Summary].Gf_Fnds_1_01_Amount - [Campaign Summary].Gf_WO_1_01_Amount) AS AmountminusWO, IIF([AmountminusWO] Is Null, [SplitAmount], [AmountminusWO]) AS FinalSplitAmount, [Campaign Summary].Gf_CnAdrSal_Addressee AS PrimaryAddressee, [Campaign Summary].Gf_CnAdrSal_Salutation AS PrimarySalutation, [Campaign Summary].Gf_CnAdrPrf_Addrline1 AS Add1, [Campaign Summary].Gf_CnAdrPrf_Addrline2 AS Add2, [Campaign Summary].Gf_CnAdrPrf_City AS City, [Campaign Summary].Gf_CnAdrPrf_State AS State, [Campaign Summary].Gf_CnAdrPrf_Region AS Region
FROM [Campaign Summary]
WHERE ([Campaign Summary].Gf_Fnds_1_01_Amount Is Not Null) and ([Campaign Summary].Gf_Cmps_1_01_Campaign_ID Is Not Null))
UNION (SELECT [Campaign Summary].Gf_CnBio_ID AS ConstitID, [Campaign Summary].Gf_Fnds_1_02_Amount AS SplitAmount, [Campaign Summary].Gf_Cmps_1_02_Campaign_ID AS CampaignID, [Campaign Summary].Gf_Cmps_1_02_Description AS CampaignDescription, [Campaign Summary].Gf_Cmps_1_02_Cm_Overall_goal AS CampaignGoal, [Campaign Summary].Gf_Cmps_1_02_CmAtrCat_1_01_Description AS CampaignGroup, [Campaign Summary].Gf_Cmps_1_02_CM_Campaign_category AS CampaignCategory, ([Campaign Summary].Gf_Fnds_1_02_Amount - [Campaign Summary].Gf_WO_1_01_Amount) AS AmountminusWO, IIF([AmountminusWO] Is Null, [SplitAmount], [AmountminusWO]) AS FinalSplitAmount, [Campaign Summary].Gf_CnAdrSal_Addressee AS PrimaryAddressee, [Campaign Summary].Gf_CnAdrSal_Salutation AS PrimarySalutation, [Campaign Summary].Gf_CnAdrPrf_Addrline1 AS Add1, [Campaign Summary].Gf_CnAdrPrf_Addrline2 AS Add2, [Campaign Summary].Gf_CnAdrPrf_City AS City, [Campaign Summary].Gf_CnAdrPrf_State AS State, [Campaign Summary].Gf_CnAdrPrf_Region AS Region
FROM [Campaign Summary]
WHERE ([Campaign Summary].Gf_Fnds_1_02_Amount Is Not Null) and ([Campaign Summary].Gf_Cmps_1_02_Campaign_ID Is Not Null))
Crosstab:
TRANSFORM Sum([Fund Split Union for Region Summary].FinalSplitAmount) AS SumOfFinalSplitAmount
SELECT [Fund Split Union for Region Summary].CampaignID, [Fund Split Union for Region Summary].CampaignDescription, [Fund Split Union for Region Summary].CampaignCategory, [Fund Split Union for Region Summary].CampaignGoal, Sum([Fund Split Union for Region Summary].FinalSplitAmount) AS [Total Of FinalSplitAmount]
FROM [Fund Split Union for Region Summary]
WHERE ((([Fund Split Union for Region Summary].Region)=IIf([Region] Is Null,0,[Region])))
GROUP BY [Fund Split Union for Region Summary].CampaignID, [Fund Split Union for Region Summary].CampaignDescription, [Fund Split Union for Region Summary].CampaignCategory, [Fund Split Union for Region Summary].CampaignGoal
PIVOT [Fund Split Union for Region Summary].Region;
Problem is, when the data changes to only have some regions, I get the "No such field in the field list" when trying to open the report.