[mso] Re: SQL in Access :VSMail mx1
- From: "Glenda Wells" <gwells@xxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Fri, 4 Mar 2005 16:04:03 -0500
New problem:
I can get this to work from SQL in a query. How can I get it to work
from a report...is possible? I tried putting it on an unbound control
but it comes back & says the sttement is too long or contains invalid
characters.
-----Original Message-----
From: Glenda Wells=20
Sent: Friday, March 04, 2005 03:43 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: SQL in Access :VSMail mx1
I THINK this is it!!
select query1.* from
(SELECT caddrNmbr, max(caddr.caddrID) AS CID
FROM caddr
GROUP BY caddr.caddrNmbr) query1,
(select max(query2.CID) as maxCID
from (SELECT caddrNmbr, max(caddr.caddrID) AS CID
FROM caddr
GROUP BY caddr.caddrNmbr) query2) query3 where query1.CID =3D3D
query3.maxCID; =3D20
-----Original Message-----
From: James LaBorde [mailto:jlaborde@xxxxxxxxx]=3D20
Sent: Friday, March 04, 2005 02:47 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: SQL in Access :VSMail mx1
Glenda,
I would try it in a sub query first. Bring in the personID, caddrID, =
=3D
=3D3D and caddrType. Change the type to an aggregate query. Leave the
=3D3D PersonID setting as Group By, Change the caddrID to Max. Then
uncheck =3D3D the box to show caddrType, change it to Where and enter
"email" as your =3D3D criteria. This will give you a list of all
PersonIDs with the highest =3D3D caddrID associated with an email. You
can then use this to link back to =3D3D the data in your original table.
James=3D3D20
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Glenda Wells
Sent: Friday, March 04, 2005 11:08 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] SQL in Access :VSMail mx1
I'm trying to write this little thing in Access SQL and can't seem to
get it. =3D3D20 =3D3D20 The table is called CADDR (communication =
address) =3D
The table contains email addresses and telephone numbers. Can have
multiple of each.
Each address or number has it's own ID.
Like this:
=3D3D20
personID, caddrID, caddrNmbr, caddrType
smthJhn, 00_01, (123) 456-7890, phone
smthJhn, 00_02, jsmith@xxxxxxxxx, email
smthJhn, 00_03, jsmith_01@xxxxxxxxx, email =3D3D20 What I need to pull =
via
SQL or from a report linked directly to the table is the email address
with the highest caddrID. In this example, the desired result is =3D3D20
smthJhn, 00_03, jsmith_01@xxxxxxxxxx
*************************************************************
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).
//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
*************************************************************
*************************************************************
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).
//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:
