Consolidating field values

[StevieD via AccessMonster.com]

Hi.
Just a quick one (i hope).

I have 3 column in my database. (Mach1, Mach2, and Mach3)
Each column looksup values using a combo box.

There is a 4 column also. (MachGrouped)

This field needs to automatically group the values from the Mach1, Mach2, and
Mach3 fields each time a selection is made or altered.

For example:
Mach1 - value "Tuesday"
Mach2 - value "Thursday"
Mach3 - value "Wednesday"

Should show as "Tuesday, Thursday, Wednesday" in MachGrouped column.

And if Mach2 (for example) was switched to "Friday" MachGrouped would then
update to...
"Tuesday, Friday, Wednesday" and so on...

This should be automatic though, as opposed to manually running a query each
time to update it all.

 

[Duane Hookom]

Are you asking how to store a value that is always the result of a
calculation? This really shouldn't be done since you can assign an
expression in a query or control source. There is rarely a need to store
calculated values.

 

[Marshall Barton]

I have 3 column in my database. (Mach1, Mach2, and Mach3)
Each column looksup values using a combo box.

There is a 4 column also. (MachGrouped)

This field needs to automatically group the values from the Mach1, Mach2, and
Mach3 fields each time a selection is made or altered.

For example:
Mach1 - value "Tuesday"
Mach2 - value "Thursday"
Mach3 - value "Wednesday"

Should show as "Tuesday, Thursday, Wednesday" in MachGrouped column.

And if Mach2 (for example) was switched to "Friday" MachGrouped would then
update to...
"Tuesday, Friday, Wednesday" and so on...

This should be automatic though, as opposed to manually running a query each
time to update it all.

If you really feel that should be automatic, then I suggest
that you back to doing this in Excel.

If you want to work in a database, then one of the very
first rules is to never store a value that can be derived
from other values in the record. Instead you should
calculate the derived value whenever it needs to displayed
on a form or report. In this case, it can be done very
simply in either a query of a text box expression:

=Mach1 & Mach2 & Mach3

Now, that makes it obvious that you need to get up to speed
on the other rules of database design. The very first rule
of databases is that you should not have multiple columns in
a table that refer to the same kind of data. Instead, the
repeated columns should be organized as rows in another
table.

I don't want to sound like I'm harping on the issue, but
your question is just a hint of the trouble you are creating
for yourself. It is extemely important to get these basics
correct before you start building queries, forms and reports
based on a table design that will tie you in knots. I'd
like to suggest that you look up Normalization in Help, but
I can't find in the latest versions. Try doing a quick
Google for it and just pick a few of the articles that only
cover the basics.

 

[John Spencer]

Why? You can always use a query to combine the three columns when you want
to display them combined. That way you don't ever have to worry about a
change in one of the first three columns getting made and then 4th column
not getting updated or the 4th column being changed and one or all of the
other columns not agreeing.

Just use a calculated column when you need the combined value. Something
like the following will probably give you what you want

Field: MachGrouped: MID((", " + Mach1) & (", " + Mach2) & (", " + Mach3) ,
3)