Results 1 to 15 of 15
  1. #1
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8

    Question Create database; 2,000 Long Text fields; all data one-to-one (flat); Excel import to create and load

    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?)Click image for larger version. 

Name:	Relationship_layout.jpg 
Views:	22 
Size:	181.7 KB 
ID:	23162

    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!!!!
    Attached Files Attached Files

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't need to look at your data to see you need to understand about normalisation. Access is not excel.

    DATA
    Each Job Description:
    - unique ID (DSM) -an 8-digit code - OK
    - Header Data - OK
    - Essential Job Functions (EJFs): - Not OK
    - 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

    would think you would only need 4 tables, not the myriad you have.

    tblJobDesc
    JobDescPK
    JobDescData

    tblEJFs
    EJFPK autonumber
    JobDescFK
    EJFDesc

    tblMeasures
    MeasurePK autonumber
    EJFFK
    MeassureDesc

    tblExamples
    ExamplePK
    MeasureFK
    ExampleDesc

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8
    Hello Ajax,
    thank you! I'm sorry, but I'm confused: All of the EJF's, their Measures, and those Measure's Examples are unintelligent text. Not a single entry repeats in another document. It can't be used for another Job Description. The whole doggone dataset is un-reusable, unintelligent text. There is no hierarchy in the data. Each Job Description, each of its Measures, and those Measures' Examples, are unique to that Job Description . We DO want to get to structure, but this is our first step. I'll upload an image of one of our source documents that might shed light. The details of 2,600 of these individual, self-contained documents, are what we are creating a database to store... Did I misunderstand your suggestion? Thank you for your help!
    Click image for larger version. 

Name:	Example Job Description EJF.jpg 
Views:	21 
Size:	242.1 KB 
ID:	23171

  5. #5
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8

    Unhappy

    Thank you. I'm embarrassed to say that there is not relational anything here. It is one lumpy chunk of flat data we are trying to put into a database. Basically, like Ajax inferred, trying to treat Access as Excel...

  6. #6
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8
    And, I forgot to say thank you. I've saved the Fundamentals to study. Thank you.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So does the HeaderData table have some kind of relationship to each of the EJF tables, even if it is 1-to-1? Isn't that what the common DSM fields are for?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8
    Hello June7, yes, exactly. Thank you. The DSM is the code that designates a specific Job Description. If we had our 2,600 records in the database, there would be:

    - 2,600 records in the Header table
    - 2,600 records in each of the EJF tables, all identified with the DSM that would link them to their sister record in the Header table, and in the other EJF tables.

    Thank you, Sadhya

  9. #9
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for example you said

    2,600 records in the Header table,
    2,600 records in each of the EJF tables
    and previously
    9 EJF possible per Job Description
    this implies anything from 1 to 9 - so which is it? 9, 1? or anything in between?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So this is presently a 1-to-1 relationship (Sorry I missed that in original post). Modify database to normalize structure and the relationship will be 1-to-many.

    Other things to consider.

    Organization can have several job positions that all have the same job descriptions. Then, over time, more than one person can occupy the same job position.

    So what is purpose of this database? Just to build job description documents? Not associating descriptions with positions or people?

    It is not mandatory to build relationship links in the Relationships window for a functioning db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8
    Quote Originally Posted by Ajax View Post
    for example you said


    and previously
    this implies anything from 1 to 9 - so which is it? 9, 1? or anything in between?

    ......
    Sorry, Ajax, my bad.

    We have to have all of the fields available. If we are able to import Job Description data to all tables from a single file, I'm thinking (but don't know) that there still would be a record in all of the tables, but - except for the DSM code designating the specific Job Description - they would be blank for any EJF that Job Description didn't use. Am I making sense? I truly don't know, and I hadn't even started thinking about this question! Thank you!
    Sadhya

  12. #12
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    they would be blank for any EJF that Job Description didn't use.
    This is one of the significant differences between excel and a database (access or otherwise). In a db you do not create blank records 'just in case', only as and when required.

    Similarly, if you had a blank EJF - presumably there would not be any associated measures and examples?

  13. #13
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8
    Hello Ajax, That is absolutely correct - all of the measures and examples are tied to a header EJF. Thank you - Thank you for guiding me in thinking better. Do you happen to know if we will be able to load multiple one-to-one tables with a single Excel file? If so, I'm wondering how it would work when we get to a DSM code that has no values for an EJF. Would it just skip that table?, or would it fill in the DSM code all by itself? I don't even know if repeating the DSM code across all the tables is even the correct way to link them together! Thank you for your time and help.

  14. #14
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    yes - subject to a maximum 255 columns to be imported. It may be you will need to repeat the 'key' column in excel. You would have multiple loads, minimum one for each table, perhaps more. depends on what your spreadsheet looks like.

    Suggest upload a copy of the existing spreadsheet for two or three jobs to get a feel for what is involved. I'm guessing this will be like the 'job descriptions all fields' workbook, but with some example data.

    Suggest use colour formatting to indicate which column relates to Job/ejf/measure/example

  15. #15
    sadhya is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Dec 2015
    Posts
    8
    Thank you Ajax, I apologize for the delay in responding - I was driving long distance! I did the sample upload: multiple files to create multiple tables: all with same key, and 3 sample records broken out between them. Thank you for that, and also for the knowledge that we will have to load with multiple files using the same key each table. I will now learn about color formatting and apply. Thank you again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-02-2015, 12:19 PM
  2. Create macro to import a text file
    By asmith533 in forum Macros
    Replies: 2
    Last Post: 03-05-2015, 08:18 AM
  3. Replies: 12
    Last Post: 03-27-2014, 04:49 AM
  4. create a button for import excel file
    By tggsun in forum Forms
    Replies: 3
    Last Post: 01-17-2012, 08:40 AM
  5. Replies: 1
    Last Post: 08-13-2010, 01:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums