Re: Complex Querys using instances of tables

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

Very Good your explanation. Thanks a lot!

Indeed it took me some time but i learned with this examples.

RESUMING:Among other methods to accomplish this we have these (all
right and working in access 2003)

*YourMethod 1: WHERE SUBQUERY (instancing table to filter it but the
subquery is made in WHERE clause)

*YourMethod 2: INNER JOIN (creates another table of maxresults, and
combines with initial table to retrieve values)
I think my first example that is in Oracle mode, should be the
equivalent a third option that is:

*FirstMethod 3: FROM SUBQUERY (instancing table to filter it but the
subquery is made in FROM clause)
SELECT DISTINCT rh.report_name, rh.user_name, rh.report_run_date
FROM report_history AS rh, (SELECT report_name, max(report_run_date) as
maxdate FROM report_history GROUP BY report_name) AS maxresults
WHERE rh.report_name=maxresults.report_name And
rh.report_run_date=maxresults.maxdate;

Observations: notice that square brackets and period are not needed in
Access 2003 :).

For instancing or subquerying the sintaxe only needs generaly:
(query|subquery|table) AS <name>

Thanks :)

.

Relevant Pages

  • Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)
    ... > Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL ... > subquery, but maybe there is a simple syntax error. ... I need the PK's in the cross tab query to ... > This WHERE has the same IN Clause as J1T4. ...
    (microsoft.public.access.queries)
  • Re: query wont run in access 97
    ... It returns a syntax error FROM Clause. ... A Jet subquery used in a FROM clause will always ... it is wrapped in brackets with an ending period ... or the Jet query parser will choke on them. ...
    (microsoft.public.access.queries)
  • Re: Help with derived table SQL statement in Access
    ... SELECT expression on the FROM clause, but that is just that, a table ... You can use three forms of syntax to create a subquery: ... You can also use table name aliases in a subquery to refer to tables ... than the average salary of all employees having the same job title. ...
    (microsoft.public.access.queries)
  • Re: Insert via another tables columns
    ... The UPDATE with a SET (SELECT subquery) also requires that col_a1 and col_a2 ... UPDATE clause, beside the subquery in the SET clause. ... Earlier version of FoxPro need to use xbase REPLACE command or a mix of SQL ... I try the same in SQL Server and works great. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: subquery
    ... Generatrice ON [transport de generatrice].Nom = Generatrice.Nom) INNER ... SELECT clause, ... You can "correlate" the subquery back to the main ... on a field in the main query. ...
    (microsoft.public.access.queries)