How to consolodate two tables into one

[Ben2k]

Hi, I have two simple tables that have NAME, ADD1, ADD2, ADD3, POSTCODE

I want to take all the records out of both tables and put them into one consolodated table.

I know I can copy and paste but there are thousands of records. Is there an easier way, a macro perhaps?

Thanks
Ben

 

[Norie]

There are three ways to do this

1 Using an append query add the records from one of the tables to the other.

INSERT INTO Table1 ( NAME, ADD1, ADD2, ADD3, POSTCODE )
SELECT Table2.NAME, Table2.ADD1, Table2.ADD2, Table2.ADD3, Table2.[POSTCODE ]
FROM Table2;


2 Create a make table query from one of the tables including all the fields, run that query to create a new table. Then create an append query to this new table from the other table

SELECT Table1.NAME, Table1.ADD1, Table1.ADD2, Table1.ADD3, Table1.[POSTCODE ] INTO NewTable
FROM Table1;

INSERT INTO NewTable ( NAME, ADD1, ADD2, ADD3, [POSTCODE ] )
SELECT Table2.NAME, Table2.ADD1, Table2.ADD2, Table2.ADD3, Table2.[POSTCODE ]
FROM Table2;

3 Create a union query and then create a make table query based on it

SELECT Table1.NAME, Table1.ADD1, Table1.ADD2, Table1.ADD3, Table1.[POSTCODE ]
FROM Table1

UNION

SELECT Table2.NAME, Table2.ADD1, Table2.ADD2, Table2.ADD3, Table2.[POSTCODE ]
FROM Table2;

The first two would include all the records from both tables and option 3 would only have unique records.

 

[ringmaker]

Norie - Is there a simple macro that one could create to do option 1 or 2 of your recommendation? This is a process I will need to repeat for 5 tables that I need to combine several times each month. I have my tables stored in excel and cannot combine them there since they are more than 65K rows when stacked. Would I have to create 5 separate append queries? thanks for your help.

 

[SydneyGeek]

Hi ringmaker,

Yes -- you'll need to create 5 separate Make-Table or Append queries -- let's call them App1, App2 .... App5

You can then create a macro or a piece of VBA code which runs each of the 5 queries in succession -- you can also set it to suppress the warnings about modifying records. This can then be attached to a button.

Denis

 

[Norie]

Ringmaker

You could do as Denis says or you could dynamically create the queries and then run them.

eg

Sub AppendAddresses()
Dim strSQL As String

    strSQL = "INSERT INTO Table1 ( NAME, ADD1, ADD2, ADD3, POSTCODE ) " & _
             "SELECT Table2.NAME, Table2.ADD1, Table2.ADD2, Table2.ADD3, Table2.[POSTCODE]" & _
             "FROM Table2;"
    
    DoCmd.RunSQL strSQL
    
End Sub

 

[ringmaker]

Denis and Norie - Many thanks to both of you. I will give your suggestions a whirl. Thanks