Query from two tables to compare

[de Vroede]

Hi,

I'm having difficulty building a query that compares two tables. I'm
using access 2000 on win2000.

I have a hardware db that contains a table with hardware, a table with
users and a table where hardware is linked to users (called tbl_assets)
..

I filled the hardware list from an excel file and I want to update the
asset table with data from the hardware list and link them to a default
user. but I don't want to have multiple values, so values allready in
the asset table should be skipped.

I've tried to use a criteria that should exclude values in the
tbl_assets IDhardware column, but the query doesn't like that.

regards,

jeroen

 

[Brendan Reynolds]

You can do it with a sub-query ...

INSERT INTO TargetTable (TargetID, TargetText) SELECT SourceID, SourceText
FROM SourceTable WHERE SourceID NOT IN (SELECT TargetID FROM TargetTable)

 

[de Vroede]

Thanks,

this works great!

regards,

jeroen

You can do it with a sub-query ...

INSERT INTO TargetTable (TargetID, TargetText) SELECT SourceID, SourceText
FROM SourceTable WHERE SourceID NOT IN (SELECT TargetID FROM TargetTable)