A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problem nesting one SQL in another



 
 
Thread Tools Display Modes
  #1  
Old February 21st, 2007, 01:35 AM posted to microsoft.public.access.queries
cinnie
external usenet poster
 
Posts: 67
Default Problem nesting one SQL in another

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

--
cinnie
  #2  
Old February 21st, 2007, 02:17 AM posted to microsoft.public.access.queries
Sylvain Lafontaine
external usenet poster
 
Posts: 528
Default Problem nesting one SQL in another

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"cinnie" wrote in message
...
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

--
cinnie



  #3  
Old February 21st, 2007, 03:33 AM posted to microsoft.public.access.queries
cinnie
external usenet poster
 
Posts: 67
Default Problem nesting one SQL in another

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.