Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Unanswered: Combining multiple tables in Access 2007

    Hello group! I'm a very new user to Access and databases in general. I've only taken a few basic classes in Access 2007 and they didn't cover SQL at all.

    I've imported data from three different Excel spreadsheets into three new Access tables. Now I want to combine the three new tables into one main existing table in my database. The existing table already has 2040 records, so I'm hoping it can add the rows from the three new tables into the existing table starting from a new row 2040 and go up from there.

    The only way I've found to add data from a new table(s) into the existing table is to first go into the existing table and add new rows, ONE BY ONE, making sure to number them correctly. Then I copy the data (column by column) from the new table and paste it into columns in the existing table.

    I know this is the totally wrong way to import data into an existing table, and it takes too long to add numerous blank rows as you can imagine. Is there an easier way to import table from Excel into Access? Or from one table to another in Access when the columns in each table don't necessarily have the same names? I hope so because adding 1500 blank rows manually is the worse!

  2. #2
    Join Date
    Jun 2014
    Location
    In a deep fog...
    Posts
    11
    This can be very easy to do. You need to create an append query that looks at the new table. For each column in the new table, you can tell the query where to put it in the table you want the data to go. This way you don't have to rename any columns and can let the program to the work.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If the columns are the same another approach is to opend the destination table and source table(s). Then select all rows from the source cut (ctrl + c) and then paste (ctrl + v) into the destination.

    but Tinpusher's suggestion is the 'right' one
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You don't supply the names of the tables not those of the columns in each table so I'll remain very general. Although you could solve your problem in a row-by-row manner using Recordsets objects in VBA Do... While loops, , the "natural" solution to this kind of problem consists in issuing SQL commands to the database.

    If all the tables involved in the process (4 in your description) exist, the syntax to insert (add) rows (lines) from a table into another table is as follows:
    Code:
    INSERT INTO DestinationTable (Column1D, Column2D, ..., ColumnND) 
        SELECT Column1S, Column2S, ..., ColumnNS
        FROM SourceTable;
    Where Column1D, Column2D, ..., ColumnND are the names of the columns in the destination table and Column1S, Column2S, ..., ColumnNS are the names of the columns in the source table. Notice that the names are not necessary the same, although the number of columns must be equal in both columns list and the data type should be the same in each matching pair (don't count too much on automatic data conversion in SQL, although some are possible). In Access, all the built-in VBA functions as well as any user-defined Public function can be used in a SQL expression, although calling some of them would be meaningless. So, if a data conversion type is needed, specify it explicitly in the SQL expression (Query) with a VBA conversion function.

    Example:
    If Column1S in the source table is of type Text while Column1D in the destination table is numeric (Long), the SQL expression should become:
    Code:
    INSERT INTO DestinationTable (Column1D, Column2D, ..., ColumnND) 
        SELECT CLng(Column1S), Column2S, ..., ColumnND
        FROM SourceTable;
    If some values in Column1S are not numeric and a zero must be inserted in their place in Column1D, you can use the VBA Val() function because Val() returns zero when its argument is not numeric:
    Code:
    INSERT INTO DestinationTable (Column1D, Column2D, ..., ColumnND) 
        SELECT Val(Column1S), Column2S, ..., ColumnND
        FROM SourceTable;
    If nothing (Null) must be inserted in Column1D when the matching value in Column1S is not numeric, you can use the IIf() VBA function:
    Code:
    INSERT INTO DestinationTable (Column1D, Column2D, ..., ColumnND) 
        SELECT IIf(IsNumeric(Column1S), Columns1S, Null), Column2S, ..., ColumnND
        FROM SourceTable;
    Notice that you can also specify a condition in the SELECT part of the query. If Column1S in the source table is of type Text while Column1D in the destination table is numeric (Long) and the rows containing non-numeric data in Column1S must not be inserted into DestinationTable, you can use:
    Code:
    INSERT INTO DestinationTable (Column1D, Column2D, ..., ColumnND) 
        SELECT CLng(Column1S), Column2S, ..., ColumnND
        FROM SourceTable
        WHERE IsNumeric(Column1S) = True;
    To execute such a query you can:
    1. Create a query object and type the SQL expression into it (or in many cases, just use the Query Wizard).
    2. Create a VBA procedure (Sub or Function) and call it (from another VBA procedure or from a Macro):
    Code:
    Sub TransferData(ByVal SourceTable As String, ByVal DestinationTable As String)
    
        Const c_SQL As String = "INSERT INTO @D (Column1D, Column2D, Column3D, ColumnND) " & _
                                "SELECT Column1S, Column2S, Column3S, ColumnND " & _
                                "FROM @S;"
                                
        Dim strSQL As String
        
        strSQL = Replace(Replace(c_SQL, "@S", SourceTable), "@D", DestinationTable)
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Note: If a name (of a column or of a table) contains spaces charaters or any non alphanumeric characters (#, $, -, etc.), except the underline character (_), you must enclose that name in square brackets ([]).
    Example:
    Code:
    INSERT INTO [Destination Table] ([Column1-D], [Column2-D], ..., [ColumnN-D]) 
        SELECT [Column1-S], [Column2-S], ..., [ColumnN-S]
        FROM [Source Table];
    You should refrain from using such characters when naming objects in your databases, sooner or later, you'll be in trouble if you use them.
    Have a nice day!

  5. #5
    Join Date
    Jun 2014
    Posts
    2

    Thumbs up

    Thank you, everyone, for your helpful responses. It sounds like I need to learn SQL commands. It's amazing that the MS-Access 2007 Lev 1-4 classes I took never covered them as they seem necessary for importing functionality.

    I tried copy & pasting as one of the posts said, but before I can post to the destination dB I need to have blank rows (creating rows with primary keys (index numbers) with no data in them. That's where I ran into a wall.

    So I will hit the Databases for Dummies books/sites and see where the heck to enter SQL coding and how to execute them.

    Thank you, all!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •