combining tables & importing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I think this has been answered multiple times, but I just want to clarify
since I'm not really familiar w/ access..

I have 5 tables, w/ the same fields, and in order to combine them i should
use an append query? Do I have to link the tables as well?

Secondly...I have an excel spreadsheet w/ the same fields as the above
access tables, will I be able to import that data and combine with the above?

Any help would be appreciated!

Gigi
 
Hi Gigi,

1) If you want to move all the data into one table, you need to use
append queries. For the Excel data, either create a linked table
pointing to the worksheet and base your append query on that, or else
use an append query like this to get the data directly from the
worksheet:

INSERT INTO MyAccessTable (XXX, YYY, ZZZ)
SELECT (XXX, YYY, ZZZ)
FROM [Excel 8.0;HDR=Yes;Database=D:\Folder\File.xls;].[Sheet1]
;

Replace XXX etc. with the actual field names. If the list of field names
is the same in the worksheet and the Access table, you can omit the
first (XXX, YYY, ZZZ) list and replace the second with *.

2) If you just want to get the records from all the tables without
actualy moving the data, use a Union query.

On Sun, 6 Nov 2005 17:51:15 -0800, Gigi S <Gigi
 
Gigi

As John points out, you can use a UNION query to collect all the relevant
rows from five tables.

Keep in mind, however, that just because Excel uses spreadsheets (and, I'm
guessing, five different 'sheets), Access is not limited to that table
structure/data design.

In fact, if you've only copied Excel structure into Access, you will not get
the full benefit of Access, and will need to, for example, resort to UNION
queries to connect things together.

Another way to structure 5 identical data sets is to create a single table
with all of the fields these five share, plus one more field that defines
the "source" (i.e., set1, set2, set3, ...). With this design, you can
create a query that returns all of the data at once, grouped by "source".
 
Back
Top