Solved

ACCESS VBA to merge  tables with additional field

Posted on 2014-10-20
6
610 Views
Last Modified: 2014-10-22
Hi ,

    I have 10 tables of the identical structure Tb1, Tb2 ..TB10
 
   The  TB_merge is a table to merge all the rows in the 10 tables . But  with an  additional column called  Origin .  This is to identify the origin of the row ( This  For each row  from Tb1,  the column  Origin  should be filled with Tb2 .. For each row from Tb2 the column should be filled with Tb2 )

 How can I do this in a VBA ( or any other means )

 Thanks
0
Comment
Question by:Sam OZ
6 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 150 total points
ID: 40391628
Hi Sam,
An INSERT INTO .... SELECT... UNION query may be a possible option:
The following is an example

INSERT TB_merge
SELECT 
*,
'TB_01' AS [Origin]
FROM
[Tb1]
UNION
SELECT 
*,
'TB_02' AS [Origin]
FROM
[Tb2]
UNION
SELECT 
*,
'TB_03' AS [Origin]
FROM
[Tb3]
.....
.....

Open in new window



So in MS Access you can create a new query and choose View | SQL to get to a free format SQL window and then you can create SQL similar to that above to SELECT all rows from the different source tables and INSERT INTO TB_merge
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 40391678
I would not bother with the UNIONs; just do ten separate queries, one for each table that you want to append.

If this is a one time thing, you can execute them manually.

If via VBA, use CurrentDB().execute <SQL>  , dbFailOnError

to execute the SQL directly

or:

Dim qryDef as DAO.Querydef

set qrydef = CurrentDB().querydef("<name of query>")

qrydef.execute, dbFailOnError

Jim.
0
 

Author Comment

by:Sam OZ
ID: 40391690
Thanks for the comments .  I was looking for a way of automation than manual . Because the number of Tables can change later ( But I can assume it will be tables starting with TB_)

 Infact, My real need is to have  a column by name Origin  to identify from which Table  the row is originated from
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40391733
Or another option, since you don't know how many tables you are going to have, write a VBA subroutine which loops through the tables (you could either pass the table names in as a parameter array or if they all have the same name with the exception of the value 01-10, then you could just build the table name at run time.

Adding the Origin is actually the easy part.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 40391825
try these codes

Dim td As DAO.TableDef, db As DAO.Database
Set db = CurrentDb
For Each td In db.TableDefs
If td.Name <> "TB_Merge" Then
    If Left(td.Name, 3) = "TB_" Then
        CurrentDb.Execute "Insert into TB_merge select [" & td.Name & "].* From [" & td.Name & "]"
        CurrentDb.Execute "Update Tb_merge set origin='" & td.Name & "' where Origin is null"
    End If
End If
Next

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 40391927
Comments above are fine.
You mentioned: " I was looking for a way of automation than manual . "

Here is a comment to try.
Assume in a form 3 text boxes (prefix, noFrom, noTo) and a command button (processDo
Private Sub processDo_Click()
Dim prefix As String
    Dim sql As String
    Dim i As Integer
    'validate fields
    If Me.prefix <> "" Then
    Else
        Me.prefix = "TB_"
    End If
    If Me.NoTo < NoFrom Then
        Me.NoTo = Me.NoFrom
    End If
    'loop to process
    DoCmd.SetWarnings off
    For i = Me.NoFrom To Me.NoTo
        sql = "INSERT INTO " & Me.prefix & "merge" _
             & " SELECT * FROM " & Me.prefix & i
        DoCmd.RunSQL sql
    Next
    DoCmd.SetWarnings True
End Sub

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question