SubQuery vs. DLookup()

  • Thread starter Thread starter dchendrickson
  • Start date Start date
D

dchendrickson

I am running Access2002/XP.

I am trying to create a calculated field in a query that
simply pulls data from a table.

I can use the DLookup method and all is well. But I can
not get an SQL subquery with - at least what I believe
is - the same parameters to return anything.

I have a table that contains information about the many
pins (electrical terminals) in a multi-pin connector.
Table tblPins has columns PinID, ConnectorID, PinLabel,
ConnectsTo. PinID is Primary Key (autonumber),
ConnectorID is a link to another table with (yep!)
connector info, PinLabel is the alpha-numeric label for
the pin in the connector, and ConnectsTo is linked back
to the PinID in the same manner as the Northwinds
Employee table's ReportsTo field is linked back to
EmployeeID. ConnectsTo tells which other pin in tblPins
is connected to the current record's pin.

In the calculated field I would like to show the PinLabel
of that OTHER pin; i.e. the PinLabel of the PinID in the
current record's ConnectsTo field.

I have in the query fields:
PinID, PinLabel, ConnectsTo, and then the calculated
field for the label of the PinID found in ConnectsTo.

The DLookup version, which works fine, is: Expr1:
DLookup ("PinLabel","tblPins","PinID = " & [ConnectsTo])

The SQL version, which returns nothing is: Expr2:
(SELECT PinLabel FROM tblPins WHERE PinID = [ConnectsTo];)

My question is twofold:
1> what is wrong with my SQL syntax? Am I not allowed to
refer to the query's [ConnectsTo] field?

2> do I need to worry? I was under the impression that
the Dlookup method was slow and inefficient. The time my
machine takes to fill in 50 records is 3-5 seconds on a
well equipped year-old HP. Is the SQL version a better
choice?

Thanks for your insight and assistance.

-dc
 
Since the subquery uses the same table as the main query, alias it and be
explicit about which table each field is from. Something like this:

SELECT * ,
( SELECT PinLabel FROM tblPins AS Dupe
WHERE Dupe.PinID = tblPins.ConnectsTo ) AS ConnectPinLabel
FROM tblPins;
 
Thanks Allen,

That does the trick! Now my problems have been pushed
downstream. I'll make a seperate post for those.

Your help is greatly appreciated.

-dc
-----Original Message-----
Since the subquery uses the same table as the main query, alias it and be
explicit about which table each field is from. Something like this:

SELECT * ,
( SELECT PinLabel FROM tblPins AS Dupe
WHERE Dupe.PinID = tblPins.ConnectsTo ) AS ConnectPinLabel
FROM tblPins;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


I am running Access2002/XP.

I am trying to create a calculated field in a query that
simply pulls data from a table.

I can use the DLookup method and all is well. But I can
not get an SQL subquery with - at least what I believe
is - the same parameters to return anything.

I have a table that contains information about the many
pins (electrical terminals) in a multi-pin connector.
Table tblPins has columns PinID, ConnectorID, PinLabel,
ConnectsTo. PinID is Primary Key (autonumber),
ConnectorID is a link to another table with (yep!)
connector info, PinLabel is the alpha-numeric label for
the pin in the connector, and ConnectsTo is linked back
to the PinID in the same manner as the Northwinds
Employee table's ReportsTo field is linked back to
EmployeeID. ConnectsTo tells which other pin in tblPins
is connected to the current record's pin.

In the calculated field I would like to show the PinLabel
of that OTHER pin; i.e. the PinLabel of the PinID in the
current record's ConnectsTo field.

I have in the query fields:
PinID, PinLabel, ConnectsTo, and then the calculated
field for the label of the PinID found in ConnectsTo.

The DLookup version, which works fine, is: Expr1:
DLookup ("PinLabel","tblPins","PinID = " & [ConnectsTo])

The SQL version, which returns nothing is: Expr2:
(SELECT PinLabel FROM tblPins WHERE PinID = [ConnectsTo];)

My question is twofold:
1> what is wrong with my SQL syntax? Am I not allowed to
refer to the query's [ConnectsTo] field?

2> do I need to worry? I was under the impression that
the Dlookup method was slow and inefficient. The time my
machine takes to fill in 50 records is 3-5 seconds on a
well equipped year-old HP. Is the SQL version a better
choice?

Thanks for your insight and assistance.

-dc
Click to expand...


.
Click to expand...
 
Back
Top