+ Reply to Thread
Results 1 to 7 of 7

Combining several tables in MS Access 2007

  1. Combining several tables in MS Access 2007

    I work for a school district, and I have just imported 6 Excel
    worksheets into Access 2007. Now I have 6 different tables. In each
    table all the fields are the same. I need the easiest way to merge
    all the tables into one huge table. There are at least 2,500 records
    per table if that makes a difference.

    I am unfamilar with writing any type of code, so please don't suggest
    that unless you can also tell me how to do that.

    Thanks



  2. Re: Combining several tables in MS Access 2007

    On Mar 26, 10:23*am, masonic35and7 wrote:
    > I work for a school district, and I have just imported 6 Excel
    > worksheets into Access 2007. *Now I have 6 different tables. *In each
    > table all the fields are the same. *I need the easiest way to merge
    > all the tables into one huge table. *There are at least 2,500 records
    > per table if that makes a difference.


    1) Make a new empty table or choose the existing table to be the
    central repository of the data.

    2) Make a query for all field and records for the first table. Change
    the query type to Append and select the destination table as the
    target.

    3) Execute the query

    4) Delete the source table and replace with the next

    5) Lather, rinse, repeat until all source tables have been used.

    Good luck

    Tim Mills-Groninger


  3. Re: Combining several tables in MS Access 2007

    On Mar 26, 9:17*am, timmg wrote:
    > On Mar 26, 10:23*am, masonic35and7 wrote:
    >
    > > I work for a school district, and I have just imported 6 Excel
    > > worksheets into Access 2007. *Now I have 6 different tables. *In each
    > > table all the fields are the same. *I need the easiest way to merge
    > > all the tables into one huge table. *There are at least 2,500 records
    > > per table if that makes a difference.

    >
    > 1) Make a new empty table or choose the existing table to be the
    > central repository of the data.
    >
    > 2) Make a query for all field and records for the first table. *Change
    > the query type to Append and select the destination table as the
    > target.
    >
    > 3) Execute the query
    >
    > 4) Delete the source table and replace with the next
    >
    > 5) Lather, rinse, repeat until all source tables have been used.
    >
    > Good luck
    >
    > Tim Mills-Groninger


    Tim,
    I did as you instructed. I have run into a hurdle. I created a new
    empty table, and I selected that as my "Append to" destination. I
    click on OK, then I hit run, and it gave me the error message that I
    need at least one destination field. What do I do??

  4. Re: Combining several tables in MS Access 2007

    On 26 mrt, 16:23, masonic35and7 wrote:
    > I work for a school district, and I have just imported 6 Excel
    > worksheets into Access 2007. *Now I have 6 different tables. *In each
    > table all the fields are the same. *I need the easiest way to merge
    > all the tables into one huge table. *There are at least 2,500 records
    > per table if that makes a difference.
    >
    > I am unfamilar with writing any type of code, so please don't suggest
    > that unless you can also tell me how to do that.
    >
    > Thanks


    I'm using Access 2003, so layout etc. may be different

    Open your database and select queries in the left-hand column.
    Create query in design view (doubleclick).
    Select two of your tables (doubleclick) and Close.

    Now you are in the Query Wizard, however we are going to write SQL to
    insert rows from the second table into the first.
    You may need to make the active window (then one with the QW) smaller:
    click on the 'resize-window' symbol in the upper right hand corner if
    it consists of two overlapping squares (I'm sure there is a shorter
    way to say this).
    Right-click (i.e. right mouse button) on the top bar of the QW window
    and choose SQL view.

    The wizard has already decided for you that a select query is
    appropriate, which it isn't.
    Edit the text as follows:
    Replace SELECT FROM with INSERT INTO
    Then comes TABLE1, however it's called
    Add SELECT * FROM
    Then comes TABLE2

    You end up with
    INSERT INTO TABLE1
    SELECT * FROM TABLE2

    Click the red exclamation mark and the query runs. You may now close
    and save it for reuse, or edit it for the next table to be added into
    table1.

    When you've done this for all tables (you may even insert the contents
    of table1 in table1, but that results just in duplicate values), you
    should adjust the field size for both text columns (255 is more
    positions than is usually needed) and numerical columns (Excel stores
    numerical values as floating point, often you prefer either integer or
    long integer in Access).

    This should get you going
    Regards









  5. Re: Combining several tables in MS Access 2007

    On Mar 26, 9:39*am, floronDBA
    wrote:
    > On 26 mrt, 16:23, masonic35and7 wrote:
    >
    > > I work for a school district, and I have just imported 6 Excel
    > > worksheets into Access 2007. *Now I have 6 different tables. *In each
    > > table all the fields are the same. *I need the easiest way to merge
    > > all the tables into one huge table. *There are at least 2,500 records
    > > per table if that makes a difference.

    >
    > > I am unfamilar with writing any type of code, so please don't suggest
    > > that unless you can also tell me how to do that.

    >
    > > Thanks

    >
    > I'm using Access 2003, so layout etc. may be different
    >
    > Open your database and select queries in the left-hand column.
    > Create query in design view (doubleclick).
    > Select two of your tables *(doubleclick) and Close.
    >
    > Now you are in the Query Wizard, however we are going to write SQL to
    > insert rows from the second table into the first.
    > You may need to make the active window (then one with the QW) smaller:
    > click on the 'resize-window' symbol in the upper right hand corner if
    > it consists of two overlapping squares (I'm sure there is a shorter
    > way to say this).
    > Right-click (i.e. right mouse button) on the top bar of the QW window
    > and choose SQL view.
    >
    > The wizard has already decided for you that a select query is
    > appropriate, which it isn't.
    > Edit the text as follows:
    > Replace SELECT FROM with INSERT INTO
    > Then comes TABLE1, however it's called
    > Add SELECT * FROM
    > Then comes TABLE2
    >
    > You end up with
    > INSERT INTO TABLE1
    > SELECT * FROM TABLE2
    >
    > Click the red exclamation mark and the query runs. You may now close
    > and save it for reuse, or edit it for the next table to be added into
    > table1.
    >
    > When you've done this for all tables (you may even insert the contents
    > of table1 in table1, but that results just in duplicate values), you
    > should adjust the field size for both text columns (255 is more
    > positions than is usually needed) and numerical columns (Excel stores
    > numerical values as floating point, often you prefer either integer or
    > long integer in Access).
    >
    > This should get you going
    > Regards


    FloronDBA,
    Access 2007, looks totally different than 2003. It will not allow me
    to have a query open and then goto the query wizard.

  6. Re: Combining several tables in MS Access 2007

    On Mar 26, 11:34*am, masonic35and7 wrote:
    > On Mar 26, 9:17*am, timmg wrote:
    >
    >
    >
    >
    >
    > > On Mar 26, 10:23*am, masonic35and7 wrote:

    >
    > > > I work for a school district, and I have just imported 6 Excel
    > > > worksheets into Access 2007. *Now I have 6 different tables. *In each
    > > > table all the fields are the same. *I need the easiest way to merge
    > > > all the tables into one huge table. *There are at least 2,500 records
    > > > per table if that makes a difference.

    >
    > > 1) Make a new empty table or choose the existing table to be the
    > > central repository of the data.

    >
    > > 2) Make a query for all field and records for the first table. *Change
    > > the query type to Append and select the destination table as the
    > > target.

    >
    > > 3) Execute the query

    >
    > > 4) Delete the source table and replace with the next

    >
    > > 5) Lather, rinse, repeat until all source tables have been used.

    >
    > > Good luck

    >
    > > Tim Mills-Groninger

    >
    > Tim,
    > I did as you instructed. I have run into a hurdle. *I created a new
    > empty table, and I selected that as my "Append to" destination. I
    > click on OK, then I hit run, and it gave me the error message that I
    > need at least one destination field. *What do I do??- Hide quoted text -
    >
    > - Show quoted text -


    See the title bar of the box holding all of the field names?
    Excellent. Double click on that and it will select all of the fields
    in the query. Next click on the first field name (it can be anything
    except *) and drag it down to the field grid. All of the field names
    should be in the field row and have matching destination fields. Now
    execute the query.

    Access, I like to say, is the very model of the obdurate bureaucrat
    and must be told exactly what to do because it will do no more or no
    less.

    Good luck

    Tim Mills-Groninger

  7. Re: Combining several tables in MS Access 2007

    Greetings,

    Instead of using queries, lets try a simple code module. First, I
    understand you imported 6 excel worksheets. They will be named
    something like Sheet1, Sheet2, Sheet3,...

    So you have created an empty table that contains the same number of
    fields as each of the Sheet tables. If you did not create a table
    exactly like the sheet tables -- what you can do is to just copy any one
    of the sheet tables and choose only structure but not data. For my
    explanation I will call the new table table1.

    Now go to the Code Module tab and create a new code module and save it.
    It will be saved as module11 I believe, by default. Now copy the
    following code from here and paste it into your code module and rename
    the sheet tables as necessary.

    '--copy this code
    Sub AppendData()
    DoCmd.RunSql "Insert Into table1 Select * from Sheet1"
    DoCmd.RunSql "Insert Into table1 Select * from Sheet2"
    DoCmd.RunSql "Insert Into table1 Select * from Sheet3"
    DoCmd.RunSql "Insert Into table1 Select * from Sheet4"
    DoCmd.RunSql "Insert Into table1 Select * from Sheet5"
    DoCmd.RunSql "Insert Into table1 Select * from Sheet6"
    End Sub

    Now place the mouse cursor anywhere inside

    Sub AppendData()

    and press the F5 key and all the data from each of the sheet tables will
    be appended to table1.

    Note: each of the sheet tables must be exactly alike for this to work.
    They must all have the exact same number of fields.

    Rich

    *** Sent via Developersdex http://www.developersdex.com ***

+ Reply to Thread