Problem with a Derived Table SQL query 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

[DavidB]

On Jun 12, 7:24 am, Andrew <[e-mail]:

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

If there is no default contact what is the determining factor as to
which contact is 'first'?

[Andrew]

..lngLabelID=tempContacts.lnglabelID

>
If there is no default contact what is the determining factor as to
which contact is 'first'?- Hide quoted text -

- Show quoted text -

There's no preferred contact as such, so it would presumably default
to whatever comes up first as ordered by underlying index settings.

Andrew

[Neil]

You might want to store your derived table SQL as a stored query and look at
the results of it alone. Then, if they're fine, join that stored query to
the other table in your below SQL, and so on. That's an easy way to
troubleshoot it.

"Andrew" <an*************@yahoo.co.ukwrote in message
news:11**********************@a26g2000pre.googlegr oups.com...

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