Help with query

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Table 1 has 10 more records than Table 2. I want to
create a query that only displays the 10 records in Table
1 that are not in Table 2.

I tried a couple of different things (e.g., joining
tables, not joing tables, an expression) but I am not
having much luck.

There is a unique field in Table 1 and Table 2 that should
match. I only want those records where the unique field
in Table 1 does not match any record in Table 2.

Any help would be greatly appreciated. Thanks
 
There's a Unmatched Query Wizard available when you go to create new queries
that will do exactly what you're trying to do.
 
Hi Am a new Access usere but know a little about data base manipulation

Question is how to display in a two-table query records that are not matched in one table or the other. It doesn't seem to happen by itself.

Thanks H
 
Right click on an area of the query grid or use the
properties button up top to view the query properties.
Change the "Output All Fields" to yes and see if you get
what you want.

You can also right click the join line between the tables in
the upper window of the query grid and set join properties.

Ernie Scofield

hsmcconx said:
Hi Am a new Access usere but know a little about data base manipulation

Question is how to display in a two-table query records
Click to expand...
that are not matched in one table or the other. It doesn't
seem to happen by itself.
 
Access doesn't support full-outer join so you will need to use Union Query
with 2 Left / Right joins. something like:

SELECT *
FROM Table1 LEFT JOIN Table2
ON Table1.MatchField = Table2.MatchField
UNION ALL
SELECT *
FROM Table1 RIGHT JOIN Table2
ON Table1.MatchField = Table2.MatchField

--
HTH
Van T. Dinh
MVP (Access)



hsmcconx said:
Hi Am a new Access usere but know a little about data base manipulation

Question is how to display in a two-table query records that are not
Click to expand...
matched in one table or the other. It doesn't seem to happen by itself.
 
Hi H,

I thought I understood your question until I tried writing the query. I
can't work out what you mean by "records that are not matched in one
table or the other".

If there are only two tables, records that are matched in neither of
them are by definition records that don't exist, so it's not possible to
return them.

If you mean records in table A that are not matched in table B plus
records in B that are not matched in A, try something like

SELECT * FROM A WHERE ID NOT IN (SELECT ID FROM B)
UNION
SELECT * FROM B WHERE ID NOT IN (SELECT ID FROM A);
 
Back
Top