Re: Help with derived table SQL statement in Access


"Andrew" <andrewmrichards@xxxxxxxxxxx> wrote in message
news:1181660751.467152.258130@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 12, 2:44 pm, "Chris2"
<rainofsteel.NOTVA...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
"Andrew" <andrewmricha...@xxxxxxxxxxx> wrote in message

news:1181646621.996165.249640@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hi All

<snip>

Andrew,

<snip>

Hi Chris

Thanks for your input.

I've amended the query as you suggest (resultant SQL is pasted
below).
As far as I'm aware, although Access does use the table alias before
every column name, it's not actually needed, and so I often remove
it
for clarity, without ill effect...

Andrew,

Table aliases increase the clarity of SQL by explicitly noting each
column's origin table. Explicit code is preferred to implicit code.


All of which leads me to.... nothing new! Unfortunately, it still
doesn't work. A minor error in what I said first time is that if you
don't enter a value in the parameter inputbox, you get all those
labels which don't have a matching contact at all, rather than no
output.

If I remove the where clause from the subquery, it works, but I get
all the contacts, not just the first one...


<snip>


<sigh> Is understanding too much to ask of life?!

Andrew



<code snip>



I tried to replicate your issue in general.

ItemMaster and PricingMaster are two simple example tables I keep
around in one of my databases, their structure is not important for
this example.

SELECT *
FROM ItemMaster AS IM1
LEFT JOIN
(SELECT *
FROM PricingMaster as PM0
WHERE IM1.ItemID = PM0.ItemID
ORDER BY 1) AS PM1
ON IM1.ItemID = PM1.ItemID

When I try to run this query, MS Access throws a parameter input box
and wants a parameter for IM1.ItemID.

When I take out the WHERE, the issue goes away.

This seems to be exactly what is happening to you.

While you can "create" a table (as a table expression) by using a
SELECT expression on the FROM clause, but that is just that, a table
expression (a full query), I am thinking you cannot do correlation
between two table-type expressions on the FROM clause. If you had a
subquery in the table-type expression, you might swing correlation,
but I'm not sure.

A reading of the JET SQL 4.0 Reference "SQL Subqueries", seems to
vaguely suggest it as well.

--------------------------
Syntax
You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement)

expression [NOT] IN (sqlstatement)

[NOT] EXISTS (sqlstatement)
--------------------------

And:

--------------------------

You can also use table name aliases in a subquery to refer to tables
listed in a FROM clause outside the subquery. The following example
returns the names of employees whose salaries are equal to or greater
than the average salary of all employees having the same job title.
The Employees table is given the alias "T1":

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;


In the preceding example, the AS reserved word is optional.

--------------------------

It doesn't come right out and say it, but I believe that correlation
may not be possible between two table expressions on the FROM clause.
(My belief may be incorrect, of course. It just appears that way from
this.)


Sincerely,

Chris O.


.

Relevant Pages

  • RE: Data from Table to Populate Query
    ... You'll need to change your critieria to an IN clause and then reference all ... the records in your second table using a subquery. ... The SQL will look something like this: ...
    (microsoft.public.access.queries)
  • Re: VFP, SQL: Locking
    ... > Not everything can be done in ONE SQL statement. ... That'ts true of course but in the enhanced SQL of VFP9 it's a lot less so. ... VFP9 implements Standard SQL SELECT statements n the SELECT clause, ...
    (microsoft.public.fox.programmer.exchange)
  • Re: update trigger help!!
    ... >3) When the Rapidia Date Completion Column is filled in from NULL on the ... The SQL in the subquery is formatted in a way that ... If you take just the subquery and run it in Query Analyzer, ...
    (microsoft.public.sqlserver.programming)
  • Re: Q: Derived table--should I use WHERE in there?
    ... My practice is to apply the where clause as part of the subquery. ... Will the execution plan generator be smart enough to produce the best ... Everyone here speaks SQL; some are more fluent, ...
    (microsoft.public.sqlserver.programming)
  • RE: Subquery Question
    ... Below is the query that I am using. ... The statement in question is near the from clause. ... your first SQL statement isn't going to return much useful ... I consider a subquery as a query inside of another query. ...
    (microsoft.public.access.queries)