Tables relationships help (1 Viewer)

iroman

New member
Local time
Today, 07:49
Joined
Dec 19, 2017
Messages
5
Hello,

I am trying to make a database for CV's.I have made a schema and the tables and they look like in the attachment.

I made a Entry form to enter the data for new candidates.
The problem I am having is for the Skills and Language parts.
I have made some Intermediate tables for Many to Many relationships between the Candidate and the Skill and another between Skill and Level.
When I am trying to make a query and a sub form for the Competences Tab of the entry form I get the "the object doesn't contain the automation object".

Can somebody please help me? AT least tell me the relationships are wrong or something.

I'm quite new to this.

Thanks!
 

Attachments

  • relationship.PNG
    relationship.PNG
    50.2 KB · Views: 128

Ranman256

Well-known member
Local time
Today, 01:49
Joined
Apr 9, 2015
Messages
4,339
skilllevel.png

I dont see the need for these extra tables in the relation.
Skill level should be 1:1 with tSkills.
Adding in the Lookup tables: t.SkillLevel and t.LanguageLevel are not needed. They are lookup tables, Not parent/child.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:49
Joined
May 7, 2009
Messages
19,169
bring your Relationship schema.
right click on each connecting.
then choose Edit Relationship.
choose Join Type..
always choose option 2, which
is a Left Join.
if you will use option 1, then
if there is no corresponding
record on the Right side of the
connecting line, you will not be
able to add new record to your Form.
 

iroman

New member
Local time
Today, 07:49
Joined
Dec 19, 2017
Messages
5
I was thinking that for example Autocad has levels beginner intermediate and Advanced and so other software. And I thought it was a many to many relationship.
Now should I delete the Intermediate table Skill_LevelSkill and Language_LevelLanguage? Make a 1 to many relationship between t.Skills and t.SkillsLevel ?
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:49
Joined
Sep 12, 2017
Messages
2,111
What you are really looking for is a "Skills" lookup table. This would be broken down as
SkillID (Primary, Auto number)
SkillType (Things like Language, Degree, Certificate, System, what have you)
SkillName (Under Language you could have Deutch, Tagalog, something else)
SkillLevel (OPTIONAL)
SkillSort (OPTIONAL, used when you want to display in other than alpha order)

This will allow you to remove several tables. You would then link from Candidate to Skill. In the junction table you can also hold certificate number, institution, date awarded, and date expires.

From a logical standpoint this also reduces the work you, as the programmer, will need to do when looking for multiple criteria. Instead of checking multiple tables (and trying to join them together) you are looking for multiple matches in the same field of the same table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:49
Joined
Feb 19, 2002
Messages
42,981
If skill levels can be generic. 1,2,3 or Low, medium, high, then eliminate the the four extra level tables and simply add a SkilLevel column in the Skills and Language tables.

I probably wouldn't use a separate table for language since language is just another skill.

If you decide you want to keep the excess level tables, then the the schema needs to be changed. You need a single PK for Skill_LevelSkill and Language_LevelLanguage and that autonumber is what the Skills and Language junction tables need to join to because you want to pick up the intersection of skill and level.

What does certificate mean in the Skills and Language tables? Do you have that qualification ONLY if you have the certivicate? Certificate probably belongs in the Skills and Language junction tables for the candidate rather than in the skills and language table.

You should also clean up your column names and get rid of the embedded spaces and especially the special characters. Personally, I prefer CamelCase. I find the underscores annoying to type and jarring to look at so I only use underscores for emphasis.

Application has a repeating group. If you want to report on interviews, make a proper 1-m relationship.
 

iroman

New member
Local time
Today, 07:49
Joined
Dec 19, 2017
Messages
5
Thank you Mark for the feedback. I am trying to make a Search form and find it difficult to join the tables and so on...so I will try to remake the relationship between Candidate and Skills (probably call it Competences) and use the columns you mention.
I would make 1:M relation from Candidate to Competences, right?
 

iroman

New member
Local time
Today, 07:49
Joined
Dec 19, 2017
Messages
5
Thank you Pat Hartman. I really appreciate your help.
I will clean the column names, thanks for that.
Can you please tell me what is the repeating group in Application?
I attached the relationship again because it had more column and they couldn't be seen.
 

Attachments

  • Application.PNG
    Application.PNG
    27.1 KB · Views: 76

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:49
Joined
Feb 19, 2002
Messages
42,981
You have two sets of interview data. Once you have more than one, you have a 1-m relationship and the "m" belongs in a separate table. If tests can be taken more than once , they also belong in a separate table.

I would make 1:M relation from Candidate to Competences, right?
No. Candidates to competencies is m-m as you have it. It is only the levels that are causing confusion.
 

Users who are viewing this thread

Top Bottom