Auto-populating one table field when select other from combo looku

[Guest]

I have a data table with the two relevant fields for my question being
ReportSection and ReportSectionSort, plus many other information fields. The
ReportSection is text; ReportSectionSort is a number. The ReportSection is a
lookup field combo box tied to a query of a separate table which has the
ReportSections and ReportSection Sorts (which can be easily changed there).
How can I populate the new ReportSectionSort field in the main data table
automatically upon selection of the ReportSection from the lookup combo box?
I want to be able to enforce referential integrity so when I have to change
Sections and Sorts in the secondary table, the changes will be reflected
automatically in the data table.

 

[Jeff Boyce]

Ray

Am I understanding you correctly? Are you saying that you have a table that
stores [ReportSection] and [ReportSectionSort], and then you have ANOTHER
table that you are adding the same information into?

If so, stop. If referential integrity and a well-normalized relational
database are your goals, you don't store the same data more than once.

Also, check the "tablesdbdesign" newsgroup on the use of lookup data
fields -- the very strong consensus there is DON'T!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff,

I began thinking about what you wrote, and it made sense. The idea is really
referrential integrity. I want to be able to change the ReportSectionSort
information - maintained in one table; but then when I go to the main table
and select the ReportSection, I want the new sort information to
automatically take effect in the table. For example, let's say I have an item
in the ReportSectionSort table that has a ReportSection equal to "Needs
Priority" and the corresponding ReportSectionSort field equals 1. Those
fields should be reflected in the main data table with other information
items. I want the flexibility to change the ReportSectionSort for the items
tagged as "Needs Priority" to 2, 3, or whatever. But, when I go to the main
data table, I want the change that I made to the ReportSectionSort table to
be automatically reflected in the main data information table. Am I clear
enough?

Ray

Am I understanding you correctly? Are you saying that you have a table that
stores [ReportSection] and [ReportSectionSort], and then you have ANOTHER
table that you are adding the same information into?

If so, stop. If referential integrity and a well-normalized relational
database are your goals, you don't store the same data more than once.

Also, check the "tablesdbdesign" newsgroup on the use of lookup data
fields -- the very strong consensus there is DON'T!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a data table with the two relevant fields for my question being
ReportSection and ReportSectionSort, plus many other information fields.
The
ReportSection is text; ReportSectionSort is a number. The ReportSection is
a
lookup field combo box tied to a query of a separate table which has the
ReportSections and ReportSection Sorts (which can be easily changed
there).
How can I populate the new ReportSectionSort field in the main data table
automatically upon selection of the ReportSection from the lookup combo
box?
I want to be able to enforce referential integrity so when I have to
change
Sections and Sorts in the secondary table, the changes will be reflected
automatically in the data table.

 

[Jeff Boyce]

Ray

I believe I understand what you say you want ... and I think you don't
really want it!

If I'm understanding, you still want to have the data in more than one
table, and you want it to stay synchronized. But it isn't necessary to
store it more than once to have it synchronized!

Only store the "1" in your main table. When you need to see what that
corresponds to (e.g., "Needs Priority"), use a query to connect the two
tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff,

I began thinking about what you wrote, and it made sense. The idea is
really
referrential integrity. I want to be able to change the ReportSectionSort
information - maintained in one table; but then when I go to the main
table
and select the ReportSection, I want the new sort information to
automatically take effect in the table. For example, let's say I have an
item
in the ReportSectionSort table that has a ReportSection equal to "Needs
Priority" and the corresponding ReportSectionSort field equals 1. Those
fields should be reflected in the main data table with other information
items. I want the flexibility to change the ReportSectionSort for the
items
tagged as "Needs Priority" to 2, 3, or whatever. But, when I go to the
main
data table, I want the change that I made to the ReportSectionSort table
to
be automatically reflected in the main data information table. Am I clear
enough?

Ray

Am I understanding you correctly? Are you saying that you have a table
that
stores [ReportSection] and [ReportSectionSort], and then you have ANOTHER
table that you are adding the same information into?

If so, stop. If referential integrity and a well-normalized relational
database are your goals, you don't store the same data more than once.

Also, check the "tablesdbdesign" newsgroup on the use of lookup data
fields -- the very strong consensus there is DON'T!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a data table with the two relevant fields for my question being
ReportSection and ReportSectionSort, plus many other information
fields.
The
ReportSection is text; ReportSectionSort is a number. The ReportSection
is
a
lookup field combo box tied to a query of a separate table which has
the
ReportSections and ReportSection Sorts (which can be easily changed
there).
How can I populate the new ReportSectionSort field in the main data
table
automatically upon selection of the ReportSection from the lookup combo
box?
I want to be able to enforce referential integrity so when I have to
change
Sections and Sorts in the secondary table, the changes will be
reflected
automatically in the data table.

 

[Guest]

Thanks...I got caught up in unnecessary complexity.

Ray

I believe I understand what you say you want ... and I think you don't
really want it!

If I'm understanding, you still want to have the data in more than one
table, and you want it to stay synchronized. But it isn't necessary to
store it more than once to have it synchronized!

Only store the "1" in your main table. When you need to see what that
corresponds to (e.g., "Needs Priority"), use a query to connect the two
tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff,

I began thinking about what you wrote, and it made sense. The idea is
really
referrential integrity. I want to be able to change the ReportSectionSort
information - maintained in one table; but then when I go to the main
table
and select the ReportSection, I want the new sort information to
automatically take effect in the table. For example, let's say I have an
item
in the ReportSectionSort table that has a ReportSection equal to "Needs
Priority" and the corresponding ReportSectionSort field equals 1. Those
fields should be reflected in the main data table with other information
items. I want the flexibility to change the ReportSectionSort for the
items
tagged as "Needs Priority" to 2, 3, or whatever. But, when I go to the
main
data table, I want the change that I made to the ReportSectionSort table
to
be automatically reflected in the main data information table. Am I clear
enough?

Ray

Am I understanding you correctly? Are you saying that you have a table
that
stores [ReportSection] and [ReportSectionSort], and then you have ANOTHER
table that you are adding the same information into?

If so, stop. If referential integrity and a well-normalized relational
database are your goals, you don't store the same data more than once.

Also, check the "tablesdbdesign" newsgroup on the use of lookup data
fields -- the very strong consensus there is DON'T!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a data table with the two relevant fields for my question being
ReportSection and ReportSectionSort, plus many other information
fields.
The
ReportSection is text; ReportSectionSort is a number. The ReportSection
is
a
lookup field combo box tied to a query of a separate table which has
the
ReportSections and ReportSection Sorts (which can be easily changed
there).
How can I populate the new ReportSectionSort field in the main data
table
automatically upon selection of the ReportSection from the lookup combo
box?
I want to be able to enforce referential integrity so when I have to
change
Sections and Sorts in the secondary table, the changes will be
reflected
automatically in the data table.