Results 1 to 13 of 13
  1. #1
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9

    Need Help with Separating Data From Excel File

    Hey guys, I really hope you guys can help me with this!!! I work at a college & I have an excel file for my contracts that we do to pay the professors for the classes they teach in the summer. The file is a list of the contracts created & I import this file into my access database. A contract can have up to 10 courses on it. So the main identifier in the excel file is the WFID which is the contract ID & 1 row is created for that WFID. However, the CRN is the identifying factor for me, so I need an individual row for each CRN, rather than an individual row for a WFID # with multiple CRNs. I hope this makes sense...

    Does anyone know how I could separate the data in excel or have access do it for me? Right now I have to do alot of sorting, copying, pasting & deleting in the excel file in order to get what I want, which takes quite a bit of time since this file typically has over 500 rows of data... So I very much appreciate any help you guys can give!!

    Oh &
    I have attached the excel file for you to get an idea of what I need. It has 2 sheets in it. The 1st sheet is how the data comes originally & the other sheet is of the changes I have to make in order to have it work in my database. I can attach the database if necessary, just let me know if you need it.

    Thanks again guys!!

    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    attach (link) the excel file as an external table,
    then run 2 append queries (to import):

    Q1 to import the contracts fields to the tContracts table
    Q2 to impor the classes to the sub child tClasses table. (child of tContracts)

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.......

    It would really help to see your database.

    Code can be written for either Access or Excel.
    How often would you have to do this transformation?
    If it is done more than once, is the previous data deleted or do you append the new data to the old?

    BTW, in the sheet "Original Data", at AP1, there is an empty cell, but in sheet "Changes Needed", "O1" has "dean_admin_summer_comments".
    Should "Original Data.AP1" have "dean_admin_summer_comments"?

  4. #4
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9
    please disregard the WFID changes file... Here are copies of all the files that I use..


    Original WWFID.xlsx is how i get the data originally
    upWWFID.xlsx is how i have to change the data to paste into access
    SSFA is the access database

    I paste the upWWFID.xslx data into the upWWFID table... then I run the following queries to update the data in *Classes with the data from the upWWFID table

    Class7-upWWFID1
    Class7-upWWFID2
    Class7-upWWFID3
    Class7-upWWFID4

    I have to run that query 1-4 because I have multiple instructors teaching the class. the CRN is the primary in *Classes, which is the course number...

    I know this is a mess, but maybe you guys can help me reformat my database... I am pretty good with access in the sense that I have learned on my own & have created some pretty complex queries, but I totally dont have a good grasp on formatting my tables & things like that. Basically, I've messed around enough to understand most of the help you can offer, but not good enough to have figured this out on my own...

    I hope this is enough to help yall understand what I'm dealing with




    Quote Originally Posted by ssanfu View Post
    Welcome to the forum.......

    It would really help to see your database.

    Code can be written for either Access or Excel.
    How often would you have to do this transformation?
    If it is done more than once, is the previous data deleted or do you append the new data to the old?

    BTW, in the sheet "Original Data", at AP1, there is an empty cell, but in sheet "Changes Needed", "O1" has "dean_admin_summer_comments".
    Should "Original Data.AP1" have "dean_admin_summer_comments"?
    Attached Files Attached Files

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Questions:
    1) How often would you have to do this transformation? (second time asking)
    2) If it is done more than once, is the previous data deleted or do you append the new data to the old? (second time asking)
    3) In table Classes, what is the meaning of the prefix "WF" in the field names?

    4) In Access table names, what is the meaning of the prefix "cl"?
    5) In Access table names, what is the meaning of the prefix "up"?

    It looks like the table "Classes" should be broken up into several/many tables due to repeating fields.
    Example of repeating fields would be
    InstName1
    InstName2
    Inst1FACID
    Inst2FACID
    Inst1Type
    Inst2Type
    Inst175%
    Inst275%
    (shouldn't have the "%" in the field name.

    6) What is the significance of the 175% and 275%?


    In Access, it looks like the main (only?) table that has data from the spreadsheet data (table xxxOriginal WWFID/upWWFID) is the table Classes.
    Note: I removed the "*" from the Classes table. Should only use letters and numbers (exception is the underscore) in object names. And never start an object names with a number.

  6. #6
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9
    I apologize for not answering those questions..

    1. I have to update that data numerous times during our summer session, probably around 20 times from May until September.

    2. Yes I have to delete data that I previously uploaded... The process I follow is I download the main data file, delete any entries I had uploaded previously, edit the new entries since my last upload so there are only 1 crn per line, paste into my access table, run the queries to update the Classes table.

    3. WFID stands for WorkFlow ID... Workflow is the program our college uses to process our electronic contracts to pay professors for classes they teach during the summer term. Basically their salary is a 9 month salary for the Fall & Spring terms. The summer term is considered extra so we pay them with contracts for each class. Now that we went to electronic forms multiple classes can be put on 1 contract as long as it is in the same summer term (Maymester, Summer Eve, Summer I or Summer II)..

    4. the cl tables are tables that hold information that applies to all courses based on some information... for instance in clCost the identifyier is U or G.. Undergrad or Grad. During another process when I upload a class listing, it refers to that table & if a class has a U, it inserts the cost into the Class table with the append process. If it is G, it inserts the Graduate cost.

    5a. the up tables are tables that I have to paste data in and run an update query on... I have other tables & queries that I didnt include in this file because it would have really confused people... but here is how it goes.. I have 2 main tables, Classes & Students... Classes holds all the class information including instructor, enrollment numbers, costs, and any other info about the class. The students table is a table that I upload the Class Roll to.. The student table is needed because it has all the information about the student enrolled in a class, including their residency (R= Resident, NR = Non Resident, the level they are at Freshman, Sophomore, Junior, Senior), whether or not they are an actual Senior (over 65) and not actually enrolled for a degree but taking a class on the Senior Rate which is only $25).... Etc... Anyways, In one of my crosstab queries between the students & classes table that creates a table that is used to update the classes table for the R, NR, SR columns & the refund columns as well (R100, R90, R50, R25, R0 & NR100, NR90, NR50, NR25, NR0) coulmns which is whether withdrew before certain dates which determines their refund amount...

    5b. I haven't been able to figure out how to separate the data in my Classes table into different tables yet because my boss likes to come ask me for Class information on the spot, therefore it has just been easier to have all class data appended to that one table Classes. That way I can just pull it up & tell him at any moment.

    6. the 175% stands for 7.5% of Instructor 1's 9mth salary... that is what we pay them for a class that meets enrollment requirements. 275% is the 7.5% of Instructor 2's 9mth salary. Yes, more than 1 instructor can be teaching the same course. In fact, on some of the Travel courses, there can be 4 instructors... all with different salaries, payments & contracts..

    7. I actually have 2 main tables Classes & Students... I put the * before so it would stay at the top of the table list since they are the main tables... I knew I should really use symbols, but I did it anyways & just kept it since it hasn't seemed to have an affect... But I know it probably does & I just don't know how. It's fine that you changed it...


    8 Basically, there is alot you are not seeing & it is all very confusing, but I didnt think you needed to see all that stuff since what I do with this data is not affiliated with any of that stuff. However, I would be more than happy to share a copy of the WHOLE database with all the tables, queries & reports.... I would love for someone to help me normalize it & fix the mess I have. I know I am not doing it the best way, but I have been doing this on my own with no real training in access & while I think I've learned alot, things have gotten to a level that I am just not able to do on my own. I need someone to sit down with me but I don't know anyone that does access... literally, no one...

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    That helped my understanding.

    There are a few issues that should be fixed (if possible). There are 2 reserved words (that I noticed), "Level" and "Index", that should not be used as field names.
    And "E Class" has a space - better would be "E_Class".


    You should learn about Normalization. Here are some links. (I have more if/when you want them)

    What is Normalization?
    =======================
    What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
    What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
    What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
    What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
    What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html



    In the Excel file are a couple attempts at normalizing your Class table.
    On Sheet3, under the green line, I broke out fields as well as I could; i don't know what tables some of the field might end up.

    Sheets 2 is another attempt to separate fields into table.

    Sheet 1 I used to compare worksheets and the Class table.


    In the dB, I added code to separate CRN numbers as you asked. Normalizing the dB will take a lot more work.

    So I appended the data from the workbook "Original WWFID.xlsx". (This could be coded to select the path & workbook, import the data, then parse the records.)
    The code in the module "modParseRecords" first deletes all records in table "upWWFID". Then it parses the records from "xxxOriginalWWFID" into table "upWWFID".
    Because some of the date fields are NULL, I force the NULL date fields to be "1/1/1900". After the records are parsed, there is code to delete all of the 1/1/1900 dates.


    So
    Step 1 is import the spreadsheet "Original WWFID.xlsx" into the table "xxxOriginalWWFID".
    Step 2 is click on the button "Parse".
    Then open the table "upWWFID"......


    Hopefully this will make your life a little easier..........
    Attached Files Attached Files

  8. #8
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9
    Steve (ssanfu)... i hope you had a great Christmas & i apologize that I am just now getting back to this post. I had alot of holiday chaos going on & havent had time to sit down at a computer, much less be in the mindset to work with queries and stuff.

    However, all my holiday visitors are now gone & im off work & have the house to myself every day until Jan 2nd, so I'll be working on this every day. Gonna sit down today & look over everything you said in your last reply, the files youve shared & read over the links on normalization. Ill come back if I have any questions.. wish me luck!! Lol

  9. #9
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9
    Ok so I got a chance to try out your creation & it worked when I only pasted about 50 rows of my excel file, but not the whole thing, which is 504 rows... i think some of the data in some of the rows must be off.. i will figure that out in a few days..

    Right now I think I am gping to focus on your advice about normalizing the database. Ive really wanted to do that for a long time now & since I am off work until the 2nd, I have plenty of free time to really sit down and figure something out... Im looking at all the links you sent me, that Roger guy really has alot of good advice. I printed out his normalizing pages and Im gonna try and set things up his way... It may take a few days for me to get a hang of it & have some new tables made, but once I do, can I share it with you & see what you think Im doing right, or wrong? I just want to get it right ya know..

    Thanks again for all your help!!!

  10. #10
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9
    Oh and I tried to link the excel file too, hoping it would work better than pasting, but I couldnt get the parse form to work with the linked table. Not sure I did it right..

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the code, I added functions to change some NULL date fields to "1/1/1900" because you cannot append a NULL to a date field. The code later on updates the "1/1/1900" to NULL. Without seeing the full Excel file, I don't know why it is erring.

    I modified the dB - I added a file picked (select the Excel file) and linked to the Excel file instead of importing it.
    I used the Excel file you provided.
    So now you click the button, select the Excel file, the data gets parsed and done!
    You MUST pick an Excel file that has the correct format (column names). I did not add checks the ensure the column names are in the correct order and exist.
    Attached Files Attached Files

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, last change.

    I didn't like the previous parse code, so I re-wrote it. Should be a little faster - but more code.

    See if this version parses all 504 lines in the Excel file.
    Attached Files Attached Files

  13. #13
    cew75 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    9
    Sorry I have been away for a bit!! I had been using my Christmas break from work to work on work, lol, but I have been swamped with other stuff since I got back. I will look at everything you did for me this weekend!!

    Thanks so much for all your help!!


    Quote Originally Posted by ssanfu View Post
    OK, last change.

    I didn't like the previous parse code, so I re-wrote it. Should be a little faster - but more code.

    See if this version parses all 504 lines in the Excel file.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-31-2017, 12:19 PM
  2. Replies: 5
    Last Post: 05-25-2016, 12:43 PM
  3. Access VBA Code for separating data Please
    By rose.zhu295@gmail.com in forum Access
    Replies: 1
    Last Post: 09-03-2015, 01:16 AM
  4. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  5. Separating data into 2-fields?
    By djclntn in forum Queries
    Replies: 2
    Last Post: 11-01-2012, 10:04 AM

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