Link Excel files to single Access table

[Guest]

Hi,

I'm using Excel and Access XP.
I have 4 Excel files all with the same headings.
How can i get them into a single Access table whilst still remaining linked
to the
imdividual spreadsheets so new changes are reflected in the table.


Thanks for any help.
--

_______________________
Naz,
London

p.s I fired this off a short while ago but have no idea which board it went
to.....hence have re-posted sorry if you read it twice.

 

[Ken Snell \(MVP\)]

You don't do it directly. Each worksheet is a separate "linked table". Use a
union query to combine the data from the four worksheets into a single data
set.

 

[Guest]

Ken
This is what I'm trying to do, but as the data is added to the data set from
each worksheet I want to create a running sum. I'm new to union queries,
help would be much appreciated.

 

[Ken Snell \(MVP\)]

Can you give us more specific details about what you're wanting to do with
the data in ACCESS? I'm not understanding the nuances here yet.

 

[Guest]

I have several worksheets linked in Access with about 10000 rows in each,
when I run a Union query I'd like Access to number sheet1: 1-10000, then
sheet2: 10001-20000 etc... SQL is simple...

SELECT *
FROM sheet1
WHERE field1 >"1"

UNION

SELECT *
FROM sheet2
WHERE field1 >"1"

Hope this explains...

 

[Ken Snell \(MVP\)]

The union query that you have can be run whenever you want a "copy" of the
data that are in the four EXCEL worksheets. Use the query just as you would
a table when you design other queries, forms, or reports. There is no need
to physically store the data again in another table.

I would suggest that you add one more field to your union query so that you
can "tag" each record with the name of the worksheet from which the data
item comes:

SELECT sheet1.*, "sheet1" AS SheetSource
FROM sheet1
WHERE sheet1.field1 >"1"

UNION

SELECT sheet2.*, "sheet2" AS SheetSource
FROM sheet2
WHERE sheet2.field1 >"1"