Combining Data From Linked Excel Tables

[Guest]

I work for a small drug and alcohol charity in the UK how have been using
Excel to collect worker client contact episodes. Thats fine in itself, but
the data is spread across several workbooks each (1 per class of worker) with
several workshets in each (1 sheet per worker).

I've been asked to look at how we can extract ongoing reports from this
data. But I'm and information specialist, not IT or application

My thoughts are to use access (2003) to link to each worksheet to an access
database then use an append query to create a aggrated table of all data,
which then then be analysed. They do not have the resources to deploy access
to all staff either finacially or re-traing etc. So to continue to use
excell to collect data would be a 'simple' solution.

Can people here advise on this? I don't have a lot of time or other
resourcesto be able to devote on this so any help would be really
apprecaitted. All so pointers to traing resorces on the web would be good

TIA

Trevor

 

[Guest]

If the Excel workbooks do not change either location, name, sheet name, or
columns then you can link them into Access and use a union query rather than
an append query so that the data is realtime.

 

[Guest]

Thats great certainly seems to point me in the correct direction. But on 1st
test run only returned 6 records (from possible 100's) .. any ideas?

 

[Guest]

only returned 6 records (from possible 100's)
What returned the records? The union query or the suming query?

Build a little - test a little.

 

[Guest]

the union query (thats all I've looked at to date)
e.g
SELECT [Clinician], [Title]
FROM [D Ramsey ]

UNION SELECT [Clinician], [Title]
FROM [H Cook]

The idea being to add more fields & tables

 

[Guest]

Revise it like this ---
SELECT [Clinician], [Title]
FROM [D Ramsey ]

UNION ALL SELECT [Clinician], [Title]
FROM [H Cook];

the union query (thats all I've looked at to date)
e.g
SELECT [Clinician], [Title]
FROM [D Ramsey ]

UNION SELECT [Clinician], [Title]
FROM [H Cook]

The idea being to add more fields & tables

What returned the records? The union query or the suming query?

Build a little - test a little.