Extract trades that do NOT match the other table

[ali]

I have 2 tables.

Table1: All new trades, (ID, Name, Amount, Remarks)

Table2: All trades from before till now (ID, Name, Amount, Remark).

---------------------------------------------------------------------
I'd like to :

Link these two tables to extract the new trades, meaning trades in table1
which got no record in table2.

--------------------------------------------------------------------
Note:
I have another query to update into table2 everyday.


Thanks a lot, dear experts !

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You really shouldn't keep the data in 2 tables, based on the date,
that's called "attribute splitting," and is a violation of the rules of
DB design. You should add a date column to one table (probably table2)
and keep all the records in that table. Use the date value to determine
if a record is "new" or not.

But, if you insist, here is the solution:

SELECT T1.*
FROM table1 As T1 LEFT JOIN table2 As T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL

This query assumes that there can only be one unique ID in both tables,
combined.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR7rTF4echKqOuFEgEQIScQCgrey9Y6tb1MEPXmdYrr/lAC7kJg0AoPGR
kaORXZu6kDhtqkrlmUmY2Q8J
=ICUn
-----END PGP SIGNATURE-----

 

[John Spencer]

Is ID field unique?
HOW do you determine which records in Table1 MATCH a record in Table2? Same
ID or a same ID and Name or a same ID, Name, and Amount?

Assuming that ID and Name both must match then try the following query.

SELECT Table1.*
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
AND Table1.[Name] = Table2.[Name]
WHERE Table2.ID is Null

If only one field determines the match then use the Unmatched query wizard
to build your query.
-- Click New Query
-- Select find Unmatched Query Wizard
-- Follow the prompts.

You can even use the Unmatched query wizard to build the base of the query
on just one field and then modify the resulting query to include the
additional fields.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..