Complex Querys using instances of tables

  • Thread starter Thread starter Francis
  • Start date Start date
F

Francis

*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
 
I suggest you try it and see. If you get errors than post here.
Access will probably insist that you use the AS when naming the subquery.
Also, it may change the SQL format to use square brackets and a period
around the subquery vice the parentheses. So you may see the following.

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}. AS maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

Personnally I would use an inner join or a coordinated subquery in the WHERE
clause as I think they would be more efficient in Access then the cartesian
join created by the above query.

Subquery in Where clause approach.
SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh
WHERE rh.Report_run_date =
(SELECT max(report_run_date) as maxdate
FROM report_history
WHERE report_History.Report_name = rh.report_name)

Inner Join approach
SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh INNER JOIN
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) as maxresults
ON rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;
 
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 :)
 
Ok thanks a lot!

Have tested and everything works.
now there many methods to accomplish this, but those are all possible:
*First Method: FORM SUBQUERY
*Observations: this method instances a table to filter the initial
table, in fact to instance tables/subqueries/queries you just need to
use: (<query>) AS <name> in Access 2003.
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;

*Your Method of WHERE SUBQUERY
It's interesting and easy you just subquery directly in WHere CLause.

*Your Method of INNER JOIN
Creates a table with maxresults, and combines it with inner join with
initial table to retrieve the user name fields.

All work ok.

Thanks.
 
Thanks again!
And its all working. Btw the access 2003, uses the simple sintax:
<query|table|subquery> AS <instancename> to create instances or to name
subquerys.

So first version in Oracle comes:
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;
 
Back
Top