Create Query to run on Numerous Linked Tables

[G rumpy O ld D uffer]

This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.

I've been given 30+ (and counting) separate 'Weekly' Databases which
all have a Table, in exactly the same Field format, in each of these
'Weekly' DataBases.

I want to set-up a new 'Master' Database, so that I can write one
Query to search all the 'Weekly' Databases and produce one new Table
in the 'Master' Database from all the Tables in the 'Weekly'
Databases.

I've Linked (File/Get External Data/Link Tables) all the relevant
Tables from the 'Weekly' Databases to the 'Master' Database but need
help to create a Query for extracting, for example, all London related
items. I've been told it can be done using SQL (which I have no
experience of!!).

Any 'step by step' assistance would be greatly appreciated.

I'm using ACCESS 2000.

[Tom van Stiphout]

On 14 Nov 2003 04:25:12 -0800, gr**********@fr eeuk.com (G rumpy O ld
D uffer) wrote:

You could start with a union query:
select * from tblWeek1
union
select * from tblWeek2
....etc...

A much better solution is to develop a single perpetual database for
them, by adding a date field to the data, or perhaps a yyyyww (year
number, week number) field.

-Tom.

This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.

I've been given 30+ (and counting) separate 'Weekly' Databases which
all have a Table, in exactly the same Field format, in each of these
'Weekly' DataBases.

I want to set-up a new 'Master' Database, so that I can write one
Query to search all the 'Weekly' Databases and produce one new Table
in the 'Master' Database from all the Tables in the 'Weekly'
Databases.

I've Linked (File/Get External Data/Link Tables) all the relevant
Tables from the 'Weekly' Databases to the 'Master' Database but need
help to create a Query for extracting, for example, all London related
items. I've been told it can be done using SQL (which I have no
experience of!!).

Any 'step by step' assistance would be greatly appreciated.

I'm using ACCESS 2000.

[G rumpy O ld D uffer]

The problem with this is that each 'Weekly' Database contains circa
800,000 records and 50 fields. Could get very large!!!!

A much better solution is to develop a single perpetual database for
them, by adding a date field to the data, or perhaps a yyyyww (year
number, week number) field.

-Tom.

This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.

I've been given 30+ (and counting) separate 'Weekly' Databases which
all have a Table, in exactly the same Field format, in each of these
'Weekly' DataBases.

I want to set-up a new 'Master' Database, so that I can write one
Query to search all the 'Weekly' Databases and produce one new Table
in the 'Master' Database from all the Tables in the 'Weekly'
Databases.

I've Linked (File/Get External Data/Link Tables) all the relevant
Tables from the 'Weekly' Databases to the 'Master' Database but need
help to create a Query for extracting, for example, all London related
items. I've been told it can be done using SQL (which I have no
experience of!!).

Any 'step by step' assistance would be greatly appreciated.

I'm using ACCESS 2000.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Zowie . . . 800,000 records per week!!

Well hopefully, the table name is the same in all database files
(.mdb). You'd have to create a loop in VBA that iterates thru each db
file name & programmaticall y alter the SQL statement for each db file.
You don't have to link all the tables in the other dbs to the working
db file - you can use the JET SQL "In" predicate to indicate which db
file you want to read data from. You should put all weekly db files
in one folder; format the db files like this: "yyyyww.mdb " i.e., week
30 of year 2003 would be "200330.mdb " (numbers are best - 'cuz they
can be searched in numerical order, programmaticall y).

SELECT *
FROM tblWeeklyData In "c:\MyDocs\2003 30.mdb"
WHERE City = 'London'

You'd programmaticall y change the name of the db file

(. . . In " <change this file name> " . . .).

You'd have to save the retrieved data in a temp table in the current
db 'cuz you have the possibility of many recordsets returned from the
many db files. After all the db files have been searched, run another
query that accesses the temp table.

Quite a project, but not undo-able.

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7WXfYechKq OuFEgEQKwIwCfUy +eTzv1dKEpbQooU y3oC5U7fKEAoIAh
Ivr5Al8QDgcvmHY 6n0bh3dzr
=ZmgL
-----END PGP SIGNATURE-----

G rumpy O ld D uffer wrote:

The problem with this is that each 'Weekly' Database contains circa
800,000 records and 50 fields. Could get very large!!!!

A much better solution is to develop a single perpetual database for
them, by adding a date field to the data, or perhaps a yyyyww (year
number, week number) field.

-Tom.

This is probably a 'Low-Level' question to all the ACCESS experts but
I've only been using ACCESS for a couple of weeks.

I've been given 30+ (and counting) separate 'Weekly' Databases which
all have a Table, in exactly the same Field format, in each of these
'Weekly' DataBases.

I want to set-up a new 'Master' Database, so that I can write one
Query to search all the 'Weekly' Databases and produce one new Table
in the 'Master' Database from all the Tables in the 'Weekly'
Databases.

I've Linked (File/Get External Data/Link Tables) all the relevant
Tables from the 'Weekly' Databases to the 'Master' Database but need
help to create a Query for extracting, for example, all London related
items. I've been told it can be done using SQL (which I have no
experience of!!).

Any 'step by step' assistance would be greatly appreciated.

I'm using ACCESS 2000.