Help with derived table SQL statement in Access

[Andrew]

Hi All

I wonder if anyone can help me with this.

The scenario is that I have a pair of related tables. One contains
record labels, the other contains contact names at those labels. In
the contacts table there is a boolean field called blnLabelDefault
which identifies whether the listed contact should be used as the
default for a label. There is code to ensure that no more than one
contact may be listed as the default for any given label. However a
label might not have ANY contacts listed as default.

I am trying to create a query which will show basic info from the
tblLabels table and basic details from the FIRST record only of the
tblLabelContacts table, which is sorted by blnLabelDefault. The theory
is that this will show me the the label details, plus the info for
only the default contact if there is one, but if not then only the
first contact for the label. Ultimately, the data will move to a
position where every label has a default contact.

The way I have approached it is to create a SQL statement which joins
tblLabels with a derived table, called TempContacts, which shows the
top 1 contact, ordered by blnLabelDefault, where the label ID matches
the label ID for the outer query. The full statement is below.

However when I run the query, I am asked to supply a value for
lngLabelID. If I don't, or if I supply a non-existant LabelID, I get
no results, if I supply an actual LabelID, I get effectively a cross-
join query, where I see the correct details for the contacts at the
valid label ID that I supplied, next to every single label in the
table.

All field names are correct and double-checked!

Help!

Thanks a lot
Andrew

Select Labels.lngLabelID, strLabelName, blnActive,
tempContacts.ContactName
from tblLabels as Labels
left outer JOIN

(
SELECT top 1 lngContactID, lngLabelID, [strcontactfirstname] & " " &
[strcontactlastname] AS ContactName, strContactEmail, strPhone,
strContactType, blnLabelDefault
FROM tblLabelContacts
WHERE tempContacts.lngLabelID=Labels.lngLabelID
ORDER BY blnLabelDefault
) AS tempContacts

on Labels.lngLabelID=tempContacts.lnglabelID

 

[Chris2]

Hi All

<snip>

Andrew,

Comments are in-line.

Select Labels.lngLabelID, strLabelName, blnActive,

Two column names are missing table alias prefixes.

tempContacts.ContactName
from tblLabels as Labels
left outer JOIN

(
SELECT top 1 lngContactID, lngLabelID, [strcontactfirstname] & " " &
[strcontactlastname] AS ContactName, strContactEmail, strPhone,
strContactType, blnLabelDefault

Eight more column names are missing table alias prefixes.

It is my thought, completely untested and quite possibly wrong, that
JET cannot figure out that lngLabelID above is a part of a table, or
which table it belongs to, and that it is a parameter because of this.
(Or that this situation might be happening somewhere else because of
missing table aliases.)

FROM tblLabelContacts

A table is missing a table alias.

WHERE tempContacts.lngLabelID=Labels.lngLabelID

tempContacts is the alias of the table being produced by this
subquery.

You are referring to that inside the subquery. (It appears to me that
the subquery is pointing at itself.)

ORDER BY blnLabelDefault
) AS tempContacts

on Labels.lngLabelID=tempContacts.lnglabelID

Go through the SQL and properly establish all table aliases on all
columns.


Sincerely,

Chris O.

 

[Andrew]

<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...

However, your point about the circular reference within the subquery
was right, and I've removed that mistake.

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...

I have, however, come up with a work-around. Not happy, because I'd
rather find the solution (or does Access not do corelated sub-
queries?) but it does the job. Workaround is to create the query as
below, but not include the where clause. Then create another query
based on this one, which groups all the fields together and uses the
LAST function on the Contact details to extract the last one - which
will be the one labeled as the LabelDefault..

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

Andrew


SELECT Labels.lngLabelID, strLabelName, blnActive,
tempContacts.ContactName, tempContacts.strContactEmail,
tempContacts.strPhone, tempContacts.strContactType,
tempContacts.blnLabelDefault
FROM tblLabels AS Labels LEFT JOIN [SELECT lngContactID, lngLabelID,
[strcontactfirstname] & " " &
[strcontactlastname] AS ContactName, strContactEmail, strPhone,
strContactType, blnLabelDefault
FROM tblLabelContacts

ORDER BY blnLabelDefault
]. AS tempContacts ON Labels.lngLabelID=tempContacts.lnglabelID;

 

[Chris2]

<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...

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

Andrew

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.

 

[Andrew]

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.- Hide quoted text -

- Show quoted text -

Okay. Thanks again for your input... I'm thinking I might be able to
get this to work by re-constructing the query based in this info.

Thanks for your help.

Andrew