Marlon Marlon - 2 months ago 9
SQL Question

SQL Union All to display old data with new data?

In MS Access, I have some reports that use some queries, to show data, within a date range. The queries use aliases, if, sum, and avg functions, and join multiple tables to get its data.

I'd like to know if i could use a UNION ALL, with a table that has all the needed fields, to display this new data from this table, along with the older data, if someone selects a range that spans the new and the old.

Here's an example "old" query:

SELECT tblAssessment.fldValid, tblATraining.fldTID, tblATraining.fldTCrsID,
tblCourses.fldCrsName, [fldCrsHrs]/8 AS Days, tblATraining.fldTLocAbr,
tblDistrict.fldDistAbr, tblRegion.fldRegName, tblATraining.fldTDateStart,
tblATraining.fldTDateEnd, tblATraining.fldTEnrolled, tblATraining.fldTPID,
tblPersonnel.fldPName, tblAssessment.fldTrngSID, tblAssessment.Q1,
IIf([fldValid]=True,IIf([Q1]>0,1,0),0) AS Q1Valid, tblAssessment.Q2,
IIf([fldValid]=True,IIf([Q2]>0,1,0),0) AS Q2Valid, tblAssessment.Q3,
IIf([fldValid]=True,IIf([Q3]>0,1,0),0) AS Q3Valid, tblAssessment.Q4,
IIf([fldValid]=True,IIf([Q4]>0,1,0),0) AS Q4Valid, tblAssessment.Q5,
IIf([fldValid]=True,IIf([Q5]>0,1,0),0) AS Q5Valid, tblAssessment.Q6,
IIf([fldValid]=True,IIf([Q6]>0,1,0),0) AS Q6Valid, tblAssessment.Q7,
IIf([fldValid]=True,IIf([Q7]>0,1,0),0) AS Q7Valid, tblAssessment.Q8,
tblAssessment.Q9,
IIf([fldValid]=True,IIf([Q9]>0,1,0),0) AS Q9Valid, tblAssessment.Q10,
IIf([fldValid]=True,IIf([Q10]>0,1,0),0) AS Q10Valid, tblAssessment.Q11,
IIf([fldValid]=True,IIf([Q11]>0,1,0),0) AS Q11Valid, tblAssessment.Q12,
IIf([fldValid]=True,IIf([Q12]>0,1,0),0) AS Q12Valid, tblAssessment.Q13,
tblAssessment.Q14,
IIf([fldValid]=True,IIf([Q14]>0,1,0),0) AS Q14Valid, tblAssessment.Q15,
IIf([fldValid]=True,IIf([Q15]>0,1,0),0) AS Q15Valid, tblAssessment.Q16,
IIf([fldValid]=True,IIf([Q16]>0,1,0),0) AS Q16Valid, tblAssessment.Q17,
IIf([fldValid]=True,IIf([Q17]>0,1,0),0) AS Q17Valid, tblAssessment.Q18,
IIf([fldValid]=True,IIf([Q18]>0,1,0),0) AS Q18Valid, tblAssessment.Q19,
IIf([fldValid]=True,IIf([Q19]>0,1,0),0) AS Q19Valid, tblAssessment.Q20,
tblAssessment.Q21,
IIf([fldValid]=True,IIf([Q21]>0,1,0),0) AS Q21Valid, tblAssessment.Q22,
IIf([fldValid]=True,IIf([Q22]>0,1,0),0) AS Q22Valid, tblAssessment.Q23,
IIf([fldValid]=True,IIf([Q23]>0,1,0),0) AS Q23Valid, tblAssessment.Q24,
IIf([fldValid]=True,IIf([Q24]>0,1,0),0) AS Q24Valid, tblAssessment.Q25,
IIf([fldValid]=True,IIf([Q25]>0,1,0),0) AS Q25Valid, tblAssessment.Q26,
IIf([fldValid]=True,IIf([Q26]>0,1,0),0) AS Q26Valid, tblAssessment.Q27,
IIf([fldValid]=True,IIf([Q27]>0,1,0),0) AS Q27Valid, tblAssessment.Q28,
IIf([fldValid]=True,IIf([Q28]>0,1,0),0) AS Q28Valid, tblAssessment.Q29,
tblAssessment.Q30,
tblAssessment.Q31, tblAssessment.Q32
FROM ((tblDistrict
LEFT JOIN tblRegion ON tblDistrict.fldDRegID = tblRegion.fldRegID)
RIGHT JOIN (((tblATraining
LEFT JOIN tblCourses ON tblATraining.fldTCrsID = tblCourses.fldCrsID)
LEFT JOIN tblPersonnel ON tblATraining.fldTPID = tblPersonnel.fldPID)
LEFT JOIN tblLocations ON tblATraining.fldTLocAbr = tblLocations.fldLID) ON tblDistrict.fldDistAbr = tblATraining.fldTDistAbr)
LEFT JOIN tblAssessment ON tblATraining.fldTID = tblAssessment.fldTrngCID
WHERE (((tblAssessment.fldValid)=True)
AND ((tblATraining.fldTCrsID) Like [forms]![fdlgRptCriteria].[selCrsCd])
AND ((tblATraining.fldTDateStart) Between [forms]![fdlgRptCriteria].[seldate1] And [forms]![fdlgRptCriteria].[seldate2])
AND ((tblAssessment.fldTrngSID) Is Not Null));

Answer

Yes, but you might consider creating a new querydef for the union.

(You'll need to use the SQL design window last time I checked.)

It would be approximately:

    SELECT
    tblATraining.fldTID,
    tblATraining.fldTCrsID,
    tblATraining.fldTLocAbr,
    tblATraining.fldTDateStart,
    tblATraining.fldTDateStart,
    tblATraining.fldTDateEnd,
    tblATraining.fldTEnrolled,
    tblATraining.fldTPID

    UNION ALL

    tblATrainingArchive.fldTID,
    tblATrainingArchive.fldTCrsID,
    tblATrainingArchive.fldTLocAbr,
    tblATrainingArchive.fldTDateStart,
    tblATrainingArchive.fldTDateStart,
    tblATrainingArchive.fldTDateEnd,
    tblATrainingArchive.fldTEnrolled,
    tblATrainingArchive.fldTPID

and name it something like tblATrainingUnion.

Then just plug that into your existing query wherever you have tblATraining.

Note: Many people also tend to create an archive table sooner than it really makes sense - you might consider just leaving it all in the main table, and make the split when you can actually measure the difference. (You may already have reached that point and done that; and it can make more sense with Access than with say SQL Server.)

Comments