Re: Complex Querys using instances of tables

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;

"Francis" <francis_1750@xxxxxxx> wrote in message
news:1136459400.541248.136930@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> *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: Two Queries
    ... you can't use a subquery that reguires square braces embedded in the subquery. ... Since your field and table names meet the requirements you can simply paste the subquery into the query. ... tblEmployees LEFT JOIN Query13 ... ... FROM tblJobNames INNER JOIN ((tblEmployees ...
    (microsoft.public.access.queries)
  • Re: Reporting the last two updates
    ... a subquery. ... correlated subquery because it refers to the outer query, ... last two updates per property. ... My report brings in all the information ok, and I was able to limit the ...
    (microsoft.public.access.gettingstarted)
  • Re: Calculated field question
    ... In a query, you could use a subquery to get the time from the previous row. ... PriorTime: ... If your report performs any aggregation that depends on the subquery field, ...
    (microsoft.public.access.queries)
  • Re: Report based on query w/subquery wont give a Report Footer to
    ... I don't have the underlying query updating the tables. ... the user's entry into the entry form. ... and report on the difference between them. ... multiple Group Bys in a subquery. ...
    (microsoft.public.access.reports)
  • Re: Display a prior record value with current record value
    ... with a subquery, but IME, ... The report often gives ... possibly by stacking one query on top of another. ... TransactionID DateChange Amount InvID ...
    (microsoft.public.access.reports)