If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data....
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 =) |
#2
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data....
On 25 mrt, 12:27, Aso wrote:
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 |
#3
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data....
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. -- Daryl S "Aso" wrote: 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 =) |
#4
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data.
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" wrote: 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. -- Daryl S "Aso" wrote: 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 =) |
#5
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data.
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 -- Daryl S "Aso" wrote: 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" wrote: 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. -- Daryl S "Aso" wrote: 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 =) |
#6
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data.
On Thu, 25 Mar 2010 07:38:01 -0700, Aso wrote:
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. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data.
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" wrote: 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" wrote: 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. -- Daryl S "Aso" wrote: 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 =) |
#8
|
|||
|
|||
Altering Combo Box Choices and the bearing on historical data.
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 "John W. Vinson" wrote: On Thu, 25 Mar 2010 07:38:01 -0700, Aso wrote: 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. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|