Access Database for CV's - Help!

Mighty_Mouse

Hey,

Hope someone here has a few pointers for me. I need to design a database that will contain 700 C.V's or so.

I was hoping to use Access to do it as it's easier to use but I've a little Oracle experience also.

How do I set up the fields if the CV's are not standardised? I can fields for name , age, address, etc education , languages spoken etc

But what about work experience etc. Is there any way I can just copy and paste this whole section into a text field and still be able to search it within the database.

It will take forever to enter a C.V into the database without pasting the whole work experience into some searchable text field in the database.

Anyone got any ideas on how I should go about this. I used to know a little VB, access, SQL but Im not sure how good I am at it - hence the access route

uberwolf

do you expect all the CV's to be for similar positions and therefore have similar experience. Do you want to be able to go, for e.g, return all with VB experience with just names, or return all cvs for a keyword search?

Cos windows provides text searching for files stored in it. Not really scalable but if ur talking about a one time problem here...

Mighty_Mouse

We currently use windows file "text" search but its not very effiecient


I ll give some more detail.Its CVs of "experts" and consultants we lease work to. When searching to find a suitable expert for a certain project its a very inefficient process at the moment.

I need to be able to search by

1. landguage spoken and to which level -eg russian fluent
2. I need to be able to search by regions that people have work in the world - e.g eastern europe, north america, mediterraen, africa etc

3. years experience - ie 1-5, 5-10, 10- 15 etc
4. education - degree, masters, phd etc level
5. area eduction - social policy , engineering etc

All the above sort of stuff which I can put in manuel,

But importantly

6. Area of expertise/experience -

SME development, srtructural funds/ economic development/ financial etc

this is really work experience but would be very difficult break it down accordingly because no real hard categories.

I hoping to devise a database - which is could search field 1-5 above, with a random text search "SME development" in field 6.

Is this possible in access

clearz

The proper way to go about this would be to use a relational database with a different table containing all the work expierence data fields such as startDate, finnishDate, company and description and a foreign key field with the id of the cv owner. This would allow for more complex searches.

blorg

There is no problem searching memo fields for key phrases. You just use

WHERE column LIKE '*phrase*'

in your query (for Access, most DBs, including Oracle, use % instead of *) Older versions of Access *may* have had problems with LIKE on memo fields, but this works fine in Office XP. (If you are using the graphical interface, just put LIKE '*phrase*' in the 'Criteria' section.)

Clearz is right about his suggestion being the 'proper' way to do it, but I understand that this would impede you in the data entry. Far easier just to cut and paste, and that might be sufficient for your needs. Let me know if you need any more SQL advice.

Karoma

other than a performance hit. but hey, if it works..

Mighty_Mouse

Hey,

Thanks a million for all the help.

My problem to date has been how to break down "experience" . I could do this by asking every person to submit a CV in a structured template form and type in all into a website page linked to a d base.

Apart from being a little out of my reach!! I already have 700 cvs which would have to be "processed" into different categories.

i would also have to try and describe every experts experience or area of expertise in standardised form. Ie agricultural development - , sme development,

but this loses a lot in the description of a presons job title and if i was to list them all it would take forever. also experts are experts in various different job categories..

So hence the idea to copy and paste all experience and search this text field with a few key words such as - agriculture, sme etc

this search combined with the standard fields of languages spoken, regions worked, age, experience , nationailty, availablitiy for long term work etc should return a very specific and limited number of experts

Any further thoughts. are these field "combinable"?

Can i set up and access database which allows me to input and search by 6 or 7 standardised fields with a keyword search in a large text field.

is there a limit on the size of a memo field, ie if every memo entry is three pages long, will it take too long to complete a search of 800 cvs?

Am I going down the right path.?

Can this be done with limited access, sql, oracle , database theory background? basically a few projects in college experience?

If I need to buy a book have ye any sugestions?

thanks a million for all help

Mighty_Mouse

also to input 800 cvs by startDate, finishDate, descriptoin in one field would take a century.

But is this effort worth it ?

regards

Hobbes

Originally posted by Mighty_Mouse
also to input 800 cvs by startDate, finishDate, descriptoin in one field would take a century.

But is this effort worth it ?

regards

You could just put the job out to a data entry company 800 CVs is a tiny job.

RicardoSmith

If I was doing it I write an app probably in VB to parse all the CVs and import it into a database, probably access or mySql. You could add fields in the database over time. You could flag all the ones that have fields missing based on your criteria. This would give you a much shorter list of CV for data entry.

You could also email all the CV authors with a revised CV template to fill in, asking them for an update of their skillset, experience and to confirm their details etc.

Between both processes you would populate the database fairly quickly.

blorg

I would think that you would be fine doing the way you suggest, in Access. Computers are fast and cheap these days and so I don't think the performance should be a problem if you are doing it on a 'modern' computer. We are talking about a system that is going to be used by one person, after all, not something that is going to be hammered with multiple queries from different people. If you get a good amount of memory the whole database could fit in RAM - 800 records is just not that much.

While producing a fully normalised database with seperate "startDate, finishDate, description," etc. might seem ideal, I don't see any point to this as you won't want to extract this information (e.g. while you may be interested in someone with 5 years VB experience, you are (presumably) not specifically interested in 'all people who worked in Hewlett Packard in 1995'. If possible it would be good to standardise on fields for expertise, but this may be difficult given that you may have one person use the term 'SME development' while another uses 'Small to Medium Enterprise development', etc. You can of course search for both (WHERE memofield LIKE '*SME*' OR memofield LIKE '*Small to Medium Enterprise*').

It is easy to "search by 6 or 7 standardised fields with a keyword search in a large text field". You just use WHERE field1 = 'contents2' AND field2 = 'contents2' AND memofield LIKE '*searchtext*'. For basic SQL, I'd recommend Sams Teach Yourself SQL in 10 Minutes by Ben Forta. It's a good short book that has all the basics. 'Database Design for Mere Mortals' by Michael J. Hernandez is a very good book explaining relational database design to newcomers, while there is a companion 'SQL Queries for Mere Mortals' which is also good.

If you need an "expert" consultant to do the DB design for you, just let me know ;-)

blorg

Another book that I would very much recommend is Chris Fehily's Visual Quickstart Guide: SQL. I have it in work and couldn't remember the actual name beyond 'sqarish-book with a rabbit on the cover'. It's very, very good, both to learn SQL and as a reference for the basics. It's cross-platform, with examples given for Access, SQL Server, Oracle and PostgreSQL. He'll use ANSI standard SQL where possible, pointing out when this has to be diverged from for one of these databases.

Note that these SQL books cover SQL, not the theory of relational database design - best book for that is Hernandez's Database Design for Mere Mortals, as referenced above.

clearz

Originally posted by RicardoSmith
If I was doing it I write an app probably in VB to parse all the CVs and import it into a database, probably access or mySql. You could add fields in the database over time. You could flag all the ones that have fields missing based on your criteria. This would give you a much shorter list of CV for data entry.

You could also email all the CV authors with a revised CV template to fill in, asking them for an update of their skillset, experience and to confirm their details etc.

Between both processes you would populate the database fairly quickly.

Parsing it the way to go. The only problem there must be a pattern in the layout of the CV's otherwise the hastle can be greater than the time saved.

RicardoSmith

Originally posted by clearz
Parsing it the way to go. The only problem there must be a pattern in the layout of the CV's otherwise the hastle can be greater than the time saved.

I'd say if you did a word count of all the most common words in a CV and then used that as a basis for a keyword list, you'd be 90% of the way there. Do a test on a sample of about 10 disimilar CV's and you'd be amazed how common they are. I used a similar technique for a email list cleaner I wrote a while back.

Mighty_Mouse if you need a hand just drop us a PM sounds like an interesting project. I might be doing some thing similar for some other data I'm working on.

The Corinthian

An important point that appears to be left out is what this database for? Sitting on a server and being accessed via a HTML interface? Standalone on a local PC? Shared throughout an office over a LAN? What platform? This should realistically influence the database you use over all.

Originally posted by Mighty_Mouse
also to input 800 cvs by startDate, finishDate, descriptoin in one field would take a century.

But is this effort worth it ?

Often efforts like that are not worth it in the short term. It’s a lot faster, cheaper and a Hell of a lot less hassle to do it as a straightforward hack. Chances are, however, that if this is data that you’ll be keeping and building up from, you’ll end up paying for your shortcut in the long run.

Having said that, if the long run ain’t your problem and your employers don’t want to pay the money necessary to get it done properly, then run with it.

Mighty_Mouse

"employers don’t want to pay the money necessary to get it done properly, then run with it"


These are my sentiments at the moment exactly. If they want something fancy I'll organise it when the time comes.

I'm aiming for a short-term solution here. I'm not even a straight/pure IT graduate. . . . . . . and I have a heap of other non-IT related consulting projects to get through.

If I get this up and running with maby 50 cvs in it. It will completely remove the pressure.

Thanks again for all yer help. By the sounds of thing this is completly "Doable"

---

2 definitely (3 nice) computers need to access this database. I was thinking I would just burn it to a RW disc and give ppl who want it a copy.??????????//

My computer will act as the main database / a stand alone pc.

----

RicardoSmith

Is there no network?

Capt'n Midnight

If it's CV's there may be data protection act stuff involved - keep track of the CD ! - and wireless would be a no-no for similar reasons.

AFAIK you can attach a 64K file in to a record in SQL - maybe you could do the same in Access (BLOAT) but you'd have the whole lot in one place - anyone know of a handy way of putting hyper links into access. So you could point it at a folder full of CV's and it would fill a basic record and then you could click through to the CV.

eh. what is the best way of putting a clickable shortcut to a file in Access ?