Re: Access Query

anonymous_at_discussions.microsoft.com
Date: 08/11/04 

Date: Wed, 11 Aug 2004 16:23:45 -0700

Thankyou
>-----Original Message-----
>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: 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: Management Studio & Views containing unions
    ... display the View, which I guess is OK, the old tool couldn't do that ... the syntax verification doesn't work either.. ... "Incorrect Syntax near UNION" error each time. ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)
  • Re: Mangled, with a difference
    ... My only recollection about the "mangling" is something about ... a query recompile ran into the issue or not. ... * This different syntax can not be translated back into the ... everything that would require square brackets around it. ...
    (microsoft.public.access.queries)
  • Management Studio & Views containing unions
    ... editing Views that contain a UNION statement. ... For starters, it can't display ... I get an "Incorrect Syntax ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)
  • Re: Union Query not working yet
    ... I *think* we have to explicitly mentioned the fields, when we use a UNION. ... I can't seem to get this union query thing going right. ... Instead of selecting the most ... Maybe with the exact syntax life would be easier. ...
    (microsoft.public.access.queries)