Re: Combining several tables in MS Access 2007

On Mar 26, 9:39 am, floronDBA <peterbro...@xxxxxxxxxxxxxxxxxxxx>
wrote:
On 26 mrt, 16:23, masonic35and7 <masonic35a...@xxxxxxxxx> 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.
.