Simple question about query result

[Angie M.]

Hi,

I have a Main table that has 10,000 records. Fields include Main ID, Name,
Address, etc. I have another table that contains the same Main ID and phone
numbers, this one contains about 3000 records. Some of the people from the
main table don't have phone number records in the 2nd table, which is fine.

I want to combine the two tables so each record has the name, address AND
phone number. When I use both tables and run a query to combine them, only
the records with phone numbers get picked up in the query result. I want all
10,000 records to appear in the result, not just the ones with phone numbers.
The phone field can be blank for those without a phone number.

How can I do this? I've tried everything I know to do...Thanks for any help!

 

[John]

Hi,

I have a Main table that has 10,000 records. Fields include Main ID, Name,
Address, etc. I have another table that contains the same Main ID and phone
numbers, this one contains about 3000 records. Some of the people from the
main table don't have phone number records in the 2nd table, which is fine.

I want to combine the two tables so each record has the name, address AND
phone number. When I use both tables and run a query to combine them, only
the records with phone numbers get picked up in the query result. I want all
10,000 records to appear in the result, not just the ones with phone numbers.
The phone field can be blank for those without a phone number.

How can I do this? I've tried everything I know to do...Thanks for any help!

Do a left join with the two tables. In query designer, bring both tables
into the query. Click on the MainID field in the table with 10000
records and drag it on top of the MainID field in the table with 3000
records. Then, double click the join line that appears, and choose a
type 2 join. Add the required fields to your query. This should show all
records from the first table, and only the records from the second table
that have a matching record in the first table.

Just out of curiosity, would it be better to have the phone number
stored in the first table? The way it's designed, it allows each name to
have multiple phone numbers - if that's a requirement of the
application, then they should definitely be split out like that. If you
only need one number per name, then it would make more sense to have it
all in one table.

 

[Rick Brandt]

Hi,

I have a Main table that has 10,000 records. Fields include Main ID,
Name, Address, etc. I have another table that contains the same Main
ID and phone numbers, this one contains about 3000 records. Some of
the people from the main table don't have phone number records in the
2nd table, which is fine.

I want to combine the two tables so each record has the name, address
AND phone number. When I use both tables and run a query to combine
them, only the records with phone numbers get picked up in the query
result. I want all 10,000 records to appear in the result, not just
the ones with phone numbers. The phone field can be blank for those
without a phone number.

How can I do this? I've tried everything I know to do...Thanks for
any help!

In your query double-click the line joining the two tables. The resulting
dialog is self-explanatory.

 

[Angie M.]

Yes, thanks John. This was set up as a Main table and phone table because
there were primary, fax, cell phones, etc., we are in the process of
exporting to Outlook contacts so that's why what I was doing sounded weird.
Thanks for your help!