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.

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

"Ray S." <(E-Mail Removed)> wrote in message
news:E986A1AD-8E0A-44D3-9BF3-(E-Mail Removed)...
>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.

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?

"Jeff Boyce" wrote:

> 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
>
> "Ray S." <(E-Mail Removed)> wrote in message
> news:E986A1AD-8E0A-44D3-9BF3-(E-Mail Removed)...
> >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.
>
>
>

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

"Ray S." <(E-Mail Removed)> wrote in message
news63EF3DC-48A7-495F-892B-(E-Mail Removed)...
> 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?
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>> "Ray S." <(E-Mail Removed)> wrote in message
>> news:E986A1AD-8E0A-44D3-9BF3-(E-Mail Removed)...
>> >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.
>>
>>
>>

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

"Jeff Boyce" wrote:

> 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
>
> "Ray S." <(E-Mail Removed)> wrote in message
> news63EF3DC-48A7-495F-892B-(E-Mail Removed)...
> > 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?
> >
> > "Jeff Boyce" wrote:
> >
> >> 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
> >>
> >> "Ray S." <(E-Mail Removed)> wrote in message
> >> news:E986A1AD-8E0A-44D3-9BF3-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>