Table Updates - PK changes - help (2 Viewers)

[wmix]

The company I work for is in the midst of a sale and the new owner wants to utilize the Access database I've created. However, as part of the purchase agreement, he only gets access to some of the records. With this in mind, I'm creating a new database, a large project but also a great chance for me to clean-up the old database (I've learned a lot about Access and databases over the past 8+ years). With all this in mind, I'm on to one of the greatest challenges I've faced so far, cleaning up the product list.

tblProduct
ProductID - PK - autonumber
ProductName
etc...,

We currently have around 250 products in the list. Of these, only about 105 are "active" products. The rest have all been discontinued. The new owner wants only the active products in the new database, and he'll be adding new ones eventually.

Originally, I thought I would just upload the list of active products, eliminating all the inactive ProductID numbers, starting fresh. In theory it sounded like a good solution, until I found out that the new owner is getting the past 2-years of product-sold numbers. So, my idea of uploading only "active" ProductID information cannot be done because the previous ProductID from the original database would not match the new database ProductID. (example, ProductID 1, 3, 15, 27, 32, etc. are all currently discontinued items, so right from the get-go the new numbers do not match previous ones).

Has anyone every tackled this type of issue in the past? Any best practices or theories on a way to achieve a "cleaner" product list but use previous ProductID for specific items?

Thank you for your insights.

 

[GPGeorge]

The company I work for is in the midst of a sale and the new owner wants to utilize the Access database I've created. However, as part of the purchase agreement, he only gets access to some of the records. With this in mind, I'm creating a new database, a large project but also a great chance for me to clean-up the old database (I've learned a lot about Access and databases over the past 8+ years). With all this in mind, I'm on to one of the greatest challenges I've faced so far, cleaning up the product list.

tblProduct
ProductID - PK - autonumber
ProductName
etc...,

We currently have around 250 products in the list. Of these, only about 105 are "active" products. The rest have all been discontinued. The new owner wants only the active products in the new database, and he'll be adding new ones eventually.

Originally, I thought I would just upload the list of active products, eliminating all the inactive ProductID numbers, starting fresh. In theory it sounded like a good solution, until I found out that the new owner is getting the past 2-years of product-sold numbers. So, my idea of uploading only "active" ProductID information cannot be done because the previous ProductID from the original database would not match the new database ProductID. (example, ProductID 1, 3, 15, 27, 32, etc. are all currently discontinued items, so right from the get-go the new numbers do not match previous ones).

Has anyone every tackled this type of issue in the past? Any best practices or theories on a way to achieve a "cleaner" product list but use previous ProductID for specific items?

Thank you for your insights.

There is no reason to try to change current ProductIDs. Import them "as is" and continue on from there. Any new products will get their own new values as they are added.

In fact, as a general concept, no external meaning should ever be assigned to Autonumber Primary Keys.

If I understand the situation correctly, there is an inherent conflict in the requirements, though, which might need to be clarified before you proceed.

Your new owner wants only "active" products, presumably as of the date ownership changes.
Your new owner also wants historically sold products, active or inactive, in the two years prior to the date ownership changes.

If any products sold in the last 2 years have become "inactive" as of the date ownership changes, you either have to override the "active" rule, or you have to override the "2 year rule" for that subset of products. Is that not correct?

Again, this is a separate question from the values of Autonumber-generated Primary Keys. Migrate them as-is to the new database and keep all of the related foreign keys in other tables intact.

 

[The_Doc_Man]

George beat me to it.

Point out to the owner that if he wants historical data he can't drop currently inactive items. Then offer to include a flag that says Active/Inactive (a Yes/No field will suffice and is the simplest option, I think) and you then need to assure that every major form or "current item" report the new owner sees will be driven by a query that includes in its WHERE clause "... AND NOT [Active] ..." so that he never has to be bothered with inactive stuff. But then, for the DB's admin, include a form that DOES NOT check for the [Active] flag so that you could, if needed, REACTIVATE the product and so that you can provide those historical reports.

I used this method in a computer hosting center where certain machines would be retired (and deactivated), but it was a military site that had a hard limit of six months full retention of all data for reporting and accounting purposes, and there were exceptions to the six month limit that would extend it if we were tracking discrepancies.

 

[GPGeorge]

There is no reason to try to change current ProductIDs. Import them "as is" and continue on from there. Any new products will get their own new values as they are added.

In fact, as a general concept, no external meaning should ever be assigned to Autonumber Primary Keys.

If I understand the situation correctly, there is an inherent conflict in the requirements, though, which might need to be clarified before you proceed.

Your new owner wants only "active" products, presumably as of the date ownership changes.
Your new owner also wants historically sold products, active or inactive, in the two years prior to the date ownership changes.

If any products sold in the last 2 years have become "inactive" as of the date ownership changes, you either have to override the "active" rule, or you have to override the "2 year rule" for that subset of products. Is that not correct?

Again, this is a separate question from the values of Autonumber-generated Primary Keys. Migrate them as-is to the new database and keep all of the related foreign keys in other tables intact.

Like The_Doc_Man said. This time he beat me.
Depending on WHY the new owner doesn't want the inactive products, in fact, you might not need to remove any of them during the migration.

You can add an "InactiveDate" field to the product table and set the value for that field to the date on which the products were deactivated, or if not known, the date on which ownership was transferred.

Any query which is based on that table, of course, would have to include a filter to exclude products with an inactivedate.

 

[wmix]

There is no reason to try to change current ProductIDs. Import them "as is" and continue on from there. Any new products will get their own new values as they are added.

In fact, as a general concept, no external meaning should ever be assigned to Autonumber Primary Keys.

If I understand the situation correctly, there is an inherent conflict in the requirements, though, which might need to be clarified before you proceed.

Your new owner wants only "active" products, presumably as of the date ownership changes.
Your new owner also wants historically sold products, active or inactive, in the two years prior to the date ownership changes.

If any products sold in the last 2 years have become "inactive" as of the date ownership changes, you either have to override the "active" rule, or you have to override the "2 year rule" for that subset of products. Is that not correct?

Again, this is a separate question from the values of Autonumber-generated Primary Keys. Migrate them as-is to the new database and keep all of the related foreign keys in other tables intact.

Thank you for your reply, George and your feedback.

I've used the PK auto-number to identify many things, Examples...
tblmachine - PK, autonumber, MachineID
tblProduct - PK, autonumber, ProductID
tblProperty - PK, autonumber, PropertyID

If I'm understanding you correctly, it would be better to have something more like this?

tblproduct
tblproductpk, PK - autonumber (not used for anything but to identify each individual item)
ProductID - as an integer with no duplicates

This would clean things up, I never thought about it before, and it would solve the issue. I could keep the productID, all the references to past information would match, and I would have a 'cleaner' database with only the active products.

I did talk to the new owner, and he said that he is OKAY with have inactive products in the database if they have been used in the past two years.

 

[wmix]

George beat me to it.

Point out to the owner that if he wants historical data he can't drop currently inactive items. Then offer to include a flag that says Active/Inactive (a Yes/No field will suffice and is the simplest option, I think) and you then need to assure that every major form or "current item" report the new owner sees will be driven by a query that includes in its WHERE clause "... AND NOT [Active] ..." so that he never has to be bothered with inactive stuff. But then, for the DB's admin, include a form that DOES NOT check for the [Active] flag so that you could, if needed, REACTIVATE the product and so that you can provide those historical reports.

I used this method in a computer hosting center where certain machines would be retired (and deactivated), but it was a military site that had a hard limit of six months full retention of all data for reporting and accounting purposes, and there were exceptions to the six month limit that would extend it if we were tracking discrepancies.

Thank you, Doc Man. Yes, a flag is a good idea. I have one right now that just says "discontinued" - items that are simply no longer made. I should probably have a different one for inactive - an item that is not discontinued but we are not currently using. I'll add that, it will make life easier.

 

[Minty]

An ArchivedDate flag is something I use extensively in a lot of tables.
It makes it easy to manage historic data and keep data entry forms relative to the live environment.

IMHO there is nothing intrinsically wrong with displaying a Autonumber ID as a unique reference as long as no relevance is attached to the actual value of it and you don't mind that it might not run in sequence, there could be gaps.

 

[plog]

I don't understand the premise. Why not copy the database and just delete everything 'inactive' related and give him that copy?

 

[GPGeorge]

Thank you for your reply, George and your feedback.

I've used the PK auto-number to identify many things, Examples...
tblmachine - PK, autonumber, MachineID
tblProduct - PK, autonumber, ProductID
tblProperty - PK, autonumber, PropertyID

If I'm understanding you correctly, it would be better to have something more like this?

tblproduct
tblproductpk, PK - autonumber (not used for anything but to identify each individual item)
ProductID - as an integer with no duplicates

This would clean things up, I never thought about it before, and it would solve the issue. I could keep the productID, all the references to past information would match, and I would have a 'cleaner' database with only the active products.

I did talk to the new owner, and he said that he is OKAY with have inactive products in the database if they have been used in the past two years.

No, adding a different ProductID is not necessary. UNLESS, that is one your organization uses externally, it's not necessary. But they serve two different purposes. The Primary Key is internal to the table and related tables. A ProductID, or to make it maybe a little clearer, a ProductCode, would be the value listed in your catalog, for example.

Primary Keys never change. ProductCodes could, under the right circumstances, be changed.

 

[GPGeorge]

I don't understand the premise. Why not copy the database and just delete everything 'inactive' related and give him that copy?

History.

 

[plog]

History.

Nope.

The new owner wants only the active products in the new database, and he'll be adding new ones eventually.

 

[wmix]

I don't understand the premise. Why not copy the database and just delete everything 'inactive' related and give him that copy?

The new owner uses an online system currently and it's expensive and not as functional as the one I've created. An agreement was made that if the new owner buys Access, I can export parts of the existing database that are part of the sale.

I was going to copy the entire database and then just remove all the information from tables that's not part of the sale. However, I was learning Access as I was building the program, and honestly, a lot of it is junk and needs improvement. So, while it's not the fastest way to do things, I've enjoyed the challenge of the improving upon the current database, so the new database is cleaner and overall easier to use.

 

[Ken Sheridan]

Yes, a flag is a good idea. I have one right now that just says "discontinued" - items that are simply no longer made. I should probably have a different one for inactive - an item that is not discontinued but we are not currently using. I'll add that, it will make life easier.

The attached file illustrates how inactive entities, employees in this case, can be retained in a database, but not shown as an option for new records, when selecting an employee in a combo box. This is achieved by using the following as the combo box's RowSource property:

SELECT Employees.EmployeeID, Employees.Department, LastName & ", " & Firstname
FROM Employees
WHERE Employees.Active = TRUE OR Employees.EmployeeID = [Form]![EmployeeID]
ORDER BY Employees.LastName, Employees.FirstName;

The EmployeeID and Department columns are hidden by setting their dimensions to zero in the control's ColumnWidths property. You could of course reference Active and Discontinued columns in the WHERE clause in a parenthesized AND NOT operation. Note the use of the Form property to reference the current form.

The combo box is requeried in the form's Current event procedure to exclude the inactive employees from the list, with the exception of the current employee if they are inactive, in which case they are included in the list and therefore show in the control.

This is simple to do in single form view, but a little trickier in continuous forms view, where it is necessary to use a 'hybrid' control made up of a text box carefully superimposed on a combo box to appear to the user as a single combo box control.

 

[wmix]

The attached file illustrates how inactive entities, employees in this case, can be retained in a database, but not shown as an option for new record, when selecting an employee in a combo box. This is achieved by using the following as the combo box's RowSource property:

SELECT Employees.EmployeeID, Employees.Department, LastName & ", " & Firstname
FROM Employees
WHERE Employees.Active = TRUE OR Employees.EmployeeID = [Form]![EmployeeID]
ORDER BY Employees.LastName, Employees.FirstName;

The EmployeeID and Department columns are hidden by setting their dimensions to zero in the control's ColumnWidths property. You could of course reference Active and Discontinued columns in the WHERE clause in a parenthesized OR NOT operation. Note the use of the Form property to reference the current form.

The combo box is requeried in the form's Current event procedure to exclude the inactive employees from the list, with the exception of the current employee if they are inactive, in which case they are included in the list and therefore show in the control.

This is simple to do in single form view, but a little trickier in continuous forms view, where it is necessary to use a 'hybrid' control made up of a text box carefully superimposed on a combo box to appear to the user as a single combo box control.

Thank you, I appreciate this, I'll look into how I can use this.

 

[The_Doc_Man]

Nope.

Yep. The owner also wants historical reports. Don't want to undercut the basis for history.

 

[Gasman]

The new owner uses an online system currently and it's expensive and not as functional as the one I've created. An agreement was made that if the new owner buys Access, I can export parts of the existing database that are part of the sale.

I was going to copy the entire database and then just remove all the information from tables that's not part of the sale. However, I was learning Access as I was building the program, and honestly, a lot of it is junk and needs improvement. So, while it's not the fastest way to do things, I've enjoyed the challenge of the improving upon the current database, so the new database is cleaner and overall easier to use.

TBH, I would leave it alone.
It is working and presumably the new owner has seen it working?

You have a major opportunity to break it with 'tidying it up'.

Whilst it might not be the best behind the scenes, if it ain't broke, don't fix it.
Just remove what data that is not required.

 

[CJ_London]

It is likely your new boss will want a 'rolling' view of currently active products

If you add an inactive flag, I would make it a date rather than a yes/no field. That way you mark as 'inactive over two years go' with a date two years ago, 'inactive in the last two years' with a current date and leave null (or a date way into the future) for for all currently active ones. You will probably need to modify some combos and reports (e.g. creating a new order, only show where the inactive flag is null, for reports show where null or inactive date > two years ago.