Any and all help greatly, truly, appreciated. I've tried!
Goal: database to hold 2,600 Job Description records
- needs to provide 2,000 fields : almost all Long Text. Maybe 1/3 will be filled for each record because they have data in different sections.
None of this data repeats: each data point for each record is completely unique: all are one-to-one.
I created the field names and sample text in Excel. I broke the fields (columns) and sample data into 18 files, and imported as individual tables. (Because all of the data is flat, I would have preferred to upload as a single table.)
I see that there is a 255 field per table limit, a 65,000 character per row limit, but the one that stopped me was the "maximum start position of 32767" to create a new field. Long Text sample data has >260 characters per cell to force that data type)
DATA
Each Job Description:
- unique ID (DSM) -an 8-digit code
- Header Data
- Essential Job Functions (EJFs):
- 9 EJF possible per Job Description * 14 possible Measures per EJF * 14 possible Examples per Measure = 9+(9*14)+(9*14*14) = 1899 EJF fields per Job Description
Header | EJF I | EJF II | EJF III | EJF IV | EJF V | EJF VI | EJF VII | EJF VIII | EJF IX
MY DATABASE
the best I could do using Excel to create the fields:
Header | EJF I Part 1 | EJF I Part 2 | EJF II Part 1 | EJF II Part 2 | EJF III Part 1 | EJF III Part 2 | EJF IV Part 1 | EJF IV Part 2 | EJF V Part 1 | EJF V Part 2 | EJF VI Part 1 | EJF VI Part 2 | EJF VII Part 1 | EJF VII Part 2 | EJF VIII Part 1 | EJF VIII Part | EJF VIII Part 1 | EJF IX Part 1 | EJF IX Part 2
(Each of the EJF fields are Long Text, so the 32767 maximum start position for a new field required that each EJF set be broken into two.)
CREATING RELATIONSHIPS, IMPORTING DATA IN THE FUTURE, PROVIDING FORMS FOR MANUAL ENTRY
First, I would appreciate any advice, corrections, better ways to do or regard anything I've done/thought/communicated so far!
If I move forward with what I've created, I still need to:
1. Create relationships between these tables so that they are all one-to-one based on the Job Description's code (DSM) - I used the same code as the key for each table. Is this correct?
2. enable imports of 2,600 Job Descriptions - hopefully in full files, not each load broken into 19 upload files...
3. create forms that can be used to either update existing Job Description records, or create new ones - with fields from all of the tables.
RELATIONSHIP LAYOUT:
There are no relationships yet. I don't know how to get the different tables connected one-to-one based on the DSM code key that is in each table. (Should there instead be linking foreign keys?)
FILES ATTACHED HERE
1. the database created, with three test records [Each data point includes the name of the field and the row number of the record to test if the data matches up] (Job_Descriptions_DB.zip)
2. a zip file of the 19 Excel files used to create the database [including that test data] (JD_EJF_Data_Files.zip)
3. an Excel file of all of the field names and their field type (Job_Descriptions_All Fields_List.zip)
SECONDARY ISSUES
There are additional things -like the field type for the DSM code, the dates, and the Exempt, Non-Exempt, Bargaining Unit fields which I hope to create as a single radio button set... But, only once/if the database works! THANK YOU!!!!