Query

[Beto1967 via AccessMonster.com]

I've a query that has 3000 records I've the phone number to use to join the
query to a table that has 50000 records and the same phone number. In the
query the phone number will appear more then once and in the table the phone
number will appear more then once. In the table I want to get the survey ID
that is primary for every phone number that is in the query right now this
is what I get (I will use 3 digits for phone number)

Phone number survey id
123 6789
123 6789
345 7889
345 7889
What I would like to see is everything that is from the query and the
different survey id from the table as part of the query.
phone number survey id
123 6789
123 6790
345 7889
345 7890
I've tried inner outer in the query but it always gives me the same survey id
for the phone number. I'm at a lost .. Thanks for assistance

 

[Guest]

Hi

Try these options. I have listed several ways so you can pick which runs
the quickest for you.
(Obviously replace table and query with the actual table and query names)

1)

select distinct [phone number], [survey id]
from table

This is ok if you want all combinations from table. If you only want those
where the phone number appears in the query see rest of examples.

2)

select distinct [phone number], [survey id]
from table
where [phone number] in
(select distinct [phone number]
from query)

Note: Try this with and without the distinct on the 4th line to test speed.
You always need the distinct on the 1st line.

3)

select distinct table.[phone number], table.[survey id]
from table inner join query
on table.[phone number] = query.[phone number]


hth

Andy Hull