Query alternatives

[Graham]

I want to run a query of certain records together with an email address. I
may have a 'Personal Email address', a 'Business Email adress', and possibly
a 'Third Email address'. I may have only one of these fileds completed for
each record, or all three!. When I run a query can I make it search 'PEA'
first, and if it doesn't find one, look at 'BEA' and if it doesn't find one
there, look at 'TEA' ? Importantly I only want the Query to return a single
address, that i may then run a an email merge from it.
The basic Query works fine, it's just that I can only make it look in one
field at a time.
Many Thanks.

 

[John Spencer]

Well, if your fields are NULL, you should be able to use the expression

Nz([PEA],Nz([BEA],[TEA]))

That will return PEA unless it is null. If PEA is null then the function
will return BEA unless that is null, in which case it will return TEA.

If the field can be null or it can contain a zero-length string, then the
expression becomes a bit more complex

IIF(Len([PEA] & "") > 0, [PEA], IIF(Len([BEA] & "") >0, [BEA], [TEA]))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

Something like below should work in a query. Actually you may have problems
if there are no email addresses.

TheEmail: IIf(IsNull([Personal Email address])=False,[Personal Email
address],IIf(IsNull([Business Email address])=False,[Business Email
address],[Third Email address]))

HOWEVER what happens when you add a fourth email address? Fifth? You would
need to modify the IIf statements above and that can get ugly and slow.

Adding columns across the table like so is a sign of bad normalization.
Instead you should have another table of email address looking something like
so:

EM_ID Person_FK EmailAddress EmailType

The EM_ID is the primary key for the table. An autonumber would do.
Person_FK is the foriegn key field to the table about the people or client
such as there name, address, etc. EmailAddress is, well, the email address.
Lastly the EmailType has things like Business, Home, etc.

You would join your tables and readily get the emails using a Group By on
the Person_FK and choosing something like First or Max on the EmailAddress.
Also if you just want Business emails, you could do criteria on the EmailType
field.

 

[Graham]

Thank you for your reply Jerry. I shouldn't have more than the three email
fields and the query should return <50 records, so speed shouldn't be an
issue (?)
Where should this code be placed to try it out ?
I've come across this 'normalisation' issue in various postings. Are you
saying that it is better to have a separate table of perhaps only 50 entries,
rather than have an additional column on the single table ?

Something like below should work in a query. Actually you may have problems
if there are no email addresses.

TheEmail: IIf(IsNull([Personal Email address])=False,[Personal Email
address],IIf(IsNull([Business Email address])=False,[Business Email
address],[Third Email address]))

HOWEVER what happens when you add a fourth email address? Fifth? You would
need to modify the IIf statements above and that can get ugly and slow.

Adding columns across the table like so is a sign of bad normalization.
Instead you should have another table of email address looking something like
so:

EM_ID Person_FK EmailAddress EmailType

The EM_ID is the primary key for the table. An autonumber would do.
Person_FK is the foriegn key field to the table about the people or client
such as there name, address, etc. EmailAddress is, well, the email address.
Lastly the EmailType has things like Business, Home, etc.

You would join your tables and readily get the emails using a Group By on
the Person_FK and choosing something like First or Max on the EmailAddress.
Also if you just want Business emails, you could do criteria on the EmailType
field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I want to run a query of certain records together with an email address. I
may have a 'Personal Email address', a 'Business Email adress', and possibly
a 'Third Email address'. I may have only one of these fileds completed for
each record, or all three!. When I run a query can I make it search 'PEA'
first, and if it doesn't find one, look at 'BEA' and if it doesn't find one
there, look at 'TEA' ? Importantly I only want the Query to return a single
address, that i may then run a an email merge from it.
The basic Query works fine, it's just that I can only make it look in one
field at a time.
Many Thanks.

 

[Graham]

Thank you for your reply John, your solution appears simpler than Jerry's !
Where should this expression be placed, that I may try it out ? What is the
distinction between 'Null' & 'Zero string' ? It is likely that there will be
empty row entries for all three fields, which would this therefore be ?

Well, if your fields are NULL, you should be able to use the expression

Nz([PEA],Nz([BEA],[TEA]))

That will return PEA unless it is null. If PEA is null then the function
will return BEA unless that is null, in which case it will return TEA.

If the field can be null or it can contain a zero-length string, then the
expression becomes a bit more complex

IIF(Len([PEA] & "") > 0, [PEA], IIF(Len([BEA] & "") >0, [BEA], [TEA]))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to run a query of certain records together with an email address. I
may have a 'Personal Email address', a 'Business Email adress', and
possibly
a 'Third Email address'. I may have only one of these fileds completed for
each record, or all three!. When I run a query can I make it search 'PEA'
first, and if it doesn't find one, look at 'BEA' and if it doesn't find
one
there, look at 'TEA' ? Importantly I only want the Query to return a
single
address, that i may then run a an email merge from it.
The basic Query works fine, it's just that I can only make it look in one
field at a time.
Many Thanks.

 

[John Spencer]

You can put the expression in the query as a calculated column

Field: TheEmailAddress: Nz([PEA],Nz([BEA],[TEA]))

Null is no value at all.
Zero-length string is a value - a string with no characters in it. "" is a
zero length string.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you for your reply John, your solution appears simpler than Jerry's
!
Where should this expression be placed, that I may try it out ? What is
the
distinction between 'Null' & 'Zero string' ? It is likely that there will
be
empty row entries for all three fields, which would this therefore be ?

Well, if your fields are NULL, you should be able to use the expression

Nz([PEA],Nz([BEA],[TEA]))

That will return PEA unless it is null. If PEA is null then the function
will return BEA unless that is null, in which case it will return TEA.

If the field can be null or it can contain a zero-length string, then the
expression becomes a bit more complex

IIF(Len([PEA] & "") > 0, [PEA], IIF(Len([BEA] & "") >0, [BEA], [TEA]))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to run a query of certain records together with an email address.
I
may have a 'Personal Email address', a 'Business Email adress', and
possibly
a 'Third Email address'. I may have only one of these fileds completed
for
each record, or all three!. When I run a query can I make it search
'PEA'
first, and if it doesn't find one, look at 'BEA' and if it doesn't find
one
there, look at 'TEA' ? Importantly I only want the Query to return a
single
address, that i may then run a an email merge from it.
The basic Query works fine, it's just that I can only make it look in
one
field at a time.
Many Thanks.

 

[Graham]

Many thanks for your help again John. The fields SHOULD only ever be an email
address OR a Null value.
The expression throws up a Syntax error, It doesn't appear to like the
commas ? I've tried substituting semi colons and/or full stops to no avail.
I've also rebuilt the expression using the expression builder, see below, but
to no avail. can you spot where I'm going wrong ?

Nz («expr», «valueifnull») «Expr»( [Personal email] «Expr», Nz («expr»,
«valueifnull») «Expr» ([Business email], «Expr» [Agency email] ))

You can put the expression in the query as a calculated column

Field: TheEmailAddress: Nz([PEA],Nz([BEA],[TEA]))

Null is no value at all.
Zero-length string is a value - a string with no characters in it. "" is a
zero length string.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you for your reply John, your solution appears simpler than Jerry's
!
Where should this expression be placed, that I may try it out ? What is
the
distinction between 'Null' & 'Zero string' ? It is likely that there will
be
empty row entries for all three fields, which would this therefore be ?

Well, if your fields are NULL, you should be able to use the expression

Nz([PEA],Nz([BEA],[TEA]))

That will return PEA unless it is null. If PEA is null then the function
will return BEA unless that is null, in which case it will return TEA.

If the field can be null or it can contain a zero-length string, then the
expression becomes a bit more complex

IIF(Len([PEA] & "") > 0, [PEA], IIF(Len([BEA] & "") >0, [BEA], [TEA]))
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to run a query of certain records together with an email address.
I
may have a 'Personal Email address', a 'Business Email adress', and
possibly
a 'Third Email address'. I may have only one of these fileds completed
for
each record, or all three!. When I run a query can I make it search
'PEA'
first, and if it doesn't find one, look at 'BEA' and if it doesn't find
one
there, look at 'TEA' ? Importantly I only want the Query to return a
single
address, that i may then run a an email merge from it.
The basic Query works fine, it's just that I can only make it look in
one
field at a time.
Many Thanks.

 

[John Spencer]

Question is your database an .mdb or is an adb. The query syntax can vary
a bit between the two.

Try using the IIF structure - use semi-colons if that is your normal
delimiter, otherwise use commas.

IIF([PEA] is Not Null; [PEA]; IIF([BEA] is not Null;[BEA];[TEA]))

If that fails post back with a copy of the SQL statement (menu VIEW: SQL)
and the information on your data source type

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Many thanks for your help again John. The fields SHOULD only ever be an
email
address OR a Null value.
The expression throws up a Syntax error, It doesn't appear to like the
commas ? I've tried substituting semi colons and/or full stops to no
avail.
I've also rebuilt the expression using the expression builder, see below,
but
to no avail. can you spot where I'm going wrong ?

Nz («expr», «valueifnull») «Expr»( [Personal email] «Expr», Nz («expr»,
«valueifnull») «Expr» ([Business email], «Expr» [Agency email] ))

You can put the expression in the query as a calculated column

Field: TheEmailAddress: Nz([PEA],Nz([BEA],[TEA]))

Null is no value at all.
Zero-length string is a value - a string with no characters in it. "" is
a
zero length string.

 

[Graham]

Many Thanks John, Works a treat!! Just had to change the ; for a ,

Expr1: IIf([Personal email] Is Not Null,[Personal email],IIf([Business
email] Is Not Null,[Business email],[Agency email]))

Question is your database an .mdb or is an adb. The query syntax can vary
a bit between the two.

Try using the IIF structure - use semi-colons if that is your normal
delimiter, otherwise use commas.

IIF([PEA] is Not Null; [PEA]; IIF([BEA] is not Null;[BEA];[TEA]))

If that fails post back with a copy of the SQL statement (menu VIEW: SQL)
and the information on your data source type

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Many thanks for your help again John. The fields SHOULD only ever be an
email
address OR a Null value.
The expression throws up a Syntax error, It doesn't appear to like the
commas ? I've tried substituting semi colons and/or full stops to no
avail.
I've also rebuilt the expression using the expression builder, see below,
but
to no avail. can you spot where I'm going wrong ?

Nz («expr», «valueifnull») «Expr»( [Personal email] «Expr», Nz («expr»,
«valueifnull») «Expr» ([Business email], «Expr» [Agency email] ))

You can put the expression in the query as a calculated column

Field: TheEmailAddress: Nz([PEA],Nz([BEA],[TEA]))

Null is no value at all.
Zero-length string is a value - a string with no characters in it. "" is
a
zero length string.