Problem with correlated Sub-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 lngLabelID=Labels.lngLabelID
ORDER BY blnLabelDefault
) AS tempContacts


on Labels.lngLabelID=tempContacts.lnglabelID

 

[Jason Lepack]

This will serve your purpose I believe. It selects all records from
label and all matching records from label_contacts where the
contact_id is either the top one ordered by default,contact_id or is
null (no contacts).

SELECT
A.strLabelName,
A.blnActive,
B.strContactFirstName,
B.strContactLastName,
B.strContactEmail,
B.strPhone,
B.strContactType,
B.blnDefault
FROM
tblLabels AS A
LEFT JOIN tblLabelContacts AS B
ON A.lngLabelID = B.lngLabelID
WHERE
B.lngContactID In (
SELECT TOP 1
lngContactID
FROM
tblLabelContacts AS C
WHERE
A.lngLabelID = C.lngLabelID
ORDER BY
blnDefault,
lngContactID)
Or B.lngContactID Is Null

Cheers,
Jason Lepack

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 lngLabelID=Labels.lngLabelID
ORDER BY blnLabelDefault
) AS tempContacts

on Labels.lngLabelID=tempContacts.lnglabelID

 

[Andrew]

This will serve your purpose I believe. It selects all records from
label and all matching records from label_contacts where the
contact_id is either the top one ordered by default,contact_id or is
null (no contacts).

<snip>


Perfect!! Thank you so much for your help - it had been driving me to
distraction!!!

Thank you again.

Andrew

 

[Jason Lepack]

You're welcome.

<snip>

Perfect!! Thank you so much for your help - it had been driving me to
distraction!!!

Thank you again.

Andrew