Updating Report that uses a query with changing fields

[EM2021]

I've built a report based on queried data that has conditions 1,2,3,4,5,6,7,8.

I've now got new data that needs to use the same report but only has conditions 1, 2, and 3 and am getting "No such field in the field list". This makes sense being that 4,5,6,7 and 8 do not exist.

My question is, how/where do I code these conditions? If I update the crosstab query to say IIF is null, then the report only creates with the existing crosstab data.

Hope this makes sense. Thanks in advance.

 

[theDBguy]

Hi. I think we'll need more information. Can you post a sample db?

 

[EM2021]

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.

 

[theDBguy]

PIVOT [Fund Split Union for Region Summary].Region;

Try using the Column Headings property to add all the regions (missing or not). For example

PIVOT [Fund Split Union for Region Summary].Region In("Region1", "Region2", "Region3", etc.)

Hope that helps...

 

[EM2021]

Try using the Column Headings property to add all the regions (missing or not). For example

PIVOT [Fund Split Union for Region Summary].Region In("Region1", "Region2", "Region3", etc.)

Hope that helps...

That did the trick! I appreciate you.

 

[theDBguy]

That did the trick! I appreciate you.

You're welcome. Glad we could assist. Good luck with your project.

 

[Pat Hartman]

OK, now that you've done that you need to somewhere make a "note to self" BECAUSE if a new region is ever added, the crosstab will ignore it because of the fixed column headings. I handle variable crosstabs differently but this method is simple. Just don't forget to change the query if a new region gets added.