Hello everyone,
I am an ex access developer, but haven't done any work in the past 15+ years so am quite rusty.
Would love it if you could give me some insight as to how to proceed with my issue(s).
I've decided to get out of retirement and help a friend of mine who's in HR, and migrate her data from excel to access. The excel sheet stores mostly CV info about people, and there are around 3,000 records. She just typed in the information the way it was in the resumes.
I need to normalize that data to be able to store it in a db, but am not quite sure as to which work is better done in excel before importing, and which must be done after.
I'll list down all the columns I have in the sheet with my comments next to them.
ID : I added this one to have a record ID
Name : This field contains composite names, always starts with first name, but might have up to 5 middle/last names mostly for Spanish/Portuguese/Arabic/Asian people. The problem I am facing is that for Spanish/Portuguese, the format could be FMML (AYRTON BRENO FARTURA TIAGO), while Arabic it could be FFL(Sheikh Omar Sharif) or FLL(Hassam Abdul Rasheed). What would be the best approach into dealing with that? Use first as F and last as L, with everything in the middle as M?
Phone & Alternate Phone : Here the format varies, but shouldn't be hard to fix. Ive got 1111111,+11111111, (1)111 111 etc...
Location : No Issue
Previous Positions : These cells contain all previous positions in bullet points. I have managed to separate them using the text to column feature in excel, so now I have a sheet with ID, POS1,POS2,etc...
Interview1 Time : NI
Interview1 Date : NI
Interview1 Interviewer : NI
Vaccinated : Has the vaccine name and number of shots taken or simply a yes
Last Salary : Has some that are different currencies
Expected Salary : NI
Contracted : Has Y/N or current company name with date till end of contract.
Visa : Y/N
Applying Position : Has separate positions separated by "/" (Stage Manager / Cast Manager / Creative assistant director / Choreographer)
Feedback : NI
Rate : 1 to 5 with .25 increments
Shortlisted : Y/N
Interview2 Time : Need to be added
Interview2 Date : Need to be added
Interview2 Interviewer : NI
Manager Feedback : NI
Manager Recommended Position : has bullet points and "/" (•Cast Manager / Coordinator •Stage Manager / Coordinator)
Manager rating : Same as Rate
Shortlisted for final interview : Y/N
Past Experience : Bullet points with a list of the previous companies
I also need to add a final interview time and date
I have attached an excel sheet that has the basic table structures I am planning on creating, any input/corrections are more than welcome.
I am using office 2016, but last one I worked on was probably 2006, so have no idea about any new features.
I wish the data didn't have personal information, or else I would've attached a sample.
Thank you in advance.
I am an ex access developer, but haven't done any work in the past 15+ years so am quite rusty.
Would love it if you could give me some insight as to how to proceed with my issue(s).
I've decided to get out of retirement and help a friend of mine who's in HR, and migrate her data from excel to access. The excel sheet stores mostly CV info about people, and there are around 3,000 records. She just typed in the information the way it was in the resumes.
I need to normalize that data to be able to store it in a db, but am not quite sure as to which work is better done in excel before importing, and which must be done after.
I'll list down all the columns I have in the sheet with my comments next to them.
ID : I added this one to have a record ID
Name : This field contains composite names, always starts with first name, but might have up to 5 middle/last names mostly for Spanish/Portuguese/Arabic/Asian people. The problem I am facing is that for Spanish/Portuguese, the format could be FMML (AYRTON BRENO FARTURA TIAGO), while Arabic it could be FFL(Sheikh Omar Sharif) or FLL(Hassam Abdul Rasheed). What would be the best approach into dealing with that? Use first as F and last as L, with everything in the middle as M?
Phone & Alternate Phone : Here the format varies, but shouldn't be hard to fix. Ive got 1111111,+11111111, (1)111 111 etc...
Location : No Issue
Previous Positions : These cells contain all previous positions in bullet points. I have managed to separate them using the text to column feature in excel, so now I have a sheet with ID, POS1,POS2,etc...
Interview1 Time : NI
Interview1 Date : NI
Interview1 Interviewer : NI
Vaccinated : Has the vaccine name and number of shots taken or simply a yes
Last Salary : Has some that are different currencies
Expected Salary : NI
Contracted : Has Y/N or current company name with date till end of contract.
Visa : Y/N
Applying Position : Has separate positions separated by "/" (Stage Manager / Cast Manager / Creative assistant director / Choreographer)
Feedback : NI
Rate : 1 to 5 with .25 increments
Shortlisted : Y/N
Interview2 Time : Need to be added
Interview2 Date : Need to be added
Interview2 Interviewer : NI
Manager Feedback : NI
Manager Recommended Position : has bullet points and "/" (•Cast Manager / Coordinator •Stage Manager / Coordinator)
Manager rating : Same as Rate
Shortlisted for final interview : Y/N
Past Experience : Bullet points with a list of the previous companies
I also need to add a final interview time and date
I have attached an excel sheet that has the basic table structures I am planning on creating, any input/corrections are more than welcome.
I am using office 2016, but last one I worked on was probably 2006, so have no idea about any new features.
I wish the data didn't have personal information, or else I would've attached a sample.
Thank you in advance.