Re: Synching Multiple Tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

<peter@xxxxxxxxxxxxxxxxxx> wrote in message
news:1188077501.100144.3070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I have a database with 10 identical tables. What I would like to do is
pull all the data from all the tables into one master table. Reason is
that the data entered in the 10 tables is done online by different
users. Had to set it up like that because of user login permissions,
etc.

I would like to be able to produce a report showing the combined total
of the 10 tables. I'm getting more proficient with Access, but I'm
still a novice when it comes to SQL. I'm not even sure where to begin
with this. As of now, I manually go in and copy/paste the daily
numbers from each table into the master table.

Is there any way to update the master table any time the other 10
tables get updated? And will it happen automatically, or do I need to
manually synch them each night?

Do you really need a master table, or can you simply create a UNION query
that joins the ten tables together and use that UNION query instead?

Thanks for your response Doug. I'm sure that would work just fine. But
like I said, I'm not very proficient when it comes to SQL, which I
assume i would have to write to create a UNION query? Any tips on what
sort of code I would need?

SELECT Field1, FIeld2, Field3
FROM Table1
UNION
SELECT Field1, FIeld2, Field3
FROM Table2
UNION
....
UNION
SELECT Field1, FIeld2, Field3
FROM Table10

You have no choice but to work directly with the SQL, as you can't create
Union queries through the graphical query builder. What you can do, since
the 10 tables are identical, is create a query through the graphical query
builder for the first table, then go into the SQL View (under the View
menu), and copy-and-paste that first query 9 more times (remembering to put
UNION between each subquery)

Note that Union queries are not updatable (just in case that's an issue to
you). Also, note that UNION will eliminate duplicates. If you don't wish the
duplicate entries to be eliminated, use UNION ALL instead.

If you have a desire to know from which table a given entry comes, you can
use

SELECT Field1, FIeld2, Field3, "Table1" As DataSource
FROM Table1
UNION
SELECT Field1, FIeld2, Field3, "Table2" As DataSource
FROM Table2
UNION
....
UNION
SELECT Field1, FIeld2, Field3, "Table10" As DataSource
FROM Table10

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



.

Relevant Pages

  • RE: Joining 3 Queries That Count
    ... However I've never tried something like this in the beginning of a union ... you may need to create query with the parameters ... Jerry Whittle, Microsoft Access MVP ... FROM [Master Table] ...
    (microsoft.public.access.queries)
  • Re: UPDATE - UNION
    ... You can change the query as: ... UNION ALL ... SELECT id,col2 as MyAlias, 'Col2' as TargetCol FROM TABLE1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Query - Crosstab ?
    ... You need the reverse of a crosstab, so perhaps a UNION query. ... SELECT Company, Product1 AS Product, "1" AS Source FROM Table1 ...
    (microsoft.public.access.queries)
  • Re: Access Query
    ... UNION ALL SELECT * FROM Table3; ... Then the second query does the agregating: ... ANYTHING that would require the use of square brackets, ... FROM [SELECT * FROM Table1 ...
    (microsoft.public.access.formscoding)
  • Re: Crosstab or Pivot Frustration
    ... The first UNION query restructures you data into a normalized layout: ... SELECT Region, "Supply" AS Category, Supply AS Result FROM Table1 ... SELECT Region, "SoldOut" AS Category, SoldOut AS Result FROM Table1; ...
    (comp.databases.ms-access)