Problem nesting one SQL in another

[cinnie]

hello to all

This is a general question about nesting one SQL in another. The example is
just made up to help me understand. Start with 2 queries called qryA and
qryB.

qryA: SELECT tblP.PName, tblP.PNumber
FROM tblP
ORDER BY tblP.PNumber DESC, tblP.PName;

qryB: SELECT DISTINCT tblP.PNumber
FROM tblP
ORDER BY tblP.PNumber DESC;

In qryC, I want to incorporate qryB in qryA, something like

qryC: SELECT tblP.PName, tblP.PNumber, (SELECT Count(*) FROM [qryB] ...etc

This works fine. The next thing I want to do is replace the [qryB] in the
line above with the SQL for qryB. Now comes my difficulty. qryB had its
Query Properies/Unique Value set to 'Yes'. When I replace [qryB] with its
SQL, how do I communicate that the Unique Value should be set to 'Yes' for
this part of the SQL?

thanks in advance

 

[Sylvain Lafontaine]

Use the word DISTINCT after SELECT for the Unique Values property and
DISTINCTROW for the Unique Records property.

You can notice that this word has already been added to the sql string for
qryB.

 

[cinnie]

thanks Sylvain
I hadn't realizes that DISTINCT would automatically set this property. My
mistake