[mso] Re: AccessSQL

Simplest=20way=20would=20be=20to=20do=20a=20query=20that=20brought=20back=20=
latest=20(i.e.=20max=20comid)=20comid=20with=20"em"=20in=20where=20(or=20c=
riteria=20in=20design=20view).=20Do=20the=20same=20again=20in=20a=20new=20=
query=20but=20with=20"ph"=20in=20where.=20So=20far=20this=20can=20be=20don=
e=20using=20design=20view.=20Next=20switch=20both=20queries=20to=20sql=20v=
iew.=20copy=20the=20code=20from=20the=20first=20query=20into=20a=20new=20s=
ql=20query=20window.=20Remove=20the=20semi=20colon=20put=20in=20a=20cartri=
dge=20return=20or=202=20(for=20readability)=20insert=20the=20word=20"union=
"=20again=20add=20a=20couple=20of=20lines=20space.=20Then=20copy=20the=20s=
econd=20sql=20query=20statement.=20This=20time=20make=20sure=20you=20have=20=
the=20semi=20colon=20terminating=20your=20statement.=20To=20make=20sure=20=
that=20"em"=20entry=20follows=20"ph"=20use=20the=20order=20by=20clause=20a=
nd=20order=20by=20UID=20(or=20other=20fields=20if=20you=20are=20combining=20=
with=20other=20tables).=20Not=20the=20most=20elegant=20solution=20but=20sh=
ould=20work=20well=20enough=20and=20doesn't=20require=20much=20skill.

Hope=20that=20helps

Clint


-----Original=20Message-----
From:=20mso-bounce@xxxxxxxxxxxxx=20[mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf=20Of=20Glenda=20Wells
Sent:=2019=20July=202005=2020:20
To:=20mso@xxxxxxxxxxxxx
Subject:=20[mso]=20Re:=20AccessSQL=20


Thanks.=20=20It's=20a=20place=20to=20start=20Jim.=20Naturally,=20there=20a=
re=20other=20scenarios
to=20workout=20like=20the=20kid=20who=20doesn't=20have=20any=20email=20add=
resses=20or=20the=20kid
who=20doesn't=20provide=20a=20telephone=20number.=20I=20think=20I=20need=20=
a=20couple=20of=20OR
statements.=20Sheesh,=20this=20stuff=20can=20get=20really=20complicated!..=
.it's=20gonna
be=20a=20lonnngh=20week.

-----Original=20Message-----
From:=20Jim=20Pettit=20[mailto:jimpettit@xxxxxxxxx]=3D20
Sent:=20Monday,=20July=2018,=202005=2006:11=20PM
To:=20mso@xxxxxxxxxxxxx
Subject:=20[mso]=20Re:=20AccessSQL=3D20

Glenda--

How=20about=20this?:

SELECT=20tblCOMM.UID,=20em.comm=20AS=20email,=20tblCOMM.comm=20AS=20phone=20=
FROM=20tblCOMM
INNER=20JOIN=20[SELECT=20tblCOMM.UID,=20tblCOMM.commID,=20tblCOMM.comm=20F=
ROM
tblCOMM=20WHERE=20(((tblCOMM.commID)=20In=20(SELECT=20max(commid)=20from=20=
tblComm
where
type=3D3D'em'))=20AND=20((tblCOMM.type)=3D3D"em"))].=20AS=20em=20ON=20tblC=
OMM.UID=20=3D3D=20=3D
em.UID
WHERE=20(((tblCOMM.type)=3D3D"ph")=20AND=20((tblCOMM.active)=3D3D-1));

--Jim=3D20

-----Original=20Message-----
From:=20mso-bounce@xxxxxxxxxxxxx=20[mailto:mso-bounce@xxxxxxxxxxxxx]=20On
Behalf=20Of=20Glenda=20Wells
Sent:=20Monday,=20July=2018,=202005=208:47=20AM
To:=20mso@xxxxxxxxxxxxx
Subject:=20[mso]=20AccessSQL=3D20

I'm=20still=20feeling=20my=20way=20around=20writing=20SQL=20queries=20in=20=
reports=20and
appreciating=20all=20ya'll=20helping.

I=20have=20telephone=20and=20email=20in=20the=20same=20table

I'd=20like=20to=20extract=20the=20most=20recent=20email=20by=20commID=20(t=
his=20would=20be=20the
maximum=20record)=20and=20the=20Active=20telephone=20of=20which=20there=20=
can=20only=20be=20one.

I=20THINK=20this=20needs=20a=20query/subquery=20with=20a=20join=20to=20get=
=20the=20max=20email=20as
well=20as=20another=20subquery=20to=20get=20the=20active=20phone.

Here's=20what=20the=20table=20looks=20like

tblCOMM
UID=09commID=09type=09active=09comm
A001=091001=09=09em=09=09=09gwells@xxxxxxxxxxx
A001=091002=09=09em=09=09=09gwells@xxxxxxx
A001=091003=09=09em=09=09=09gwells@xxxxxxx
A001=091004=09=09ph=09-1=09=09386-822-7088
A001=091005=09=09ph=09=09=09386-822-7126

My=20desired=20result=20is
UID,=20email,=20phone
A001,=20gwells@xxxxxxx,=20386-822-7088


I=20have=20this=20which=20(I=20hope)=20gets=20me=20the=20email=20address=20=
but=20how=20do=20I=20get
phone=20number=20too?

SELECT=20tblCOMM.UID,=20tblCOMM.comm
FROM=20tblCOMM
WHERE=20(((tblCOMM.UID)=3D3D3D(SELECT
max(b.commID)=20FROM=20tblCOMM=20b=20WHERE=20b.UID=20=3D3D3D=20tblCOMM.UID=
))=20AND
((tblCOMM.type)=3D3D3D"em"));


*************************************************************
You=20are=20receiving=20this=20mail=20because=20you=20subscribed=20to=20ms=
o@xxxxxxxxxxxxx
or=20MicrosoftOffice@xxxxxxxxxxxxxxxx

To=20send=20mail=20to=20the=20group,=20simply=20address=20it=20to=20mso@fr=
eelists.org

To=20Unsubscribe=20from=20this=20group,=20send=20an=20email=20to
mso-request@xxxxxxxxxxxxx=20with=20the=20word=20"unsubscribe"=20(without=20=
the
quotes)=20in=20the=20subject=20line.

Or,=20visit=20the=20group's=20homepage=20and=20use=20the=20dropdown=20menu=
.=20=20This=20will
also=20allow=20you=20to=20change=20your=20email=20settings=20to=20digest=20=
or=20vacation=20(no
mail).
http://www.freelists.org/webpage/mso

To=20be=20able=20to=20use=20the=20files=20section=20for=20sharing=20files=20=
with=20the=20group,
send=20a=20request=20to=20mso-moderators@xxxxxxxxxxxxx=20and=20you=20will=20=
be=20sent=20an
invitation=20with=20instructions.=20=20Once=20you=20are=20a=20member=20of=20=
the=20files=20group,
you=20can=20go=20here=20to=20upload/download=20files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

*************************************************************
You=20are=20receiving=20this=20mail=20because=20you=20subscribed=20to=20ms=
o@xxxxxxxxxxxxx
or=20MicrosoftOffice@xxxxxxxxxxxxxxxx

To=20send=20mail=20to=20the=20group,=20simply=20address=20it=20to=20mso@fr=
eelists.org

To=20Unsubscribe=20from=20this=20group,=20send=20an=20email=20to
mso-request@xxxxxxxxxxxxx=20with=20the=20word=20"unsubscribe"=20(without=20=
the
quotes)=20in=20the=20subject=20line.

Or,=20visit=20the=20group's=20homepage=20and=20use=20the=20dropdown=20menu=
.=20=20This=20will
also=20allow=20you=20to=20change=20your=20email=20settings=20to=20digest=20=
or=20vacation=20(no
mail).
http://www.freelists.org/webpage/mso

To=20be=20able=20to=20use=20the=20files=20section=20for=20sharing=20files=20=
with=20the=20group,
send=20a=20request=20to=20mso-moderators@xxxxxxxxxxxxx=20and=20you=20will=20=
be=20sent=20an
invitation=20with=20instructions.=20=20Once=20you=20are=20a=20member=20of=20=
the=20files=20group,
you=20can=20go=20here=20to=20upload/download=20files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

*************************************************************
You=20are=20receiving=20this=20mail=20because=20you=20subscribed=20to=20ms=
o@xxxxxxxxxxxxx=20or=20MicrosoftOffice@yahoogroups.com.

To=20send=20mail=20to=20the=20group,=20simply=20address=20it=20to=20mso@fr=
eelists.org

To=20Unsubscribe=20from=20this=20group,=20send=20an=20email=20to=20
mso-request@xxxxxxxxxxxxx=20with=20the=20word=20"unsubscribe"=20(without=20=
the=20quotes)=20in=20the=20subject=20line.

Or,=20visit=20the=20group's=20homepage=20and=20use=20the=20dropdown=20menu=
.=20=20This=20will=20also=20allow=20you=20to=20change=20your=20email=20set=
tings=20to=20digest=20or=20vacation=20(no=20mail).
http://www.freelists.org/webpage/mso

To=20be=20able=20to=20use=20the=20files=20section=20for=20sharing=20files=20=
with=20the=20group,=20send=20a=20request=20to=20mso-moderators@xxxxxxxxxxx=
rg=20and=20you=20will=20be=20sent=20an=20invitation=20with=20instructions.=
=20=20Once=20you=20are=20a=20member=20of=20the=20files=20group,=20you=20ca=
n=20go=20here=20to=20upload/download=20files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

This=20message=20is=20intended=20only=20for=20the=20use=20of=20the=20perso=
n(s)=20("the=20intended=20recipient(s)")=20to=20whom=20it=20is=20addressed=
.=20=20It=20may=20contain=20information=20that=20is=20privileged=20and=20c=
onfidential=20within=20the=20meaning=20of=20applicable=20law.=20=20If=20yo=
u=20are=20not=20the=20intended=20recipient,=20please=20contact=20the=20sen=
der=20as=20soon=20as=20possible.=20=20The=20views=20expressed=20in=20this=20=
communication=20may=20not=20necessarily=20be=20the=20views=20held=20by=20L=
iberty=20Syndicate=20Management=20Limited.

This=20message=20may=20contain=20material=20non-public=20information.=20=20=
As=20such=20it=20should=20not=20be=20used=20for=20the=20purchase=20or=20sa=
le=20of=20any=20securities=20issued=20by=20either=20the=20company=20or=20t=
he=20Liberty=20Mutual=20Group,=20or=20their=20respective=20subsidiaries=20=
or=20affiliates.=20=20Such=20prohibited=20use=20may=20subject=20one=20to=20=
both=20criminal=20and=20civil=20penalties=20under=20governing=20laws.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: