query two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am very new to Access and am having some trouble.

I have two tables in my DB, one called Data that contains approx 14,000
names and another table called Returned that has approx 1000 names. I want to
create a query to remove the Returned names from the list of names in the
Data table so that the resulting query only gives me the remaining 13,000
names that were NOT in the returned table.

I tried creating a query with a Criteria in the name filed of "[data]![Name]
Not Like [Returned]![Name]". This results in a total of 1000 names in the
query instead of the 13,000 I would expect to get. I guess it is being
limited to the number of names in the Returned table.

How can I achieve the results I am looking for, which is a list of names
from the data table that do not include the names in the Returned table?

Your help is greatly appreciated.

Thanks
Robert
 
You can use the query wizard to create an Unmatch records query, that list
all the records that are in one table but not in the other.
 
I'm sorry, but I am very new to access. How do I create an Unmatch records
query?

thanks for your assistance.

Ofer Cohen said:
You can use the query wizard to create an Unmatch records query, that list
all the records that are in one table but not in the other.


--
Good Luck
BS"D


rt10516 said:
Hi, I am very new to Access and am having some trouble.

I have two tables in my DB, one called Data that contains approx 14,000
names and another table called Returned that has approx 1000 names. I want to
create a query to remove the Returned names from the list of names in the
Data table so that the resulting query only gives me the remaining 13,000
names that were NOT in the returned table.

I tried creating a query with a Criteria in the name filed of "[data]![Name]
Not Like [Returned]![Name]". This results in a total of 1000 names in the
query instead of the 13,000 I would expect to get. I guess it is being
limited to the number of names in the Returned table.

How can I achieve the results I am looking for, which is a list of names
from the data table that do not include the names in the Returned table?

Your help is greatly appreciated.

Thanks
Robert
Click to expand...
Click to expand...
 
Either use the query wizard when you create a new query, or use this SQL

SELECT BigTable.*
FROM BigTable LEFT JOIN SmallTable ON BigTable.[Name]= SmallTable.[Name]
WHERE SmallTable.[Name] Is Null

************
Note: you need to change the names of:
BigTable = the name of the table with 14,000 records
SmallTable = the name of the table with the 1000 record
[Name]= the name of the field that link the two tables

You copy and paste this SQL to a new query,
If you need more help, please post the name of the tables and the field that
link them together


--
Good Luck
BS"D


rt10516 said:
I'm sorry, but I am very new to access. How do I create an Unmatch records
query?

thanks for your assistance.

Ofer Cohen said:
You can use the query wizard to create an Unmatch records query, that list
all the records that are in one table but not in the other.


--
Good Luck
BS"D


rt10516 said:
Hi, I am very new to Access and am having some trouble.

I have two tables in my DB, one called Data that contains approx 14,000
names and another table called Returned that has approx 1000 names. I want to
create a query to remove the Returned names from the list of names in the
Data table so that the resulting query only gives me the remaining 13,000
names that were NOT in the returned table.

I tried creating a query with a Criteria in the name filed of "[data]![Name]
Not Like [Returned]![Name]". This results in a total of 1000 names in the
query instead of the 13,000 I would expect to get. I guess it is being
limited to the number of names in the Returned table.

How can I achieve the results I am looking for, which is a list of names
from the data table that do not include the names in the Returned table?

Your help is greatly appreciated.

Thanks
Robert
Click to expand...
Click to expand...
Click to expand...
 
Thank you. this worked

Ofer Cohen said:
Either use the query wizard when you create a new query, or use this SQL

SELECT BigTable.*
FROM BigTable LEFT JOIN SmallTable ON BigTable.[Name]= SmallTable.[Name]
WHERE SmallTable.[Name] Is Null

************
Note: you need to change the names of:
BigTable = the name of the table with 14,000 records
SmallTable = the name of the table with the 1000 record
[Name]= the name of the field that link the two tables

You copy and paste this SQL to a new query,
If you need more help, please post the name of the tables and the field that
link them together


--
Good Luck
BS"D


rt10516 said:
I'm sorry, but I am very new to access. How do I create an Unmatch records
query?

thanks for your assistance.

Ofer Cohen said:
You can use the query wizard to create an Unmatch records query, that list
all the records that are in one table but not in the other.


--
Good Luck
BS"D


:

Hi, I am very new to Access and am having some trouble.

I have two tables in my DB, one called Data that contains approx 14,000
names and another table called Returned that has approx 1000 names. I want to
create a query to remove the Returned names from the list of names in the
Data table so that the resulting query only gives me the remaining 13,000
names that were NOT in the returned table.

I tried creating a query with a Criteria in the name filed of "[data]![Name]
Not Like [Returned]![Name]". This results in a total of 1000 names in the
query instead of the 13,000 I would expect to get. I guess it is being
limited to the number of names in the Returned table.

How can I achieve the results I am looking for, which is a list of names
from the data table that do not include the names in the Returned table?

Your help is greatly appreciated.

Thanks
Robert
Click to expand...
Click to expand...
Click to expand...
 
Back
Top