By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,852 Members | 1,014 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,852 IT Pros & Developers. It's quick & easy.

Create Query to run on Numerous Linked Tables

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 14 Nov 2003 04:25:12 -0800, gr**********@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.

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.


Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
-----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:
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.


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.