updating a field in the "one" table

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I'd like to create an update query consisting of two tables that have a
one-to-many relationship with each other. Both tables contain a checkbox
field with a Yes/No data type. I'd like my upate query to set the checkbox
field to "Yes" for a record in the "one" table if and only if the checkbox
field in the "many" table is set to "Yes" for all records corresponding to
that record in the "one" table.

More specifically, assume the two fields are named tblAgent and
tblAgentContact. One Agent can have many Contacts:

tblAgent ---> tblAgentContact

The table tblAgent has a field named fp_agent
The table tblAgentContact has a field named fp_contact

Both of these fields have Yes/No data types. I'd like the update query to
set the value of the fp_agent field to "Yes" if and only if the value of the
fp_contact field in tblAgentContact is set to yes for all records
corresponding to that particular record in tblAgent.

Can anyone tell me what criteria I can use in the update query to accomplish
this?

Thanks in advance,

Paul

that sets a Yes/No field to "Yes" in a table on the "one" side of a
one-to-many relationship, when
 
Paul James said:
I'd like to create an update query consisting of two tables that have a
one-to-many relationship with each other. Both tables contain a checkbox
field with a Yes/No data type. I'd like my upate query to set the checkbox
field to "Yes" for a record in the "one" table if and only if the checkbox
field in the "many" table is set to "Yes" for all records corresponding to
that record in the "one" table.

More specifically, assume the two fields are named tblAgent and
tblAgentContact. One Agent can have many Contacts:

tblAgent ---> tblAgentContact

The table tblAgent has a field named fp_agent
The table tblAgentContact has a field named fp_contact

Both of these fields have Yes/No data types. I'd like the update query to
set the value of the fp_agent field to "Yes" if and only if the value of the
fp_contact field in tblAgentContact is set to yes for all records
corresponding to that particular record in tblAgent.

Can anyone tell me what criteria I can use in the update query to accomplish
this?

Thanks in advance,

Paul

that sets a Yes/No field to "Yes" in a table on the "one" side of a
one-to-many relationship, when
Click to expand...


CREATE TABLE tblAgent
(fp_agentID INTEGER
,fp_agent BIT
,CONSTRAINT pk_tblAgent PRIMARY KEY (fp_agentID)
)

Sample Data
1, No
2, No
3, No
4, Yes
5, No

CREATE TABLE tblAgentContact
(fp_tblagentcontactID INTEGER
,fp_agentID INTEGER
,fp_contact BIT
,CONSTRAINT pk_tblAgentContact PRIMARY KEY (fp_tblagentcontactID)
,CONSTRAINT fk_tblAgentContact_tblAgent FOREIGN KEY (fp_agentID)
REFERENCES tblAgent (fp_agentID)
)

Sample Data

1, 1, YES
2, 1, YES
3, 1, YES
4, 2, YES
5, 2, NO
6, 2, YES
7, 3, YES
8, 3, YES
9, 3, YES
10, 4, YES
11, 4, YES
12, 4, YES
13, 5, NO
14, 5, NO
15, 5, NO

Expected Results
fp_Agent will be changed to YES for 1 and 3, 2, 4, and 5 should remain
unchanged.


UPDATE tblAgent AS A1
SET fp_agent = -1
WHERE -1 = ALL
(SELECT AC1.fp_contact
FROM tblAgentContact AS AC1
WHERE AC1.fp_agentID = A1.fp_agentID)


Sample Data
1, YES
2, No
3, YES
4, Yes
5, No


Seems to work. Try it out.


Sincerely,

Chris O.
 
It works exactly as you wrote it. This will be a huge time saver for us.

It's amazing how much power you can have over a db when you can write SQL
code like you just did.

Thanks so much, Chris
 
Back
Top