Nick McNamara
asked on
Update very old large database with errors
Good morning experts : )
I have the wonderful task of updating to the current era a database started at the beginning of Lotus123, and I am just hoping for some confirmation/advise on the path I think it should go. I have dabbled with Access for many years, but by no means would I call myself an expert. I can put together basic linked tables and easily build queries and reports, but have only dipped little toe into VB and macro side of this tool.
This is a manufacturing database with history back to the 60's that has been poked and prodded for many years but never cleaned up properly. Currently it is giving the old "Property value is too large" error when you try to make any changes. After many google searches I have determined it is due to the width of the main table (200+ columns). For IP reasons I don't want to give away too much direct information or post any copies of database so hoping the below brief gives enough info. Lets say we are manufacturing a widget that is run by an electric motor and can have various attachments/configurations
The main "index" table contains all manufacturing data on our widgets that I believe now needs to be broken down to individual multiple tables. As mentioned there are 200+ columns with individual row data indexed by a serial number of manufactured item. The columns could be broken into 3 - 4 groups, for example there is customer info including order numbers, there is motor info min max RPM's, kWs etc , widget info with various technical aspects. But all are linked by the serial number it was manufactured to.
Solution that I am thinking.
Break the index table into 3-4 distinguishable tables as mentioned.
Create a form that updates all tables together when we manufacture another widget.
I should also mention there are report forms that others use to access some of the data that would be contained in mostly the individual tables, but sometimes crossing between.
Question
1. I know is very simplified but is above solution the best path in your experiences or do you have another suggestion?
2. As mentioned the record data will be linked via unique serial number, this is where I am a little unsure how to proceed. Do I create a separate table for generating the serial number that is linked via input form to all tables? We obviously have a lot of previous history of existing serial number that I don't want to over write that needs to be searchable, I would need a starting point from say 65000, what is the cleanest way in your collective experiences to achieve this?
Any advice gratefully accepted, thanks in advance.
I have the wonderful task of updating to the current era a database started at the beginning of Lotus123, and I am just hoping for some confirmation/advise on the path I think it should go. I have dabbled with Access for many years, but by no means would I call myself an expert. I can put together basic linked tables and easily build queries and reports, but have only dipped little toe into VB and macro side of this tool.
This is a manufacturing database with history back to the 60's that has been poked and prodded for many years but never cleaned up properly. Currently it is giving the old "Property value is too large" error when you try to make any changes. After many google searches I have determined it is due to the width of the main table (200+ columns). For IP reasons I don't want to give away too much direct information or post any copies of database so hoping the below brief gives enough info. Lets say we are manufacturing a widget that is run by an electric motor and can have various attachments/configurations
The main "index" table contains all manufacturing data on our widgets that I believe now needs to be broken down to individual multiple tables. As mentioned there are 200+ columns with individual row data indexed by a serial number of manufactured item. The columns could be broken into 3 - 4 groups, for example there is customer info including order numbers, there is motor info min max RPM's, kWs etc , widget info with various technical aspects. But all are linked by the serial number it was manufactured to.
Solution that I am thinking.
Break the index table into 3-4 distinguishable tables as mentioned.
Create a form that updates all tables together when we manufacture another widget.
I should also mention there are report forms that others use to access some of the data that would be contained in mostly the individual tables, but sometimes crossing between.
Question
1. I know is very simplified but is above solution the best path in your experiences or do you have another suggestion?
2. As mentioned the record data will be linked via unique serial number, this is where I am a little unsure how to proceed. Do I create a separate table for generating the serial number that is linked via input form to all tables? We obviously have a lot of previous history of existing serial number that I don't want to over write that needs to be searchable, I would need a starting point from say 65000, what is the cleanest way in your collective experiences to achieve this?
Any advice gratefully accepted, thanks in advance.
Usually these kind of gigantic tables are due to an extensive list attributes that we keep adding and adding and so on...
The solution...is pretty easy
A table that holds the Attributes with Field like This :
AttributeID(Autonumber) Attribute
1 motor info
2 min max RPM's
3 kWs
....etc
Then you have a table that holds all these attributes per specific widget
WidgetAttributesID(Autonum
1 13300 2 200-5000
2 19377 2 800-1700
3 19377 3 5
4 13300 1 KLC
On the main widget form you introduce this above table as a subform (Just 1 combobox to select the Attribute and 1 text box for the value...the Widget ID should be provided by the Master/Child field) and you just choosing what info you have available and you enter it.
The solution...is pretty easy
A table that holds the Attributes with Field like This :
AttributeID(Autonumber) Attribute
1 motor info
2 min max RPM's
3 kWs
....etc
Then you have a table that holds all these attributes per specific widget
WidgetAttributesID(Autonum
1 13300 2 200-5000
2 19377 2 800-1700
3 19377 3 5
4 13300 1 KLC
On the main widget form you introduce this above table as a subform (Just 1 combobox to select the Attribute and 1 text box for the value...the Widget ID should be provided by the Master/Child field) and you just choosing what info you have available and you enter it.
From your description, I'd suggest a minimum of three tables:
1. Customers
2. Widgets
3. Orders
The widgets table would contain all widget info... serial number, Part Name, Description, etc, with either Serial Number or an autonumber primary key.
The customers table would contain customer info... Company Name, address, Contact number, etc, with an autonumber/Primary Key.
The Orders table would contain order info: CompanyID, SerialNumber (or widgetID), DateOrdered, DatePromised, DateDelivered, etc.
After that, check your widgets table for data that defines additional processes. Are you tracking internal work orders, quality control processes, etc through the Widgets table? All of these distinct processes should be tracked in separate tables using the Widget Serial Number (or ID) as a foreign key. Staff involved in these processes should be tracked in a separate Employees table, with EmployeeID used as a linked field in the various processes tables...
And also separate out attributes as John described above.
This may seem like a lot of work, but it will normalize your tables and minimize redundant data entry in the long run...
1. Customers
2. Widgets
3. Orders
The widgets table would contain all widget info... serial number, Part Name, Description, etc, with either Serial Number or an autonumber primary key.
The customers table would contain customer info... Company Name, address, Contact number, etc, with an autonumber/Primary Key.
The Orders table would contain order info: CompanyID, SerialNumber (or widgetID), DateOrdered, DatePromised, DateDelivered, etc.
After that, check your widgets table for data that defines additional processes. Are you tracking internal work orders, quality control processes, etc through the Widgets table? All of these distinct processes should be tracked in separate tables using the Widget Serial Number (or ID) as a foreign key. Staff involved in these processes should be tracked in a separate Employees table, with EmployeeID used as a linked field in the various processes tables...
And also separate out attributes as John described above.
This may seem like a lot of work, but it will normalize your tables and minimize redundant data entry in the long run...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi all thank you all for your replies, big thanks to Scott I think we were on same wave length as this is almost what I had planned to do : )
Kind regards,
Nick.
Kind regards,
Nick.
It is very likely that you'll end up with 1 table of < 40 columns with basic header information, a second table for customer information, a junction table because widget to customer is many-to-many. A fourth table might be very long and narrow and it would contain a list of attributes with their values. Each row would have an attribute name and a value allong with the foreign key.
AttributeID (autonumber PK)
WidgetID (foreign key to the widget table)
AttributeType (you probably would make a separate table of just types and use an id but you could use the name if that makes sense. values would be something like min max RPM's, kWs etc
AttributeValue - whatever the spec it. this field would probably be numeric
UnitOfMeasure - whatever the UOM is for the number in the value above such as centimeters, pounds, etc.
You also need a unique index on this table for WidgetID + AttributeType to ensure that each attribute is entered only once for each Widget.