select distinct

S

sierralightfoot

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;
 
S

sierralightfoot

In design view please show me the exact expression for the criteria in
[email1] so that I get 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 said:
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;
Click to expand...
Click to expand...
 
J

Jeff Boyce

Set the Unique Values property to Yes.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

sierralightfoot said:
In design view please show me the exact expression for the criteria in
[email1] so that I get 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 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
S

sierralightfoot

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 said:
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 said:
In design view please show me the exact expression for the criteria in
[email1] so that I get 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/

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;
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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
'====================================================

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 said:
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 said:
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/

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;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select distinct 1
Query for Export 1
trying to get Word tables into Publisher 3
Problems Loading Large String Array into Array variable 6
Table schema critique needed 1
Check for existing date in record and duplicate record if date fou 0
Why am I still getting data??? 3
ADODB.Connection error '800a0e7c' 2

Top