Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
SELECT DISTINCT PersonID, PersonName
FROM TableName
WHERE PhoneType = "Home" OR
PhoneType = "Work" OR
PhoneType = "Other";
 
Will that prompt me to choose one or will it run automatically? I don't want
any prompts to select the criteria.
 
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.?
 
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 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));
 
Thanks for your help but now I'm getting a "data type mismatch in criteria
expression" error.
 
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
 
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.
 
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.
 
So, did the revised query work or did it fail or did it give you the wrong
results.
 
Back
Top