A2k - updating Many side dependant on change to record on the One side

[Deano]

Hi,

I have a quick problem that I think I know how to solve using DAO but just
realised an update query would be more elegant. However I don't have a clue
how to implement it.

My main table contains employee records.
Each employee *may* have more than one contract i.e if they do more than one
job. The contract records are in tblContracts.

There is a checkbox on the form (which is bound to tblContracts) to indicate
whether an employee is in the pension scheme.

Now if I have an employee who has 2 or more contracts, I would like the
yes/no value for each of these records to be changed to Yes.
At the moment checking the box only changes the current record.

I'm trying to google the answer but this time I'm not sure what to look
for...

thanks again for any help,
Martin

[Deano]

Bleeding hell, i just realised the subject header is all wrong!

I'm not doing anything to the main table, just the tblContracts table.

[Pieter Linden]

I could only do it using 2 queries... (Hey, I'm not Joe Celko!)

Find all the Employees with more than one Contract:

"qryMultipleContracts"
SELECT tblEmployee.EmployeeID, Count(tblContract.ContractID) AS
CountOfContractID
FROM tblEmployee INNER JOIN tblContract ON tblEmployee.EmployeeID =
tblContract.EmployeeID
GROUP BY tblEmployee.EmployeeID
HAVING (((Count(tblContract.ContractID))>1));

Mark all these folks...
UPDATE tblEmployee SET tblEmployee.InPensionPlan = True
WHERE (((tblEmployee.EmployeeID) In (SELECT EmployeeID FROM
qryMultipleContracts)));

I tried an inner join on the second one... no dice. I guess that's
why I'm a poor student instead of a rich developer... oh well!

[Deano]

Pieter Linden wrote:

I could only do it using 2 queries... (Hey, I'm not Joe Celko!)

Find all the Employees with more than one Contract:

"qryMultipleContracts"
SELECT tblEmployee.EmployeeID, Count(tblContract.ContractID) AS
CountOfContractID
FROM tblEmployee INNER JOIN tblContract ON tblEmployee.EmployeeID =
tblContract.EmployeeID
GROUP BY tblEmployee.EmployeeID
HAVING (((Count(tblContract.ContractID))>1));

Mark all these folks...
UPDATE tblEmployee SET tblEmployee.InPensionPlan = True
WHERE (((tblEmployee.EmployeeID) In (SELECT EmployeeID FROM
qryMultipleContracts)));

I tried an inner join on the second one... no dice. I guess that's
why I'm a poor student instead of a rich developer... oh well!

Thanks Pieter, I'll be having a go at that later and will report back.

[--CELKO--]

>> My main table contains employee records [sic]. Each employee *may*
have more than one contract i.e if they do more than one job. The
contract records are in tblContracts. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications.

Rows are not records and the differences are important. Next, stop
using those silly, dangerously redudant "tbl-" prefixes; this is
supposed to be SQL and we are supposed to follow ISO-11179 rules for
data element names.

There is a checkbox on the form (which is bound to tblContracts) to indicate
whether an employee is in the pension scheme. <<

So a pension is an attribute of a contract, not an employee ...
Now if I have an employee who has 2 or more contracts, I would like

the
yes/no value for each of these records [sic]to be changed to Yes. <<

CREATE TABLE Personnel
(ssn CHAR(9) NOT NULL PRIMARY KEY,
...);

CREATE TABLE Contracts
(contract_id INTEGER NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL
REFERENCES Personnel(ssn)
ON DELETE CASCADE
ON UPDATE CASCADE,
contract_type CHAR(1) DEFAULT 'N' NOT NULL
CHECK(contract_type IN ('P', 'N', 'T')) -- p = pension
...);

UPDATE Contracts
SET contract_type = 'P'
WHERE ssn
IN (SELECT ssn
FROM Contracts
GROUP BY ssn
HAVING COUNT(contract_id) > 1);

Bu this is a bad data model; pensions should be in the employee data,
not in contracts.

[Deano]

--CELKO-- wrote:

My main table contains employee records [sic]. Each employee *may*

have more than one contract i.e if they do more than one job. The
contract records are in tblContracts. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good ideas, along with clear
specifications.

Blimey, where did you come from. Thanks for the crash course in how to post
to cdm. I've managed ok up to now. I post as much info as is needed but I
think it's important not to overdo it. My SQL is so bad anyway that I'm not
going to convey much in that respect anyhow.
I'm the sort of person who appreciates ideas and concepts that will set me
on my way, I don't often need a specific solution (though it is welcomed
:) )

Rows are not records and the differences are important. Next, stop
using those silly, dangerously redudant "tbl-" prefixes; this is
supposed to be SQL and we are supposed to follow ISO-11179 rules for
data element names.
Get a life mate, i'll keep using my precious tbl prefixes - no one else
seems to object.

<SQL snipped>
Bu this is a bad data model; pensions should be in the employee data,
not in contracts.

I agree with you there but that's what i'm faced with.