Re: No duplicates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You need what is called an Outer Join (Left or Right). Join the two
tables based on the field "Colour" and you will get an "Inner Join".
Now double click on the join and you will be shown the Join Properties
screen. Choose "Include all records from the table with the most
colours and only those from the table with the least colours where they
are equal" (or something like that). Then you want to add the Colour
field from each of the two tables, but for criteria for the table with
the least colours you want to put "Is Null"

(Boy, it sounds convoluted to do it that way)

Or if you know how to input SQL you can use this:
SELECT Table1.colour
FROM Table1 LEFT JOIN Table2 ON Table1.colour = Table2.colour
WHERE Table2.colour Is Null;

Table1:
colour - Text - Primary Key

Table1 Values:
Red
Yellow
Blue
Green
Orange

Table2:
colour - Text - Primary Key

Table2 Values:
Red
Green
Orange

Query1:
SELECT Table1.colour
FROM Table1 LEFT JOIN Table2 ON Table1.colour = Table2.colour
WHERE Table2.colour Is Null;

Query1 Results:
Blue
Yellow

Cheers,
Jason Lepack

ladybug via AccessMonster.com wrote:
I know that I should know this, but I am having a complete dumb moment.

Here is a simple example.

I have two tables. 1st table has red, blue, green, yellow, orange
2nd table has red, green, orange

I want to do a query that returns blue and yellow

I want to know out of the first table what is not in the second table.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200701/1

.

Relevant Pages

  • Re: SQL Subquery on Max(date)
    ... The overall query I'm trying to insert this select into ... LEFT OUTER JOIN ... CONSTRAINT pk_oncd_company ... PRIMARY KEY, ...
    (microsoft.public.access.formscoding)
  • Re: help
    ... An outer join is a join that allows you to retrieve ALL the records from ... About the primary key, I don't have the ... another table where I would only have one thing per employee ID, ... >> MS Access MVP ...
    (microsoft.public.access.queries)
  • Re: DB Table Design Help
    ... Jason Lepack ... Primary Key that each record would be unique I can see where I was wrong on ... Products could be stored in any warehouse. ... Suppliers ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Queries/Reports not showing records
    ... I like your troubleshooting style. ... Make the query simpler and see what ... "Jason Lepack" wrote: ... Primary key is Project ID is the main project table, ...
    (microsoft.public.access.queries)
  • Re: dataset merging
    ... the primary key property does the trick. ... If you try to merge rows and the rows have the same primary key, but you're trying to merge columns of the same row, you'll have trouble. ... Basically I want to perform an outer join operation on a dataset and the resulting sql query based on the contents of said dataset. ...
    (microsoft.public.dotnet.framework.aspnet)