Compare records in two tables 1

[impulse24]

I have two tables Import, and Books. There are four fields in each table. I want to do a cross query. But the only thing that makes that data unique is that a unique record is combined of all four fields. How do i create the relationship between the two tables?


Example:

Import Table
Book Name
Author
Price
Date

Books Table
Book Name
Author
Price
Date

The combination of all four fields makes the record unique. I want to see which books records are in the import table that are not in the Books table. I have been searching for days. Please help

 

[MichaelRed]

Use the 'unmatched records' wizzard.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[impulse24]

The unmatched records wizard only works if you are using one field in one table not matching one field in the other table. There is not a unique field. All the fields together make a unique record. Thanks tho

 

[mymou]

Hi impulse24

Not sure about 'unmatched records' - let you check it out.

But, why not have multiple (*=) joins?

Stew "Make good use of bad rubbish."

 

[MichaelRed]

Really need to get BASIC?

Create Query1 from [ImportTable]
Query1.[Unique]: [Book Name] & [Author] & [Price] & [Date]

Create Query1 from [Books]
Query2.[Unique]: [Book Name] & [Author] & [Price] & [Date]


Unmatched on query1 : Query2

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[nutshell]

two suggestions

Using a subselect

SELECT Import.*
FROM Import
WHERE (BookName & Author & Price & Date) not in (Select BookName & Author & Price & Date FROM Books);


Using Left Join (similar to unmatched query wizard)

SELECT DISTINCTROW Import.*
FROM Import LEFT JOIN Books ON Import.BookName & Import.Author & Import.Price & Import.Date = Books.BookName & Books.Author & Books.Price & Books.Date
WHERE ((Books.BookName& Books.Author & Books.Price & Books.Date) Is Null);