Subquery Correllated In Select Clause - Is this possible?

[garrett.berneche]

I have a table(A) with a field (foreignFieldName) that holds the name of field in another table (B) and I want to build a Query/Subquery that returns the value of B.foreignFieldName for every entry in A. I would prefer doingthis in a native SQL way (a saved query) and avoid writing VBA code to iterate the data.

A
ID BID foreignFieldName
1 3 Length
2 2 Width
3 1 Height

B
ID Length Width Height
1 14 15 16
2 17 18 19
3 20 21 22

Desired ResultSet
ID Value
1 20
2 18
3 16

I have the following SQL right now -
SELECT A.ID,
"SELECT " + [A].[foreignFieldName] + " FROM B WHERE ID=" + Cstr([A].[ID]) FROM A;

Bad news, this puts an SQL string in ResultSet.Value. Good news, it is a well formed and ready to execute SQL string that looks like it would providethe correct result, if only it would execute.

I have also tried the following SQL
SELECT A.ID,
(SELECT A.foreignFieldName FROM B WHERE B.ID=A.ID) AS Value FROM A;
Sadly, this returns a copy of the A table - it evalutes A.foreignFieldName as a static string instead of a field name.

Does anyone know how I can accomplish my goal?

 

[Douglas J Steele]

This wouldn't have happened if table B was properly normalized!

Fortunately, you can create a Union query that normalizes the data, and join
that to table A to get the results:

SELECT A.ID, Subq.DimensionValue
FROM A INNER JOIN
(SELECT ID, "Length" AS Dimension, Length As DimensionValue
FROM B
UNION
SELECT ID, "Width", Width
FROM B
UNION
SELECT ID, "Height", Height
FROM B) AS Subq
ON A.ID = Subq.ID
AND A.foreignFieldName = Subq.DimensionValue

wrote in message

I have a table(A) with a field (foreignFieldName) that holds the name of
field in another table (B) and I want to build a Query/Subquery that returns
the value of B.foreignFieldName for every entry in A. I would prefer doing
this in a native SQL way (a saved query) and avoid writing VBA code to
iterate the data.

A
ID BID foreignFieldName
1 3 Length
2 2 Width
3 1 Height

B
ID Length Width Height
1 14 15 16
2 17 18 19
3 20 21 22

Desired ResultSet
ID Value
1 20
2 18
3 16

I have the following SQL right now -
SELECT A.ID,
"SELECT " + [A].[foreignFieldName] + " FROM B WHERE ID=" +
Cstr([A].[ID]) FROM A;

Bad news, this puts an SQL string in ResultSet.Value. Good news, it is a
well formed and ready to execute SQL string that looks like it would provide
the correct result, if only it would execute.

I have also tried the following SQL
SELECT A.ID,
(SELECT A.foreignFieldName FROM B WHERE B.ID=A.ID) AS Value FROM A;
Sadly, this returns a copy of the A table - it evalutes A.foreignFieldName
as a static string instead of a field name.

Does anyone know how I can accomplish my goal?

 

[garrett.berneche]

I was hoping for something more dynamic then the UNION query. The UNION query forces you to know the names of all the fields you might need to reference when you write the query. In truth, what I called B is a query that calculates several properties of a house.

This is more detail then I gave before, but it is still very much a simplified version of my structure...I have a table of houses, and among other things HOUSES captures length, width, height, joist spacing, stud spacing, roof pitch, etc. Each HOUSE also has several BOMs - lists of parts that are used to make specific sections of the house in specific ways. Some parts ina BOM are needed in a fixed quantity, but sometimes quantity is based on properties of the HOUSE and properties of the PART. I have a query, that isvery simplistically represented by B, where I am trying to calculate thosequantities, but now I need to match up the quantities with the BOMS.

I am working on a construction takes off system.

This wouldn't have happened if table B was properly normalized!



Fortunately, you can create a Union query that normalizes the data, and join

that to table A to get the results:



SELECT A.ID, Subq.DimensionValue

FROM A INNER JOIN

(SELECT ID, "Length" AS Dimension, Length As DimensionValue

FROM B

UNION

SELECT ID, "Width", Width

FROM B

UNION

SELECT ID, "Height", Height

FROM B) AS Subq

ON A.ID = Subq.ID

AND A.foreignFieldName = Subq.DimensionValue



wrote in message




I have a table(A) with a field (foreignFieldName) that holds the name of

field in another table (B) and I want to build a Query/Subquery that returns

the value of B.foreignFieldName for every entry in A. I would prefer doing

this in a native SQL way (a saved query) and avoid writing VBA code to

iterate the data.



A

ID BID foreignFieldName

1 3 Length

2 2 Width

3 1 Height



B

ID Length Width Height

1 14 15 16

2 17 18 19

3 20 21 22



Desired ResultSet

ID Value

1 20

2 18

3 16



I have the following SQL right now -

SELECT A.ID,

"SELECT " + [A].[foreignFieldName] + " FROM B WHERE ID=" +

Cstr([A].[ID]) FROM A;



Bad news, this puts an SQL string in ResultSet.Value. Good news, it is a

well formed and ready to execute SQL string that looks like it would provide

the correct result, if only it would execute.



I have also tried the following SQL

SELECT A.ID,

(SELECT A.foreignFieldName FROM B WHERE B.ID=A.ID) AS Value FROMA;

Sadly, this returns a copy of the A table - it evalutes A.foreignFieldName

as a static string instead of a field name.



Does anyone know how I can accomplish my goal?

 

[Douglas J Steele]

My point was that table B should be narrow (few fields) by deep (many rows),
as opposed to wide (many fields) by shallow (few rows). By having each
dimension as a separate row, what you're trying to do becomes simple. The
UNION query I suggested was simply a means of solving the problem now, while
you work on redesigning your tables.

wrote in message

I was hoping for something more dynamic then the UNION query. The UNION
query forces you to know the names of all the fields you might need to
reference when you write the query. In truth, what I called B is a query
that calculates several properties of a house.

This is more detail then I gave before, but it is still very much a
simplified version of my structure...I have a table of houses, and among
other things HOUSES captures length, width, height, joist spacing, stud
spacing, roof pitch, etc. Each HOUSE also has several BOMs - lists of parts
that are used to make specific sections of the house in specific ways. Some
parts in a BOM are needed in a fixed quantity, but sometimes quantity is
based on properties of the HOUSE and properties of the PART. I have a
query, that is very simplistically represented by B, where I am trying to
calculate those quantities, but now I need to match up the quantities with
the BOMS.

I am working on a construction takes off system.

This wouldn't have happened if table B was properly normalized!



Fortunately, you can create a Union query that normalizes the data, and
join

that to table A to get the results:



SELECT A.ID, Subq.DimensionValue

FROM A INNER JOIN

(SELECT ID, "Length" AS Dimension, Length As DimensionValue

FROM B

UNION

SELECT ID, "Width", Width

FROM B

UNION

SELECT ID, "Height", Height

FROM B) AS Subq

ON A.ID = Subq.ID

AND A.foreignFieldName = Subq.DimensionValue



wrote in message




I have a table(A) with a field (foreignFieldName) that holds the name of

field in another table (B) and I want to build a Query/Subquery that
returns

the value of B.foreignFieldName for every entry in A. I would prefer
doing

this in a native SQL way (a saved query) and avoid writing VBA code to

iterate the data.



A

ID BID foreignFieldName

1 3 Length

2 2 Width

3 1 Height



B

ID Length Width Height

1 14 15 16

2 17 18 19

3 20 21 22



Desired ResultSet

ID Value

1 20

2 18

3 16



I have the following SQL right now -

SELECT A.ID,

"SELECT " + [A].[foreignFieldName] + " FROM B WHERE ID=" +

Cstr([A].[ID]) FROM A;



Bad news, this puts an SQL string in ResultSet.Value. Good news, it is a

well formed and ready to execute SQL string that looks like it would
provide

the correct result, if only it would execute.



I have also tried the following SQL

SELECT A.ID,

(SELECT A.foreignFieldName FROM B WHERE B.ID=A.ID) AS Value FROM A;

Sadly, this returns a copy of the A table - it evalutes A.foreignFieldName

as a static string instead of a field name.



Does anyone know how I can accomplish my goal?