I created a button for users that generates multiple crosstabs, and then combine the crosstabs, into a single table that auto-exports to Excel. Unfortunately, because of the data, sometimes the crosstabs have different numbers of columns (based on normal variation in the data). Because of this, the user sometimes gets queried for parameters (up to 168 times for 14 defect types across 12 categories) when the code tries to select from fields that don't exist. Is there a way to have the code use defaults in lieu of querying the user?
This is the SQL code I am using (abbreviated slightly) to merge two crosstabs but I'd really like it to do so without bothering the user. Specifically, if tblMajorDefectsCrossTabX doesn't have a field for [Major 1], how do I make it default to zeroes, or nulls, instead of bothering the user?
mySql = "SELECT tblMajorDefectsCrossTabX.[Contractor Dept] AS [Contractor Dept], " & _
"tblMajorDefectsCrossTabX.[Major 1], tblMajorDefectsCrossTabX.[Major 7], " & _
"tblMinorDefectsCrossTabX.[Minor 1], tblMinorDefectsCrossTabX.[Minor 7] " & _
"FROM tblMajorDefectsCrossTabX LEFT JOIN tblMinorDefectsCrossTabX " & _
"ON tblMajorDefectsCrossTabX.[Contractor Dept] = tblMinorDefectsCrossTabX.[Contractor Dept] " & _
"UNION " & _
"SELECT tblMinorDefectsCrossTabX.[Contractor Dept] AS [Contractor Dept], " & _
"tblMajorDefectsCrossTabX.[Major 1], tblMajorDefectsCrossTabX.[Major 7], " & _
"tblMinorDefectsCrossTabX.[Minor 1], tblMinorDefectsCrossTabX.[Minor 7] " & _
"FROM tblMajorDefectsCrossTabX RIGHT JOIN tblMinorDefectsCrossTabX " & _
"ON tblMajorDefectsCrossTabX.[Contractor Dept] = tblMinorDefectsCrossTabX.[Contractor Dept];"
On Error Resume Next
db.QueryDefs.Delete "qryX" 'Remove temporary query if exists
Set QD = db.CreateQueryDef("qryX", mySql) 'create temporary query
DoCmd.RunSQL "SELECT * INTO tblMergeDefectCrossTabX FROM qryX;"
