SQL Union All to display old data with new data?

Question

Ok, 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));

Thanks in advance for any attempts at trying to help solve my problem^^

Answer 1

Yes, Access supports UNION ALL. A simple example would be:

select foo from OldData
union all
select foo from NewData

Answer 2

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.)

Answer 3

I would emphasize @le dorfier's comment about archiving. Most Access applications with a Jet data store don't need data to be archived. A client of mine runs a billing system for 6 parking garages that I built for her back in 1997-98 (it's been upgraded, extended and revised extensively since then). It includes data for that entire period (200-300 invoices for each month for each garage) and it works just as fast now as it did back then. Part of that is the increase in the performance of PCs (i.e., with the same amount of data and 1997-era PCs, it would probably be very slow), but it still works just fine.

If speed started to be an issue, we'd probably move the back end to SQL Server Express. The last thing we'd contemplate would be archiving the data.

Another client of mine insisted on archiving their inactive data, and then I had to reprogram the ADD NEW RECORD function to check against the archive (because of the requirements of the app, it's crucial that the same person not have a new record created, but instead have it retrieved from the archive). This vastly slowed down the process of adding new records. Now I'm trying to convince them to "de-archive" their data, because it hasn't actually helped them in any way and has made things work more slowly during daily operations.

Answer 4

Two comments not related to your question.

1) What's the purpose of the Q1 to Q32 fields? I'm thinking that maybe those could be somehow redesigned and/or normalized.

2) I see no reason to use naming standards on object names. Such as tbl, fld, frm, qry and so forth. You pretty much know what kind of an object they are by the context in the code. If in the various database container windows those are pretty obvious as well.

That said I do somewhat use the variable naming conventions in my VBA code just to help keep those clear.

See Tony's Object Naming Conventions and Tony's Table and Field Naming Conventions for more details.

I quite expect some folks to strenuously disagree with my second comment and to thumbs down my posting.