Create Query to run on Numerous Linked Tables

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.

re: Create Query to run on Numerous Linked Tables

On 14 Nov 2003 04:25:12 -0800, grumpyoldsod@freeuk.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.

[color=blue]
>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.[/color]

re: Create Query to run on Numerous Linked Tables

The problem with this is that each 'Weekly' Database contains circa
800,000 records and 50 fields. Could get very large!!!!
[color=blue]
> 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.[/color]





[color=blue]
>
>[color=green]
> >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.[/color][/color]

re: Create Query to run on Numerous Linked Tables

-----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 & programmatically 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, programmatically).

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

You'd programmatically 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/AwUBP7WXfYechKqOuFEgEQKwIwCfUy+eTzv1dKEpbQooUy3oC5 U7fKEAoIAh
Ivr5Al8QDgcvmHY6n0bh3dzr
=ZmgL
-----END PGP SIGNATURE-----

G rumpy O ld D uffer wrote:[color=blue]
> The problem with this is that each 'Weekly' Database contains circa
> 800,000 records and 50 fields. Could get very large!!!!
>
>[color=green]
>>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.[/color]
>
>
>
>
>
>
>[color=green]
>>[color=darkred]
>>>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.[/color][/color][/color]