Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2005

Tip: Looking for answers? Try searching our database.

Query Problem

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Secret Squirrel - 03 Nov 2005 04:07 GMT
I am trying to run a query that will choose specific info based on a criteria
I have on one field. The field is "PhoneType" and the selection criteria is
currently "Home" for home phone number. How do I list everyone that has
either a Home, Work, or Other phone type? If I word is like this "Home" or
"Work" or "Other" it lists the same person multiple times if they have more
than one of those types. How do I have it pull one person only once but be
able to pull people that have only one of those regardless of which one? Some
have just home, some have other, and some have work. Am I making sense here?
Ken Snell [MVP] - 03 Nov 2005 05:10 GMT
SELECT DISTINCT PersonID, PersonName
FROM TableName
WHERE PhoneType = "Home" OR
PhoneType = "Work" OR
PhoneType = "Other";

Signature

       Ken Snell
<MS ACCESS MVP>

>I am trying to run a query that will choose specific info based on a
>criteria
[quoted text clipped - 9 lines]
> have just home, some have other, and some have work. Am I making sense
> here?
Secret Squirrel - 03 Nov 2005 05:16 GMT
Will that prompt me to choose one or will it run automatically? I don't want
any prompts to select the criteria.

> SELECT DISTINCT PersonID, PersonName
> FROM TableName
[quoted text clipped - 15 lines]
> > have just home, some have other, and some have work. Am I making sense
> > here?
Secret Squirrel - 03 Nov 2005 05:37 GMT
I tried to use this code but it's not working. Do I just put this code into
the SQL statement after modifying it to my tables, etc.?

> SELECT DISTINCT PersonID, PersonName
> FROM TableName
[quoted text clipped - 15 lines]
> > have just home, some have other, and some have work. Am I making sense
> > here?
Secret Squirrel - 03 Nov 2005 05:51 GMT
Here is what the SQL looks like without these modifications. As you can see
it is only selecting the "Home" types.

SELECT DISTINCT tblNameList.ID, tblNameList.Last, tblNameList.First,
tblNameList.AddressLine1, tblNameList.AddressLine2, tblNameList.City,
tblNameList.State, tblNameList.Zip, tblNameList.Province,
tblNameList.Country, tblNameList.ClassYear, tblNameList.Comments,
tblPhones.PhoneNumber, tblPhoneTypes.PhoneType, tblNameList.Association,
tblNameList.StatusID, tblNameList.Suffix
FROM tblPhoneTypes INNER JOIN ((tblStatus INNER JOIN tblNameList ON
tblStatus.ID = tblNameList.StatusID) INNER JOIN tblPhones ON tblNameList.ID =
tblPhones.NameListID) ON tblPhoneTypes.ID = tblPhones.PhoneTypeID
WHERE (((tblPhoneTypes.PhoneType)="Home") AND ((tblNameList.Association)=-1)
AND ((tblNameList.StatusID)=1));

> SELECT DISTINCT PersonID, PersonName
> FROM TableName
[quoted text clipped - 15 lines]
> > have just home, some have other, and some have work. Am I making sense
> > here?
Ken Snell [MVP] - 03 Nov 2005 06:14 GMT
SELECT DISTINCT tblNameList.ID, tblNameList.Last, tblNameList.First,
tblNameList.AddressLine1, tblNameList.AddressLine2, tblNameList.City,
tblNameList.State, tblNameList.Zip, tblNameList.Province,
tblNameList.Country, tblNameList.ClassYear, tblNameList.Comments,
tblPhones.PhoneNumber, tblPhoneTypes.PhoneType, tblNameList.Association,
tblNameList.StatusID, tblNameList.Suffix
FROM tblPhoneTypes INNER JOIN ((tblStatus INNER JOIN tblNameList ON
tblStatus.ID = tblNameList.StatusID) INNER JOIN tblPhones ON tblNameList.ID
=
tblPhones.NameListID) ON tblPhoneTypes.ID = tblPhones.PhoneTypeID
WHERE ((((tblPhoneTypes.PhoneType)="Home") OR
tblPhoneTypes.PhoneType)="Work" OR
tblPhoneTypes.PhoneType)="Other") AND ((tblNameList.Association)=-1)
AND ((tblNameList.StatusID)=1));

Signature

       Ken Snell
<MS ACCESS MVP>

> Here is what the SQL looks like without these modifications. As you can
> see
[quoted text clipped - 37 lines]
>> > have just home, some have other, and some have work. Am I making sense
>> > here?
Secret Squirrel - 03 Nov 2005 06:23 GMT
Thanks for your help but now I'm getting a "data type mismatch in criteria
expression" error.

> SELECT DISTINCT tblNameList.ID, tblNameList.Last, tblNameList.First,
> tblNameList.AddressLine1, tblNameList.AddressLine2, tblNameList.City,
[quoted text clipped - 52 lines]
> >> > have just home, some have other, and some have work. Am I making sense
> >> > here?
John Spencer - 03 Nov 2005 14:04 GMT
I would try something like the following.  Note that I used the IN operator
and also removed the extra parentheses that Access added in the WHERE
clause.  Access will add them back, but when modifying queries I find it
easier to only put in the ones that are required.

SELECT DISTINCT tblNameList.ID, tblNameList.Last, tblNameList.First,
tblNameList.AddressLine1, tblNameList.AddressLine2, tblNameList.City,
tblNameList.State, tblNameList.Zip, tblNameList.Province,
tblNameList.Country, tblNameList.ClassYear, tblNameList.Comments,
tblPhones.PhoneNumber, tblPhoneTypes.PhoneType, tblNameList.Association,
tblNameList.StatusID, tblNameList.Suffix
FROM tblPhoneTypes INNER JOIN ((tblStatus INNER JOIN tblNameList ON
tblStatus.ID = tblNameList.StatusID) INNER JOIN tblPhones ON tblNameList.ID
=
tblPhones.NameListID) ON tblPhoneTypes.ID = tblPhones.PhoneTypeID
WHERE tblPhoneTypes.PhoneType In ("Home","Work","Other")
AND tblNameList.Association=-1
AND tblNameList.StatusID=1

> Here is what the SQL looks like without these modifications. As you can
> see
[quoted text clipped - 37 lines]
>> > have just home, some have other, and some have work. Am I making sense
>> > here?
Secret Squirrel - 03 Nov 2005 15:32 GMT
That didn't work either. I still get multiples from the same person.

> I would try something like the following.  Note that I used the IN operator
> and also removed the extra parentheses that Access added in the WHERE
[quoted text clipped - 56 lines]
> >> > have just home, some have other, and some have work. Am I making sense
> >> > here?
John Spencer - 03 Nov 2005 17:24 GMT
Since you are returning values in the select clause from tblPhoneTypes you
are going to get one record for each phone number and for each phone type.
Which phone number do you want to return?  If it doesn't make a difference
then you can use a Totals query and FIRST against any items in tblPhones.

Something like the following.

SELECT tblNameList.ID, tblNameList.Last, tblNameList.First,
tblNameList.AddressLine1, tblNameList.AddressLine2, tblNameList.City,
tblNameList.State, tblNameList.Zip, tblNameList.Province,
tblNameList.Country, tblNameList.ClassYear, tblNameList.Comments,
FIRST(tblPhones.PhoneNumber) as Phone ,
FIRST (tblPhoneTypes.PhoneType) as Type,
tblNameList.Association,
tblNameList.StatusID, tblNameList.Suffix
FROM tblPhoneTypes INNER JOIN ((tblStatus INNER JOIN tblNameList ON
tblStatus.ID = tblNameList.StatusID) INNER JOIN tblPhones
ON tblNameList.ID = tblPhones.NameListID)
ON tblPhoneTypes.ID = tblPhones.PhoneTypeID
WHERE tblPhoneTypes.PhoneType In ("Home","Work","Other")
AND tblNameList.Association=-1
AND tblNameList.StatusID=1
GROUP BY  tblNameList.ID, tblNameList.Last, tblNameList.First,
tblNameList.AddressLine1, tblNameList.AddressLine2, tblNameList.City,
tblNameList.State, tblNameList.Zip, tblNameList.Province,
tblNameList.Country, tblNameList.ClassYear, tblNameList.Comments,
tblNameList.Association,
tblNameList.StatusID, tblNameList.Suffix

If your Comments field is a memo, the memo field will get truncated to 255
characters.  If you need the full memo field, use First around it in the
SELECT clause and remove it from the GROUP BY clause.

> That didn't work either. I still get multiples from the same person.
>
[quoted text clipped - 66 lines]
>> >> > sense
>> >> > here?
Secret Squirrel - 03 Nov 2005 17:55 GMT
I want it to return just one of the phone numbers. It doesn't matter which
one as long as it returns something for each person.

> Since you are returning values in the select clause from tblPhoneTypes you
> are going to get one record for each phone number and for each phone type.
[quoted text clipped - 99 lines]
> >> >> > sense
> >> >> > here?
John Spencer - 03 Nov 2005 20:13 GMT
So, did the revised query work or did it fail or did it give you the wrong
results.

>I want it to return just one of the phone numbers. It doesn't matter which
> one as long as it returns something for each person.
[quoted text clipped - 114 lines]
>> >> >> > sense
>> >> >> > here?
Secret Squirrel - 03 Nov 2005 20:49 GMT
Yes it worked perfectly. Thank you very much! I appreciate it!

> So, did the revised query work or did it fail or did it give you the wrong
> results.
[quoted text clipped - 117 lines]
> >> >> >> > sense
> >> >> >> > here?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.