criteria question

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

Golfinray

You could make a column in your table and copy them in or use an append query
to put them in.
 
M

Michel Walsh

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
 
K

KARL DEWEY

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

DEI

I keep getting a syntax error.

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

John Spencer

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
'====================================================
 
D

DEI

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

John Spencer

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
'====================================================
 
Top