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.
.

Relevant Pages

  • Re: Combining several tables in MS Access 2007
    ... Create query in design view. ... INSERT INTO TABLE1 ... SELECT * FROM TABLE2 ... positions than is usually needed) and numerical columns (Excel stores ...
    (comp.databases.ms-access)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
  • doubt
    ... The Microsoft Jet database engine cannot find the input table or query ... Exception Details: System.Data.OleDb.OleDbException: The Microsoft Jet ... database engine cannot find the input table or query 'Table1'. ... dbParams, Object& executeResult) +255 ...
    (comp.databases.ms-access)