Not equal to selections and update

[trojnfn]

I am linking two access tables by key payroll_number. If the payroll number is
in table 1 but not in table 2, I want to take all records from table 1 and
append them to table 2. I would use my cf query and the sql insert command but
what is the selection command/proper syntax to do this ?

[paross1]

Assuming that table 1 and table 2 have the same structure (same columns, data
types, etc.)

INSERT
INTO table2
SELECT *
FROM table1 t1
WHERE NOT EXISTS (SELECT 1
FROM table2 t2
WHERE t1.payroll_number = t2.payroll_number)

Phil

[trojnfn]

Hi Phil,

Thanks for the response.

However, I am having a hard time making your code work, particulary the syntax
from table1 t1 means what ? And the command where not exists (select 1.. is
that a typoor select 1 is part of the command ?

[paross1]

The t1, t2, etc. are "alias" names assigned to the tables in order to reference
the columns by table name, especially in the sub select. (I guess that you can
add the AS, such as in FROM table1 AS t1, etc. )

The SELECT 1 is not a typo -- EXISTS (or NOT EXISTS) works like a boolean
test, so it really doesn't matter what you select in your subquery, it only
returns rows in the outer select where the inner select is true.

If you are still having trouble, what are your table and column names?

Phil

[trojnfn]

My two tables are identical in layout. The first table name is A60_new and the
second table is A60_baseline. I want to append everyting from A60_new into
A60_Baseline only if the key value baseline_num from A60_new is not already in
A60_Baseline.
If it is already there, I do not want to append. I hope this makes sense. What
is the propery syntax to do this ?
Thanks for your help.

[paross1]

Did you try something like this?

INSERT
INTO A60_Baseline
SELECT *
FROM A60_new AS t1
WHERE NOT EXISTS (SELECT 1
FROM A60_Baseline AS t2
WHERE t1.baseline_num = t2.baseline_num)

Phil

[trojnfn]

Hi Phil,

I was just about to try your method when the requirements changed slightly and
I was wondering if your method still applies. I now have a table called
Custodians and one called CompanyOwned. Custodians contains columns that are in
CompanyOwned. I want to add (append) the like columns from CompanyOwned into
the Custodians table but only if it does not already exists. In this case,
payroll number is my key linkage in both tables. Another problem is that the
payroll number has a contract code associated with it in the CompanyOwned
table. The same payroll number and the same contract code can appear multilpe
times in the CompanyOwned table but I only want to append one of the records.
If the same payroll number has a different contract code, then I want to append
that record also, even though the paroll number is the same, it has a different
contract code. For example, a payroll number-contract code combination might be
something like this :

pay100 cd250
pay100 cd250
pay100 cd250
pay100 cd530
pay100 cd530
pay100 cd800

In this example, i would want to append payroll pay100 and contract cd250
once, even though it appears 3 times, pay100 and contract 530 once, even though
it appears 2 times, and payroll pay100 and contract cd800 ,to the Custodians
table.

Can this still be done with your method ? If not, can your method be modified
to accomodate these new requirements ?

Thanks for your help.

[trojnfn]

correction, my key linkages are the payroll number and contract code combinations

[paross1]

So, what you are saying is that you only want to insert a new row into
Custodians from data in CompanyOwned if there are no rows in Custodians that
have the same payroll number, and you only want to insert ONE row from
CompanyOwned even if there are multiple rows, right? Does it matter which one
is used from CompanyOwned? In other words, since you are selecting a possible
one of many, which one do you want to use?

The query below is modified to add a correlated subquery to restrict the
selection in your outer select to one of each PayrollNumber, and it selects the
row with the largest ContractCode, but you could very well select the smallest
(MIN).

INSERT
INTO Custodians
SELECT *
FROM CompanyOwned AS t1
WHERE NOT EXISTS (SELECT 1
FROM Custodians AS t2
WHERE t1.PayrollNumber = t2.PayrollNumber)
AND t1.ContractCode = (SELECT MAX(t2.ContractCode)
FROM CompanyOwned AS t2
WHERE t1.PayrollNumber = t2.PayrollNumber)

Does this look kind of like what you are trying to do?

Phil