Combining Tables (1 Viewer)

Pvarga

Registered User.
Local time
Today, 14:13
Joined
Apr 6, 2002
Messages
50
I have created a database to keep track of students inour programs. There are 6 complicated queries I spent a long time making. I have given this to our 4 other sites and they have all entered their data. When I get all the files together is there anyway I can run my queries and reports and get data from all the databases? They all have the same fields and each only have 2 tables. The tables, queries and reports all have the same name in each database.

Thanks

I would like to run them all seperate so each can see their stats but the total of all sites gets submitted together to our state.

Thanks

Tricia
 

David R

I know a few things...
Local time
Today, 09:13
Joined
Oct 23, 2001
Messages
2,633
If your data comes from different sites, use UNION queries.

Assuming the sites aren't all networked together, you can use UNION queries to pull together all the data sets into one table when you collect them.

You have to code UNION queries by hand in the SQL window; there is no Design Window mode for them, or wizard.

Code:
SELECT [Field1] As [Field1], [Field2] As [Field2], ..., "ThisSite" As [Site]
FROM ThisSiteTable1
UNION
SELECT [Field1] As [Field1], [Field2] As [Field2], ..., "ThatSite" As [Site]
FROM ThatSiteTable1
UNION
SELECT [Field1] As [Field1], [Field2] As [Field2], ..., "TheOtherSite" As [Site]
FROM TheOtherSiteTable1
etc. I put in the [Site] field so you'd know what site they came from; if you've already got data in the tables that tells you that, you can leave that part off.

Then use these new UNION queries in place of your base tables for doing reports, etc.

Of course if your sites ARE networked, I strongly recommend keeping everything in one database and filtering the data for each site so they only see their own data, if that's needed.
 

Pvarga

Registered User.
Local time
Today, 14:13
Joined
Apr 6, 2002
Messages
50
Thanks I will give this a try on Monday. No they are not all networked. I have emailed them to each person. I played with the idea of putting it on our server and letting everyone enter into one DB but am so new at this it would have been too hard.

Thanks

Tricia
 

Pvarga

Registered User.
Local time
Today, 14:13
Joined
Apr 6, 2002
Messages
50
Sorry but I have one more question. If I make a query to put them together how do I run my current queries on the result. Does it create a new table?
T
 

David R

I know a few things...
Local time
Today, 09:13
Joined
Oct 23, 2001
Messages
2,633
Base your other queries on this UNION query.

Access considers tables and queries almost interchangeable in most situations. You just use this UNION query where you would have placed an individual table in the query before.

P.S. Depending on your network and your skill level, you may find it easier overall to place it on the network after all, since all your data will be uploaded immediately. However that depends on your network speed, for one thing; it will be slightly slower than a local copy. Synchronizing the individual copies is the main headache to sending out multiple copies of your database. Remember not to try to import the Primary Key of the individual tables as your new Primary Key, as it will probably clash with other copies.

Capiche?
 

Users who are viewing this thread

Top Bottom