Subqueries

[nogreatnamesleft]

I have a main table that has several fields with user ID numbers (e.g. sales,
engineering, etc.) and a second table with the all the user ID numbers,
names, etc.

I want to create a query that shows the user name, not ID, in the record. I
entered in the design view this:
Last Name: (SELECT LAST_NAME
FROM ENGR_NAMES LEFT JOIN MAIN_TABLE
ON ENGR_NAMES.ENGR_ID = MAIN_TABLE.ENGR_ID

I get this error:
At most one record can be returned by this subquery.

What am I doing wrong?

 

[Duane Hookom]

It isn't clear why you need a subquery when it appears you could simply join
in the ENGR_NAMES table into your main query.

However, if you can't join in the table with the names and really need to
use a subquery, try:
Last Name: (SELECT LAST_NAME
FROM ENGR_NAMES WHERE ENGR_NAMES.ENGR_ID = MAIN_TABLE.ENGR_ID)

This assumes MAIN_TABLE is part of your original "FROM" clause.

 

[John Spencer]

A subquery in the select clause can only return one value. You can fix
the subquery by changing it to use one of the aggregate functions (Max,
Min, First, or Last should all work).

SELECT Max(Last_Name) FROM ENGR_NAMES LEFT JOIN Main_Table ON
ENGR_NAMES.Engr_ID = Main_Table.Engr_ID

I have no idea why you are using a LEFT JOIN in this query.

If I were doing this I would look at adding the ENGR_Names table to the
FROM clause of the query. Something like:

SELECT ..., ENGR_NAMES.Last_Name
FROM Main_Table INNER JOIN ENGR_NAMES
ON Main_Table.Engr_ID = ENGR_NAMES.Engr_ID



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[nogreatnamesleft]

I wasn't clear on why I couldn't just join them with the drag-n-drop arrows.
It is because the main table has multiple fields that reference people all
from the same table of names. I don't think I can link a bunch of fields from
the main table to the key of the names table. Maybe I'm still not explaining
it clearly, but your suggestion worked perfectly.

 

[John Spencer]

If you need to reference the Engr_Names table multiple times, you
include the table multiple times and link from the Main_Table to the
different references to the Engr_Names table.

So from Main_Table.Engr_ID to Engr_Names.Engr_ID would be one link to
one copy. And then from Main_Table.ConsultingEngrID to
Engr_Names_1.Engr_ID (for instance) would be a second link to a second
copy of the Engr_Names table.

Access will name the second instance of the table Engr_Names_1, and the
third instance Engr_Names_2, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================