Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2012
    Posts
    9

    Red face database for job support agency

    Hello all!

    I am looking to build a database that can help a job support agency but I am finding the task quite difficult and would appreciate any help possible.

    The organisation is set up so that clients come to them, upon referral and the organisation helps the client to get back into employment. They help the client to get further skills, provides discrectionary funding for courses, and looks for jobs that suit the clients skills etc. Each client will be assigned a mentor who will look after them and deal with their "case" throughout their time with the organisation.

    The client will leave the organisation on occassions to complete the courses that the organisation sets them up with and will also leave on the basis of finding a job. the organisation needs to track all of the courses they undertake and the employment they undertake durign their time with the organisation.

    Here is a little bit more specific information about the course situation that needs to be stored and tracked.

    The organisation will help the client find and complete courses that will gain them the qualifications and skills needed to improve their CV. The courses will be specific to each client and will have therefore their own specific name however they must fall under 5 programme types so that the organisation understands what type of coruse they are undertaking because the organisation will provide funding for the client to take part in their course as appropriate to the programme type it falls into.

    Furthermore the organisation will also help the client by helping them find a job and therefore the organisation needs to keep track of the clients employment history and the type of employment they go into while with or leaving the organisation.

    Any help on how I could set this up? I've tried it everyday for the last 2-3 weeks but I still cant find the best way to set it up to link and store the right information efficiently.

    I have only a basic knowledge of access 2007 and could really do with help in this matter ?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by DinoBaggio View Post
    Any help on how I could set this up? I've tried it everyday for the last 2-3 weeks but I still cant find the best way to set it up
    I think you will have better chances for a meaningful answer if you post what you've got, even if it's not the best way.

    I would expect to have at least these entities:
    • Client
    • Mentor
    • ClientTraining
    • ClientSkill
    • ClientEmployment

  3. #3
    Join Date
    Feb 2012
    Posts
    9

    Question Thanks for replying

    Hi, thanks for the reply!

    Well I have made a number of different attempts at the design and at one stage I did have a design similar to that which you are suggesting above however I found it complicated to link all the tables using the ClientID as it didnt really build a client profile in the client table.

    At the minute therefore I changed the design and now have:

    Employee table
    Client table
    Calls table, (I need to track the calls made regarding each clients case)
    And then a case table.

    I decided to put alot of the information into a case table simply because it ensured everything appeared in on the screen building a profile for the case. Obviously I know this is the wrong design, but I am quite a novice at using Access and the other attempts I made at the design didnt really seem to build a case profile for the client well enough, maybe because the relationships I was definining where incorrect or possibly on the same level and made keeping track of the information difficult? Sorry I am probably confusing you, sorry.

    In past attempts I have made the above tables, but then created also a programmes table where I entered the records about the different category of programmes that clients can take part in eg. DEL programme, accredited course.. these are only category of programme types however and within each programme a client will need to undertake an individual course that fits that category, the names of these individual courses will need to be entered manually though because they are specific.

    I also created a table Into Employment, where details of the type of employment the client is going to take part in is stored, however I then needed another table to stored the employer details, and found this difficult to link.

    the Programmes table and Into Employment and Employer details tables all need to link back to relate specifically to an individual case. Any ideas on how excatly to design this would be very welcome and I would appreciate it greatly.

    Otherwise, do you feel the method in which I simply stored information about courses and employment in the one case table is ok? I have designed forms making the input very easy and breaks the design up well so that it seems that the information is spread out a bit better, but I realise, this is probably not best practice.

    something that is worth noting is that a client once they join employment will leave the organisation and there case will be closed.

    Many thanks for any help!! Please

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Well, again you didn't provide enough details about your current approach (vague explanations don't count), so it's hard to comment. Also, you didn't mention anything about cases in your original question.

    In a typical case management application (of which one example would be Curam Software, originating from Ireland by the way) the entire data model revolves around the Case entity, to which everything else is linked: employees, clients, client activities (phone calls, visits, etc.), resources (training programmes, certifications, etc), and outcomes (employment in your case). Typically resources and employees are not unique to a particular case, so there are many-to-many relationships between cases and those other entities. There may be another table to track case status changes.

  5. #5
    Join Date
    Feb 2012
    Posts
    9

    Question thanks for helping

    Hi N I.

    Thanks for the reply again and the advice.

    Ok, well at the minute I have:

    A client table with contact and personal information.

    I then have a case table that holds information about the case, similar to what you where suggesting with Curam. In the case table I have:

    CaseID
    Assignedto(EmployeeFK)
    Client(FK)
    Caseloaddate
    CourseID(FK)- I run into problems alot here because a client can do 5 courses and really I have only created a lookup table for this field. When you select this field you are prompted to select, multiple if necessary, the course types that are applicable to the client and their codes, however a client must also provide the specific name of a course within the category of course chosen here and I don't know how to set up this field?? also, maybe my lookup table is very poor practice here?
    IntoEmploymentCode- I then have this field which looks up again a table that holds the different codes that relate to different types of employment types that the agency looks to for clients eg temporary, permanent etc so that we can categorise what clients are going to do. Again problems arise here because I have no way of allowing the client to provide the clients name and contact information etc. Any ideas where a field could go here to allow this info to be input?
    callsID-i then have a calls ID that records the calls etc here and who took them from the employees table

    Employees table- clearly i have an employee table with their info too

    calls table-

    course code table-

    Employment codetable-
    I hope this makes it a little clearer. if not let me know please and I could maybe upload some of my work so far to better show my probably awful design flaws.

    Your help and ideas are much appreciated N I. I thank you very much for your help so far and your suggestion of Curam. thank you

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    You said a case could have multiple courses, so a better approach would be to define a separate table for case-course relationships:

    CaseCourses
    --------------------
    CaseID (FK)
    CourseType
    CourseName

    No?

  7. #7
    Join Date
    Feb 2012
    Posts
    9

    Question Thanks again for the help! If you could please check the below so I understand

    Many thanks N I,

    I had tried to design the database structure similar to that idea before but I just couldnt get my head around, but now I followed your advice and it seems much better.

    I now have:

    Client table

    Case table

    CaseCourse table

    Courses Table- I used this as a lookup table for the casecourse table so the user can select the course type that the client has undertaken.

    CaseEmployment table

    Employment table- use this table as lookup for the caseemployment table so the user can select the employment that the client has undertaken

    Calls table- to track calls

    This does seem to be working now thanks to your advice, however I ask another question please, now when I go to the case table, I dont really get a case profile view because I am unable to see the courses the client has complete orthe employment they have went into or the calls associated with the case. What I mean is that I can not select to view the subsheet data about these other tables when I click the small cross beside the caseID and instead I am presented with a window asking me what subsheet data I want to enter. I realise this is because the ,casecourse, caseemployment and calls tables all rely on the CaseID, and do not have their own field within the case table. I am just wondering if this is ok?

    I have designed a form that allows the user to enter in the immediate case details, (CaseId(Autonumber), caseloaddate, select the client from a lookup table that the case will relate to, notes, attachments and priority of the case). I then insert a tab control with 4 pages, each page holding a subform that allows the user to enter the data required about for the casecourse table, caseemployment table and calls table.

    Is this all ok? It just seems a little odd to me, obviously Im pretty much a beginner with datbases, that their doesnt seem to be fields in the case table that show in the case table what courses, employment and calls are associated with the case. But maybe this is ok because they are all related via the CaseID???

    So sorry for the confusing way I have likely worded this, and also for what is likely to be really amatuer questions, but I really do appreciate your help!!

  8. #8
    Join Date
    Feb 2012
    Posts
    9

    Question sorry forgot to mention also

    Sorry, on top of the above,

    I forgot to mention also, I have no primary key in the casecourse and caseemployment tables, this kind of worries me because I have thought a primary key was necessary for all tables?

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by DinoBaggio View Post
    now when I go to the case table, I dont really get a case profile view because I am unable to see the courses the client has complete orthe employment they have went into or the calls associated with the case. What I mean is that I can not select to view the subsheet data about these other tables when I click the small cross beside the caseID and instead I am presented with a window asking me what subsheet data I want to enter.
    I'm not at all familiar with Access, but I would think that, if you created a view joining all necessary tables, you should be able to present all case information on one form.


    Quote Originally Posted by DinoBaggio View Post
    Is this all ok? It just seems a little odd to me, obviously Im pretty much a beginner with datbases, that their doesnt seem to be fields in the case table that show in the case table what courses, employment and calls are associated with the case. But maybe this is ok because they are all related via the CaseID???
    If it works then it must be OK.

    Quote Originally Posted by DinoBaggio View Post
    I have no primary key in the casecourse and caseemployment tables
    You do, actually. In the relationship tables such as CaseEmployment or CaseCourse PKs are a combination of two FKs (e.g. CaseID + EmploymentID), which identify each record. Some might argue in favour of adding an artificial unique identifier as the PK, but it is rarely necessary.

  10. #10
    Join Date
    Feb 2012
    Posts
    9

    Smile Thankyou very much!!

    Thank you very much N I for all your help. I think it is working now as far as it needs to be anyway thanks to your input.

    And yes I did create a form for the case with subforms so that the the user can input all the information necessary about the case i.e. the caseemployment, casecourse etc. It shows all of the data relating to a case perfectly, I was just worried because when I open the case table in datasheet view I am unable to see all of the subdatabsheet information, but this must just be because all of the tables using the caseID as their parent table are on the same level as each other.

    Il post a little screenshot of the relationships here, just to show how I have made the relationships if you are interested.

    Thanks a million for all your help and replys N I.. Gentleman
    Attached Files Attached Files

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
  •