Complex Querys using instances of tables

*I studied a website who has tutors regarding SQL, but it uses more the
ORACLE approach, and they often give complex examples like this, what i
would appreciate is how to make the same work in Access so
MicrosoftJetEngine recognizes it well:

Question: I'm trying to pull some info out of a table. To simplify,
let's say the table (report_history) has 4 columns:

user_name, report_job_id, report_name, report_run_date.

Each time a report is run in Oracle, a record is written to this table
noting the above info. What I am trying to do is pull from this table
when the last time each distinct report was run and who ran it last.

My initial query:

SELECT report_name, max(report_run_date)
FROM report_history
GROUP BY report_name;

runs fine. However, it does not provide the name of the user who ran
the report.

Adding user_name to both the select list and to the group by clause
returns multiple lines for each report; the results show the last time
each person ran each report in question. (i.e. User1 ran Report 1 on
01-JUL-03, User2 ran Report1 on 01-AUG-03). I don't want that....I just
want to know who ran a particular report the last time it was run.

This is where things get a bit complicated. The SQL statement below
will return the results that you want:

SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

Ok now my questions:
1)instancing table report_history as rh (this works in access??)
2)Encapsulated a Select inside a FROM statement (this works in acess??)
3)How can we accomplish the same results in a similar query in access
2003.

Thanks in advance

.

Relevant Pages

  • Re: Storing Queries in Tables
    ... Either you should follow the method Oracle is using to store SQL ... It looks like one of those databases has a multibyte characterset ... User runs a report by running a generic ksh with parameters e.g. name ... the net effect would be to reduce the report SQL statement size by the ...
    (comp.databases.oracle.misc)
  • [NEWS] Oracle Forms SQL Injection
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... part of the Oracle Developer Suite 10g". ... All Oracle Forms applications are by default vulnerable to SQL Injection. ... The following statement sends the result of the SQL statement: ...
    (Securiteam)
  • Re: How can legally include " " (single quote) in my SQL stateme
    ... Erland Sommarskog wrote: ... >> applying it to Oracle database. ... >> the use of parameters in SQL statement. ...
    (microsoft.public.data.oledb)
  • Re: How to get SQL errors occured during Connection.execute() or RecordSet.open() ?
    ... I've never seen ADO fail to return and report errors, in VBScript or VB. ... ' No error, execute the sql statement ...
    (microsoft.public.vb.database.ado)
  • Populating 10G XE table with time AND date through VB.Net
    ... This SQL statement delivered through VB.Net works fine: ... This is fair enough - presumably because the NLS_TIME_FORMAT is wrong format. ... If I run exactly same statement from within Oracle GUI SQL window I get: ...
    (comp.databases.oracle.server)