MS Access- update checkbox field based on other checkboxfields in same table..

[Trevor Fisher]

The functionality I'm trying to implement seems pretty straight forward but I can't figure it out.

I have a table with a list of requirements. Each requirement can belong to several organizations. The table will have a field for each organization and for each org. that a requirement belongs to the end user will click the check box in that org. field. For example, a hypothetical requirement #5579 which belongs to Org1 and Org3:

Table Fields: Req # Org1 Org2 Org3
Data: 5579 [X] [ ] [X]

( A check box is represented by the [ ] and [X])

So far so good. But on top of that I want to add logic to some of the check box fields. For example Org2 and Org3 may be sub-sets of Org1 so that if Org1 is checked Org2 & Org3 will get checked automatically.

Any ideas?

 

[Beetle]

Any ideas?

Yes. Stop what you're doing. You need to re-think your structure. See this
link
for reasons not to use a collection of check boxes like you are;

http://allenbrowne.com/casu-23.html

From your post it would appear that you have a many-to-many
relationship between Organizations and Requirements, so you will
need at least three tables to start. You should have something like;

tblOrganizations
************
OrganizationID (PK)
OrganizationName
<other fields related specifically to the Organization>

tblRequirements
************
ReqID (PK)
ReqDescription

tblOrgRequirements
**************
OrganizationID (FK to tblOrganizations)
ReqID (FK to tblRequirements)

tblOrgRequirements would be a junction table to handle the relationship
between Organizations and Requirements. You could then easily assign
as many, or as few, requirements as are necessary for any given
organization.

 

[Ken Sheridan]

Firstly, I'd strongly urge you to heed Sean's excellent advice. Your present
structure does what's known as encoding data as column headings, which goes
against one of the fundamental principles of the relational database model,
the 'information principle', viz:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

To add to what Sean has said, to cater for some organisations being
sub-types of others, the way this is modelled is by a one-to-one
relationship, so you'd need a tblSubOrganizations table of the same structure
as tblOrganizations, but the primary key column of tblSubOrganizations,
OrganizationID is also a foreign key referencing the primary key of
tblOrganizations. It cannot therefore be an autonumber column of course,
unlike the key of tblOrganizations. Consequently if an organisation is
mapped to a requirement via the tblOrgRequirements table which models the
many-to-many relationship, all organizations which are sub-types of that
organization are also mapped to the requirement via the one-to-one
relationship.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Firstly, I'd strongly urge you to heed Sean's excellent advice. Your present
structure does what's known as encoding data as column headings, which goes
against one of the fundamental principles of the relational database model,
the 'information principle', viz:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

To add to what Sean has said, to cater for some organisations being
sub-types of others, the way this is modelled is by a one-to-one
relationship, so you'd need a tblSubOrganizations table of the same structure
as tblOrganizations, but the primary key column of tblSubOrganizations,
OrganizationID is also a foreign key referencing the primary key of
tblOrganizations. It cannot therefore be an autonumber column of course,
unlike the key of tblOrganizations. Consequently if an organisation is
mapped to a requirement via the tblOrgRequirements table which models the
many-to-many relationship, all organizations which are sub-types of that
organization are also mapped to the requirement via the one-to-one
relationship.

Ken Sheridan
Stafford, England

The functionality I'm trying to implement seems pretty straight forward but I can't figure it out.

I have a table with a list of requirements. Each requirement can belong to several organizations. The table will have a field for each organization and for each org. that a requirement belongs to the end user will click the check box in that org. field. For example, a hypothetical requirement #5579 which belongs to Org1 and Org3:

Table Fields: Req # Org1 Org2 Org3
Data: 5579 [X] [ ] [X]

( A check box is represented by the [ ] and [X])

So far so good. But on top of that I want to add logic to some of the check box fields. For example Org2 and Org3 may be sub-sets of Org1 so that if Org1 is checked Org2 & Org3 will get checked automatically.

Any ideas?

 

[Ken Sheridan]

Doh! I hit the send button before pasting in my text.

I realize that I misread your original post. Sub-organizations is not in
fact a sub-type of organisations ( a sub-type is characterized by sharing all
the attribute types of its super-type but not those of other sub-types, e.g.
salespeople is a sub-type of employees). So the Organization column of
tblSubOrganizations is not a primary key, but merely a foreign key
referencing the primary key of tblOrganizations.

You might not actually need the tblSubOrganizations table at all. It depends
on whether it represents a distinct entity type, i.e. it has a different set
of attribute types, and hence columns, from tblOrganizations. If they have
the same attribute types then tblOrganizations can reference itself by
including a SubOrgOf column which references the Organization primary key
column of the same table, e.g. if organization Foo had an OrganizationID of
42, and organization Bar was one of its sub-organisations then organization
Bar would have a value of 42 in its SubOrgOf column.

The above assumes that each sub-organization can be a sub-organization of
only one organization of course. If not then you'd need an 'adjacency list'
table with columns OrganizationID and SubOrganizationID, both referencing the
primary key of Organizations, to model the relationship.

Ken Sheridan
Stafford, England

Firstly, I'd strongly urge you to heed Sean's excellent advice. Your present
structure does what's known as encoding data as column headings, which goes
against one of the fundamental principles of the relational database model,
the 'information principle', viz:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

To add to what Sean has said, to cater for some organisations being
sub-types of others, the way this is modelled is by a one-to-one
relationship, so you'd need a tblSubOrganizations table of the same structure
as tblOrganizations, but the primary key column of tblSubOrganizations,
OrganizationID is also a foreign key referencing the primary key of
tblOrganizations. It cannot therefore be an autonumber column of course,
unlike the key of tblOrganizations. Consequently if an organisation is
mapped to a requirement via the tblOrgRequirements table which models the
many-to-many relationship, all organizations which are sub-types of that
organization are also mapped to the requirement via the one-to-one
relationship.

Ken Sheridan
Stafford, England

The functionality I'm trying to implement seems pretty straight forward but I can't figure it out.

I have a table with a list of requirements. Each requirement can belong to several organizations. The table will have a field for each organization and for each org. that a requirement belongs to the end user will click the check box in that org. field. For example, a hypothetical requirement #5579 which belongs to Org1 and Org3:

Table Fields: Req # Org1 Org2 Org3
Data: 5579 [X] [ ] [X]

( A check box is represented by the [ ] and [X])

So far so good. But on top of that I want to add logic to some of the check box fields. For example Org2 and Org3 may be sub-sets of Org1 so that if Org1 is checked Org2 & Org3 will get checked automatically.

Any ideas?