I had a similar problem as this. I looked at joining the table but the performance was just to slow. The following solution is the most basic, which is you check the date for which you are going to select and base your selection statement on the correct table to fit the date. So more recent dates search the actual table, older dates search the archive table. You have to be accurate with your cut off date with this. The one downfall is that if you do a date range you risk an issue if someone tries to span the dates. That would require a join of the two tables.

Example of selection based on date below.

The selection compares todays date to the date you pass in (From_DateVar) and then decides if it is in the window to select from the new or old data.

IF DateDiff("d",From_DateVar,DateAdd("yyyy",-1, date())) < 0 THEN
sql1 = "SELECT count(Actual_Count) AS BXCount, sum(Actual_1stWt+Actual_2ndWt+Actual_3rdWt) AS Weight FROM Batch WHERE Batch_DateTime >= '" & From_DateVar & " 05:00:00 AM' AND Batch_DateTime <= '" & From_DateVar & " 12:59:00 PM'"
ELSE
sql1 = "SELECT count(Actual_Count) AS BXCount, sum(Actual_1stPowderWt+Actual_2ndPowderWt+Actual_3rdPowderWt) AS Weight FROM Archive_Batch WHERE Batch_DateTime >= '" & From_DateVar & " 05:00:00 AM' AND Batch_DateTime <= '" & From_DateVar & " 12:59:00 PM'"
END IF

Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open sql1, Session("DB_conn")