Re: Q re Subqueries (Join Predicate) in Access

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

Roger Carlson wrote:
This might work:

SELECT t1.Key, t1.Col1, t1.Col2, t1.Col3, Count(e1.Key), Max(e1.Col1)
FROM Table1 t1
LEFT JOIN
[SELECT Key, Col1
FROM Table2
WHERE Col2 =3]. as e1
ON t1.Key = e1.Key
GROUP BY t1.Key,t1.Col1, t1.Col2, t1.Col3

Haven't tested it and it might take some fiddling. On my website (www.rogersaccesslibrary.com), is a small Access database sample called "SubQueryInFROM.mdb" which explains how to put a subquery in a From clause. You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=259


Excellent ... pretty much worked out of the box

Thank you eveyone
.

Relevant Pages

  • Re: Optimizing SQL - Union
    ... select 'col1' as field, col1 as val from table1 ... select 'col2' as field, ...
    (comp.databases.ms-sqlserver)
  • RE: Data Comparison
    ... SELECT Col1, Col2... ... FROM Table1 ... FROM Table2; ...
    (microsoft.public.sqlserver.programming)
  • Re: DTS Lokup %
    ... I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Nilay Shah" wrote in message ... > SELECT col1 ... > FROM Table1 ... > WHERE col2 like? ...
    (microsoft.public.sqlserver.dts)
  • Query Tuning Help - Sum multiple columns
    ... from table1 ... order by col1, col2 ... The table has 171,000 rows, but the query takes 20 minutes to ...
    (comp.databases.oracle.server)
  • Re: Q re Subqueries (Join Predicate) in Access
    ... FROM Table1 t1 ... [SELECT Key, Col1 ... WHERE Col2 =3]. ... is a small Access database sample called ...
    (microsoft.public.access.queries)