Re: Access Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Roger Carlson (NO-Rog3erc-SPAM_at_hotmail.com)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 16:13:40 -0400

Well, one way is to use two queries. The first is a UNION query to put the
tables together:

qryTable123:
SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3;

Then the second query does the agregating:

SELECT Field1, Sum(Field2) AS SumOfField2
FROM qryTable123
GROUP BY Field1;

However, there is a seriously weird, undocumented format for using a
subquery in the FROM clause - essentially as another table. It won't work if
you have spaces or special characters in the names of tables or fields
(which I don't like anyway). Because of the syntax, *this* sort
of subquery cannot itself contain a subquery of the same format. The syntax
is:

[insert your query here]. As SomeAlias

The left and right square brackets are required, the dot after the right
bracket is required, and the As and alias are required. You can't do
ANYTHING that would require the use of square brackets, inside the square
brackets - that's why no spaces or special characters in
names.

Here's the syntax:
SELECT Field1, Sum(Field2) AS SumOfField2
FROM [SELECT * FROM Table1
Union All
SELECT * FROM Table2
UNION ALL SELECT * FROM Table3]. AS Table123
GROUP BY Field1; 

-- 
--Roger Carlson
  www.rogersaccesslibrary.com
  Reply to: Roger dot Carlson at Spectrum-Health dot Org
<anonymous@discussions.microsoft.com> wrote in message
news:44d001c47fd6$faf585e0$a501280a@phx.gbl...
> I have 3 tables that I would like to combine..
> Table 1            Table2             Table3
> A    10            B    2            C     -3
> B    20            D    5            E      1
> C    30
>
> The query should return
> A    10
> B    22
> C    27
> D     5
> E     1...How do I go about it?
>
> Thanks

Relevant Pages

  • 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: Access Query
    ... UNION query to put the ... Because of the syntax, ... >The left and right square brackets are required, ...
    (microsoft.public.access.formscoding)
  • Re: Synching Multiple Tables
    ... pull all the data from all the tables into one master table. ... Do you really need a master table, or can you simply create a UNION query ... FROM Table1 ...
    (microsoft.public.access.replication)
  • 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)
  • Re: Criterion - How to Write Query for Multiple Tables
    ... Table1: CoName, Address, WebSite, SalesExec, NULL, NULL, NULL ... The union query is thus: ... > what can be done with the query wizard, ... > simpler way to write a query in Design View to accomplish this task? ...
    (microsoft.public.access.queries)