|
| 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. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 12th, 2007, 09:05 PM
posted to microsoft.public.access.queries
|
|||
|
|||
select distinct
I want to add:
SELECT DISTINCT [RIVER SCHOOL].email1 FROM [RIVER SCHOOL]; to the following query in an accrees database but I just can't seem to get the syntax right SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname, [River School].Sent, [River School].Dad, [River School].Mom, [River School].Student, [River School].Last, [River School].grade, [River School].Address, [River School].Phone1, [River School].Phone2, [River School].email1, [River School].email2, [River School].City, [River School].State, [River School].Zip, [River School].MomLast, [River School].DadLast FROM [River School] WHERE ((([River School].Sent) Is Null)) ORDER BY [River School].email1; 
|
|
#2
February 13th, 2007, 12:52 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
select distinct
I'll recommend again that you use a query design view, rather than trying to
create the SQL statement directly. Or perhaps one of the other newsgroup readers can offer raw SQL suggestions... -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... I want to add: SELECT DISTINCT [RIVER SCHOOL].email1 FROM [RIVER SCHOOL]; to the following query in an accrees database but I just can't seem to get the syntax right SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname, [River School].Sent, [River School].Dad, [River School].Mom, [River School].Student, [River School].Last, [River School].grade, [River School].Address, [River School].Phone1, [River School].Phone2, [River School].email1, [River School].email2, [River School].City, [River School].State, [River School].Zip, [River School].MomLast, [River School].DadLast FROM [River School] WHERE ((([River School].Sent) Is Null)) ORDER BY [River School].email1;
|
|
#3
February 13th, 2007, 04:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
select distinct
In design view please show me the exact expression for the criteria in
[email1] so that I get select distinct. eff Boyce" wrote: I'll recommend again that you use a query design view, rather than trying to create the SQL statement directly. Or perhaps one of the other newsgroup readers can offer raw SQL suggestions... -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... I want to add: SELECT DISTINCT [RIVER SCHOOL].email1 FROM [RIVER SCHOOL]; to the following query in an accrees database but I just can't seem to get the syntax right SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname, [River School].Sent, [River School].Dad, [River School].Mom, [River School].Student, [River School].Last, [River School].grade, [River School].Address, [River School].Phone1, [River School].Phone2, [River School].email1, [River School].email2, [River School].City, [River School].State, [River School].Zip, [River School].MomLast, [River School].DadLast FROM [River School] WHERE ((([River School].Sent) Is Null)) ORDER BY [River School].email1;
|
|
#4
February 14th, 2007, 02:05 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
select distinct
Set the Unique Values property to Yes.
-- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... In design view please show me the exact expression for the criteria in [email1] so that I get select distinct. eff Boyce" wrote: I'll recommend again that you use a query design view, rather than trying to create the SQL statement directly. Or perhaps one of the other newsgroup readers can offer raw SQL suggestions... -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... I want to add: SELECT DISTINCT [RIVER SCHOOL].email1 FROM [RIVER SCHOOL]; to the following query in an accrees database but I just can't seem to get the syntax right SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname, [River School].Sent, [River School].Dad, [River School].Mom, [River School].Student, [River School].Last, [River School].grade, [River School].Address, [River School].Phone1, [River School].Phone2, [River School].email1, [River School].email2, [River School].City, [River School].State, [River School].Zip, [River School].MomLast, [River School].DadLast FROM [River School] WHERE ((([River School].Sent) Is Null)) ORDER BY [River School].email1;
|
|
#5
February 14th, 2007, 04:52 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
select distinct
I see where I can set yes for unique values for the entire record, but not
where I can set unique vales for a specific field: [email1] "Jeff Boyce" wrote: Set the Unique Values property to Yes. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... In design view please show me the exact expression for the criteria in [email1] so that I get select distinct. eff Boyce" wrote: I'll recommend again that you use a query design view, rather than trying to create the SQL statement directly. Or perhaps one of the other newsgroup readers can offer raw SQL suggestions... -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... I want to add: SELECT DISTINCT [RIVER SCHOOL].email1 FROM [RIVER SCHOOL]; to the following query in an accrees database but I just can't seem to get the syntax right SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname, [River School].Sent, [River School].Dad, [River School].Mom, [River School].Student, [River School].Last, [River School].grade, [River School].Address, [River School].Phone1, [River School].Phone2, [River School].email1, [River School].email2, [River School].City, [River School].State, [River School].Zip, [River School].MomLast, [River School].DadLast FROM [River School] WHERE ((([River School].Sent) Is Null)) ORDER BY [River School].email1;
|
|
#6
February 14th, 2007, 06:25 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
select distinct
What do you want to get back in your query? Do you want one email
address or do you want multiple email addresses? If you want one email address per Student, does it make any difference to you which one? You might be able to use an aggregate query to get the results you want. SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname , [River School].Sent , [River School].Dad , [River School].Mom , [River School].Student , [River School].Last , [River School].grade , [River School].Address , [River School].Phone1 , [River School].Phone2 , FIRST([River School].email1) as OneEmail , FIRST([River School].email2) as SecondEmail , [River School].City , [River School].State , [River School].Zip , [River School].MomLast , [River School].DadLast FROM [River School] WHERE [River School].Sent) Is Null GROUP BY IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname , [River School].Sent , [River School].Dad , [River School].Mom , [River School].Student , [River School].Last , [River School].grade , [River School].Address , [River School].Phone1 , [River School].Phone2 , [River School].City , [River School].State , [River School].Zip , [River School].MomLast , [River School].DadLast ORDER BY First([River School].email1); By the way, you could probably make the calculation of ggwName simpler using this expression. IIF([Mom] is not Null and [Dad] is not null, [dad] & " and " & [mom], Nz([Mom],[Dad])) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === sierralightfoot wrote: I see where I can set yes for unique values for the entire record, but not where I can set unique vales for a specific field: [email1] "Jeff Boyce" wrote: Set the Unique Values property to Yes. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... In design view please show me the exact expression for the criteria in [email1] so that I get select distinct. eff Boyce" wrote: I'll recommend again that you use a query design view, rather than trying to create the SQL statement directly. Or perhaps one of the other newsgroup readers can offer raw SQL suggestions... -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "sierralightfoot" wrote in message ... I want to add: SELECT DISTINCT [RIVER SCHOOL].email1 FROM [RIVER SCHOOL]; to the following query in an accrees database but I just can't seem to get the syntax right SELECT IIf(IsNull([mom]),[dad],IIf(IsNull([dad]),[mom],IIf([dad]="A" And [mom]="A",[dad] & " " & "and" & " " & [mom]))) AS ggwname, [River School].Sent, [River School].Dad, [River School].Mom, [River School].Student, [River School].Last, [River School].grade, [River School].Address, [River School].Phone1, [River School].Phone2, [River School].email1, [River School].email2, [River School].City, [River School].State, [River School].Zip, [River School].MomLast, [River School].DadLast FROM [River School] WHERE ((([River School].Sent) Is Null)) ORDER BY [River School].email1;
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
