TimothyA
asked on
Importing data from Spreadsheet formated Database data into relational Database structure
HiTechCoach and All,
I have a been working for some time to transform actively growing data base into a relational data system that is normalized.
Data is shown in tblTotal and frmTotal of attached PSAssm0111 file.
I believe I have now got the beginning of a relational system started. I have built tables tblLUComponents and tblLUComponentList and have populated each with heading data from existing working tblTotal and frmTotal. Note I have split out non-changing data into tblSite. I know I will also have to move this data into the new relational system.
What is the best way to convert all data from tblTotal into this relational system? I have in the neighbor of 60,000 data fields.
Your help is greatly needed.
Timothy
PSAssm0111.accdb
I have a been working for some time to transform actively growing data base into a relational data system that is normalized.
Data is shown in tblTotal and frmTotal of attached PSAssm0111 file.
I believe I have now got the beginning of a relational system started. I have built tables tblLUComponents and tblLUComponentList and have populated each with heading data from existing working tblTotal and frmTotal. Note I have split out non-changing data into tblSite. I know I will also have to move this data into the new relational system.
What is the best way to convert all data from tblTotal into this relational system? I have in the neighbor of 60,000 data fields.
Your help is greatly needed.
Timothy
PSAssm0111.accdb
jerryb30
Data fields, or records?
ASKER
Jerry,
Data fields.
There are 301 records and 177 fields in each record in tblTotal and another 301 records each with ~20 fields in tblSite.
Thanks for your quick response.
Timothy
Data fields.
There are 301 records and 177 fields in each record in tblTotal and another 301 records each with ~20 fields in tblSite.
Thanks for your quick response.
Timothy
Not sure because I don't know what each field represents.
But in a typically un-normalized table, the tip-off will be that you have "Unrelated Fields"
This may be "one" issue with your Totals table.
See this excel file for an example of the un-normalized table and the normalized design.
Book1.xlsx
But in a typically un-normalized table, the tip-off will be that you have "Unrelated Fields"
This may be "one" issue with your Totals table.
See this excel file for an example of the un-normalized table and the normalized design.
Book1.xlsx
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks for your input.
I am beginning to understand normalization.
As I see it now tblTotals has a number of problems preventing me form developing the system into a normalized relational system.
As I now set the next step in the conversion process is to set up the existing table columns into rows. I have developed a way to make this change using excel. However, I discovered and even much bigger problem, existing tblTotals table fields have multiple data types (number, text, and Date/Time). As I understand it the new row format (fields) can only have a single data type.
This change over using excel is further hampered in trying to import back to Access, by the limit of 255 columns Excel to Access. I presently have 300+ columns and growing.
Any guidance will be greatly appreciated.
Timothy
I am beginning to understand normalization.
As I see it now tblTotals has a number of problems preventing me form developing the system into a normalized relational system.
As I now set the next step in the conversion process is to set up the existing table columns into rows. I have developed a way to make this change using excel. However, I discovered and even much bigger problem, existing tblTotals table fields have multiple data types (number, text, and Date/Time). As I understand it the new row format (fields) can only have a single data type.
This change over using excel is further hampered in trying to import back to Access, by the limit of 255 columns Excel to Access. I presently have 300+ columns and growing.
Any guidance will be greatly appreciated.
Timothy
ASKER
Question is no longer relevant. Need to close this thread.
Timothy
Timothy
ASKER
I have come to understand my question was too vague. I do greatly appreciate the general guidance and help.