Hi, Mark.
Is there any way I can treat these tables as
a single 'appended' object without actually creating a new table, and taking
up loads of space?
Don't use an append query for this because, as you mentioned, it takes up a
lot of space. Use a UNION query that only grabs the three tables for the
previous three months. You haven't given the names of these tables, so for
the purposes of this example, I'll assume they're named tblStuff_Nov,
tblStuff_Oct, tblStuff_Sep, tblStuff_Aug, and so on -- basically with the
three letter abbreviation of each month concatenated to the name of the
table. If yours is different, then you'll need to modify the code below.
First, create a query and name it qryLast3Mos. It doesn't matter what the
SQL is, because it's going to be changed in VBA code. Next, set a Reference
to the DAO library if you haven't already. Create a new form and place a
button named ShowLast3MosBtn on it. Paste the following code into the form's
VBA module:
Private Sub ShowLast3MosBtn_Click()
On Error GoTo ErrHandler
Dim qry As QueryDef
Dim sMon1 As String
Dim sMon2 As String
Dim sMon3 As String
sMon1 = Format(DateAdd("m", -1, Date), "mmm")
sMon2 = Format(DateAdd("m", -2, Date), "mmm")
sMon3 = Format(DateAdd("m", -3, Date), "mmm")
Set qry = CurrentDb().QueryDefs("qryLast3Mos")
qry.sql = "SELECT * " & _
"FROM tblStuff_" & sMon1 & _
" UNION SELECT * " & _
"FROM tblStuff_" & sMon2 & _
" UNION SELECT * " & _
"FROM tblStuff_" & sMon3 & ";"
DoCmd.OpenQuery "qryLast3Mos"
CleanUp:
Set qry = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in ShowLast3MosBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub ' ShowLast3MosBtn_Click( )
Save and compile the code. Open the form in Form View and select the
ShowLast3MosBtn button to display the records in all three tables for the
previous three months, not including the current month.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
