filter by query

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

Guest

In MS Access, I created a query to link between two primary key field in
order to show those that have the same number, and then I wanted to use that
query as filter in order to filter in each table to come out with those that
don't show in BOTH table, however MS Access didn't allow me to do so,
because query that used as filter need to meet certain specification in order
to be used as filter. Therefore, I want to ask if anyone could help me with
this.... Thanks a lot!
 
Can you post the SQL statement of your query?

It may be that you could use two queries. The first, yours, finds all
matching records. A second query, based on the first query and one of your
tables, could be used to find records in the table that are NOT in the query
results.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I'm sorry, I don't what is my SQL...
But how could you get the records that are NOT in the first query if you
link one of the table and the first query? I really don't get this, because
when you link a table and another OR a table with a query , the records in
the RESULT query will be those that are appear in BOTH table or query, which
is NOT the records that are NOT appear in the first query.....
However, if I misunderstand this, please explain to me how it works......
Thanks Jeff
 
Open your query in design mode. Change the view to SQL. Copy the text and
paste it into your newsgroup response.

Access writes SQL statements to carry out the queries you design.

If you want to see an example of the query that finds items in one table not
in another, use the query wizard, select the "...not in ..." query example,
and the wizard will walk you through creating the query (you'll need to use
a pair of tables that have corresponding items, and not all of them in
one -- you could create a pair of small "dummy" tables to try this).

When the query is created by the wizard, you can open it in design mode to
see how it was done.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Here is my Access SQL, could you tell me if there is anything wrong with my
database?

SELECT Co Receipt.*, Cus Receipt.*
FROM [Co Receipt] INNER JOIN [Cus Receipt] ON ([Co Receipt].[Re_Num]=[Cus
Receipt].[Re_Num]) AND ([Co Receipt].[Re_Val]=[Cus Receipt].[Re_Val]);

Regard,
Neon520
 
I can't see your database. I have no way to know if there's anything wrong
with it.

Please re-read my previous response. You can use an Access wizard to help
you find rows in one table not in another.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Neon520 said:
Here is my Access SQL, could you tell me if there is anything wrong with
my
database?

SELECT Co Receipt.*, Cus Receipt.*
FROM [Co Receipt] INNER JOIN [Cus Receipt] ON ([Co Receipt].[Re_Num]=[Cus
Receipt].[Re_Num]) AND ([Co Receipt].[Re_Val]=[Cus Receipt].[Re_Val]);

Regard,
Neon520

Jeff Boyce said:
Open your query in design mode. Change the view to SQL. Copy the text
and
paste it into your newsgroup response.

Access writes SQL statements to carry out the queries you design.

If you want to see an example of the query that finds items in one table
not
in another, use the query wizard, select the "...not in ..." query
example,
and the wizard will walk you through creating the query (you'll need to
use
a pair of tables that have corresponding items, and not all of them in
one -- you could create a pair of small "dummy" tables to try this).

When the query is created by the wizard, you can open it in design mode
to
see how it was done.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Back
Top