criteria question

  • Thread starter Thread starter DEI
  • Start date Start date
D

DEI

I have several phone numbers I would like to use as criteria in a phone
number field. I have these numbers in another table. Is there an easy way
of using the phone numbers in that table rather than entering each number?
I'm talking like 300 numbers.
 
You could make a column in your table and copy them in or use an append query
to put them in.
 
I am not sure about the exact meaning of the question but *if* it is about a
criteria like:


SELECT *
FROM tableName
WHERE phoneNumber IN( telPhone1, telPhone2, ..., telPhoneN)


but with the list of phone numbers already in another table, then:

SELECT *
FROM tableName
WHERE phoneNumber IN( SELECT phoneNumber FROM otherTable)


or, if there is no duplicated phone numbers in the second table:


SELECT *
FROM tableName INNER JOIN otherTable
ON tableName.phoneNumber = otherTable.phoneNumber



Hoping it may help,
Vanderghast, Access MVP
 
This is how I use a table. Create a query and place both tables in the table
space of the design view grid. Use the phone list table as criteria.
SQL would look like this ---
SELECT PeteyP.CCRScopeInitSigDate, PeteyP.Phone
FROM PeteyP, Phone
WHERE (((PeteyP.Phone)=[Phone].[Phone]));
If your list changes but you want to keep the numbers you can add a Yes/No
field named Active in the phone list table and then use added criteria in the
query ---
SELECT PeteyP.CCRScopeInitSigDate, PeteyP.Phone
FROM PeteyP, Phone
WHERE (((PeteyP.Phone)=[Phone].[Phone]) AND ((Phone.Active)=-1));
 
I keep getting a syntax error.

SELECT *
FROM marchbill
where phonenumber in (select potnumber in phonenumbers)
 
You have "in" where you should have "FROM" in the subquery.

SELECT *
FROM marchbill
where phonenumber in (select potnumber FROM phonenumbers)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
rats. That still doesn't give me what I'm looking for. Let me see if I can
clarify the fields.

Table 1 has a field (phoneNumber) that I would like to filter using a field
(potsNumber) in table 2.

Can you help me?
 
Well is the field in the table PhoneNumbers named potsNumber or
potNumber. You used the latter in the subquery.

And can you be a bit more specific than "still doesn't give me what I'm
looking for." That does not tell anyone what happened.

Did you get a syntax error?
Did you get records that you should not have?
Did you get one record when you "KNOW" you should have gotten 423 records?



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top