Query Problem

  • Thread starter Secret Squirrel
  • Start date
S

Secret Squirrel

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?
 
K

Ken Snell [MVP]

SELECT DISTINCT PersonID, PersonName
FROM TableName
WHERE PhoneType = "Home" OR
PhoneType = "Work" OR
PhoneType = "Other";
 
S

Secret Squirrel

Will that prompt me to choose one or will it run automatically? I don't want
any prompts to select the criteria.
 
S

Secret Squirrel

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.?
 
S

Secret Squirrel

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));
 
K

Ken Snell [MVP]

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));
 
S

Secret Squirrel

Thanks for your help but now I'm getting a "data type mismatch in criteria
expression" error.
 
J

John Spencer

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
 
J

John Spencer

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.
 
S

Secret Squirrel

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.
 
J

John Spencer

So, did the revised query work or did it fail or did it give you the wrong
results.
 
Top