MS Access Forum / Queries / November 2005
Query Problem
|
|
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?
|
|
|