Encapsulated queries and parameters

[Ralf]

Hi All,

I have the following two queries
qryTableA: SELECT columnA, (SELECT * FROM qryTableB) FROM tableA;
qryTableB: SELECT COUNT(columnB) FROM tableB WHERE columnB=[COLUMNA_VALUE];

Now, as you can see I have the parameter 'COLUMNA_VALUE' in qryTableB. This
parameter should get assigned the value of the actual columnA cell. Is this
possible? I tried to modify qryTableA into
SELECT columnA, (SELECT * FROM qryTableB(columnA)) FROM tableA;

But all my attempts failed.

Your help is much appreciated!
Thank you, Ralf

 

[Marshall Barton]

I have the following two queries
qryTableA: SELECT columnA, (SELECT * FROM qryTableB) FROM tableA;
qryTableB: SELECT COUNT(columnB) FROM tableB WHERE columnB=[COLUMNA_VALUE];

Now, as you can see I have the parameter 'COLUMNA_VALUE' in qryTableB. This
parameter should get assigned the value of the actual columnA cell. Is this
possible? I tried to modify qryTableA into
SELECT columnA, (SELECT * FROM qryTableB(columnA)) FROM tableA;

That's not too far off. Try it this way:

qryTableA:

SELECT tableA.columnA, (SELECT Count(columnB)
FROM TableB
WHERE TableB.columnB=tableA.columnA
) As CountOfColumnB
FROM tableA

Note that your second query is not needed.

Actually, all that seems rather roundabout. I think this
may do pretty much the same thing more efficiently:

SELECT tableA.columnA, Count(columnB) As CountOfColumnB
FROM tableA INNER JOIN TableB
ON TableB.columnB=tableA.columnA
GROUP BY tableA.columnA

 

[Ralf]

Hi Marshall,

Thank you for your answer.

I encapsulated the 2nd query by purpose. Actually, I have two different
queries which are much larger and more complex. Instead of posting those, I
decided to reduce them to highlight the problem I have.

I know that something like the following works:
qryTableA: SELECT columnA, (SELECT * FROM qryTableB) FROM tableA;
qryTableB: SELECT COUNT(columnB) FROM tableB;

When qryTableB does not require any parameter, everything is fine. So the
question is how to enable this 'parameter forwarding'?

Thank you,
Ralf

 

[John Spencer MVP]

The only way I can think of would be to add it to the sub query. I think what
you are trying to do is interesting, but technically not achievable.

You might be able to use VBA to construct the query SQL on the fly.

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

 

[Marshall Barton]

I encapsulated the 2nd query by purpose. Actually, I have two different
queries which are much larger and more complex. Instead of posting those, I
decided to reduce them to highlight the problem I have.

I know that something like the following works:
qryTableA: SELECT columnA, (SELECT * FROM qryTableB) FROM tableA;
qryTableB: SELECT COUNT(columnB) FROM tableB;

When qryTableB does not require any parameter, everything is fine. So the
question is how to enable this 'parameter forwarding'?

Ahh, the perils of an oversimplified example ;-)

I agree with John, Access/Jet queries just don't have that
capability.

Note that using (SELECT * FROM ...) in a query's field list
only works for the specific case where the subquery returns
a single field and a single record. Your example does that,
but you can not generalize it to a different kind of
subquery, That's why I suggested doing the Count in the
subquery instead of a separate query.