Altering Combo Box Choices and the bearing on historical data....

[Aso]

I am wracking my brain trying to not screw up years of data in a database I
created at work. I need to updated some drop down menu choices and some that
have been used need to be eliminated. I am kind of brainfarting here and I
want to make sure that by changing these items or removing them that it will
not affect the historical data in the tables where the full records are kept.
I need some reassurance if at all possible.

I made a dummy database and have played around with it and it seems to
maintain the data but this is a little nervewracking. My only other option if
this would not work, is to archive the tables/data and I cant figure out how
i would compare new data with old data (Boss' wish there) not that it
completely makes sense to. The purpose of that is to have the option to find
the trends with the current back through such a time frame in what would be
the archived data in a one stop shop in lieu of dumping this that and the
other into Excel for comparison. Any reassurance/help is EXTREMELY
appreciated.

Thank you =)

 

[XPS350]

I am wracking my brain trying to not screw up years of data in a database I
created at work. I need to updated some drop down menu choices and some that
have been used need to be eliminated. I am kind of brainfarting here and I
want to make sure that by changing these items or removing them that it will
not affect the historical data in the tables where the full records are kept.
I need some reassurance if at all possible.

I made a dummy database and have played around with it and it seems to
maintain the data but this is a little nervewracking. My only other option if
this would not work, is to archive the tables/data and I cant figure out how
i would compare new data with old data (Boss' wish there) not that it
completely makes sense to. The purpose of that is to have the option to find
the trends with the current back through such a time frame in what would be
the archived data in a one stop shop in lieu of dumping this that and the
other into Excel for comparison. Any reassurance/help is EXTREMELY
appreciated.

Thank you =)

It is not possible to reassure you without knowing anything about the
database.

How is the combo populated?
Are the values in a table?
What relations come with that table?
What do you mean by altering the combo in the first place?

Groeten,

Peter
http://access.xps350.com

 

[Daryl S]

Aso -

Can you help us understand the issue a little more? You did not mention any
table changes, only menu changes.

If you are not making changes to the tables, then you should not be losing
any historical data.

If you are changing menu items, then you may be deciding to not use certain
forms, which means you may not be collecting that data going forward, so you
would have a problem comparing non-existent current data to historical data.

If instead you are changing values used in combo boxes or list boxes (rather
than menu choices), then you do need to think things out. I assume you are
using values from a lookup table. In that case, if you are trying to add or
remove items from a lookup table, I would suggest you add an 'ExpirationDate'
field to the table, and instead of deleting old items, put in an expiration
date. Current items should not have an expriation date. Then when you are
adding new records, the combo box should only include records without an
expiration date. When viewing old records, you will still have the old
records in the table.

If your issue is something else, then please explain if any tables are being
changed, and give us an example of what is being changed and why you are
concerned.

 

[Aso]

My apologies to those trying to help me, I did leave out some crucial info.

The combo box is based off a look up table of values as code, classification
and description of the code for the user's understanding. Some of the codes
due to either a change in the manufacturing process or just improper naming
of the code to begin with could be the reason for requiring adjustment. We
have years of data based upon the coding but it is in need of
updating/cleaning up on many levels.

So we have...
Code Classification Description
D112 Dimensional Total Runout
T96 Threads Threads Non-gaging
S93 Stator Cutback
S125 Stator Rubber Cutback - Overcut (Short)
S126 Stator Rubber Cutback - Undercut (Long)

I know an update to the description would alter all the records with that
code. However, a code like "S93" is now obsolete because it became "S125" and
"S126". Therefore, I would need to make S93 no longer an option. Training
only goes so far when dealing with so many people, so I would want to make it
where "S93" is no longer available for choosing. (Hope this is now making
more sense...)

Hmm expiration date. I had not thought of something along those lines,
though other than a person making a visual choice (because some many get used
to a number and fail to "look" before choosing) is there a way with the
expiration field to disable that choice so it would not show up on the field
choices?

Aso =)

 

[Daryl S]

Aso -

After adding an ExpriationDate fidl to the table, then in the combo box, set
the row source to something like this (use your table and field names):

Select Code from CodeTable WHERE ExpirationDate Is Null

Or if you show multiple columns:

Select Code, Classification, Description from CodeTable WHERE ExpirationDate
Is Null

 

[John W. Vinson]

My apologies to those trying to help me, I did leave out some crucial info.

The combo box is based off a look up table of values as code, classification
and description of the code for the user's understanding. Some of the codes
due to either a change in the manufacturing process or just improper naming
of the code to begin with could be the reason for requiring adjustment. We
have years of data based upon the coding but it is in need of
updating/cleaning up on many levels.

So we have...
Code Classification Description
D112 Dimensional Total Runout
T96 Threads Threads Non-gaging
S93 Stator Cutback
S125 Stator Rubber Cutback - Overcut (Short)
S126 Stator Rubber Cutback - Undercut (Long)

I know an update to the description would alter all the records with that
code. However, a code like "S93" is now obsolete because it became "S125" and
"S126". Therefore, I would need to make S93 no longer an option. Training
only goes so far when dealing with so many people, so I would want to make it
where "S93" is no longer available for choosing. (Hope this is now making
more sense...)

Hmm expiration date. I had not thought of something along those lines,
though other than a person making a visual choice (because some many get used
to a number and fail to "look" before choosing) is there a way with the
expiration field to disable that choice so it would not show up on the field
choices?

You can (and certainly should!) base the combo, not directly on your table,
but on a Query. This query does not need to include all the records in the
table; if a code is obsolete, just don't include it in the query. You may need
to add a field (yes/no field "Active", an effective date, etc.) to use as a
criterion to select which codes should be included and which should not.

You may have to change the combo box properties - you don't say which is the
bound column, how it's sorted or what the column count is.

 

[Sunny Ho]

Aso, I think you can adopt the suggestion from Daryl S in adding an
additional field with datatype boolean (yes / no), then you can categorize
the combo data by "yes" or "no", indicating if the value is valid or not.

Then by using query for filtering out the one with "yes" and link to your
specific field in your desinated "form", it may help you from preserve the
old one and meanwhile controlling the "active" one to be display in your
combo.

Hope you can read my poor english

Sunny

 

[Aso]

You know, thank you so much I honestly think I didnt flip the switch to "On"
in my brain this week.

Thanks to all for your help and responses. =)

Aso