17099Good Morning

I was wondering if someone can please assist/advice. I am currently a bit stuck and feeling like I am walking into a dead end the whole time.
I am currently creating a DB with the purpose of Capturing Potential Candidates provide them with an ID. Fill in their basic details Name Surname, submit to a report, which I will then export, email to them, they will fill in the outstanding information requested, send it back and will import it back into the DB with my various queries to run searches on candidates for placements.

My only problem is that I have quite a lot of fields...

So I Decided to Create 4 Tables
In each table I am using the same ID for a person so should be a 1 to 1 Relationship.



1 General Information
2 Info Relating to Skill
3 Work History
4 Qualifications.


I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed

But then if I decide to create a query so I can run some of the details i get errors because there are to many fields. Even if i run one on the other. My last one will still have to many fields.
Basically I am hoping someone can just assist in what would be the best way for me to lay out my tables. And what would be the best to link these tables.
Is it better to have all the records horizontally, or as a one to many with duplicates.

Please find attached a copy of the tables. I would really appreciate any advice
Regards

I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed



This is the problem.

If you have 255 fields in a table then you are definately doing things wrong. You are trying to recreate a spreadsheet in Access which is incorrect.

You should read up about "normalisation"

For example - if you wanted to record peoples eye colour - you don't create a field for each colour (brown,blue,grey, green) you create a field called "colour" and then enter the colour in that field. So you have one field instead of four.
Access read tables vertically so each record should be on a new line - not horizontally like a spreadsheet.

The tables are then linked via the relationships so everything matches to the correct person

Col

Basically you need to understand Normalisation.

but some advice.

tbl for personal information. Should contain Personal information only. Name, Address gender DOB type information. If they had say previous nationalities then this data belongs in a separate table. They may have had several previous nationalities.

Consider also Languages. You list 20. Which field are you going to search looking for a specific language. Somebody may have only 1 so you have a lot of Null values.

Think about a SupplementarY Data table with colums

Candidate_ID Data_Type Data_Value Skill_Level
1 Language French Fluent
1 Language German Social
1 Previous Nationality Greek
more candidates

So you have a 1 to many relationship and only 4 fields. If you want to search a particular Data Type you can.

Very compact

len

Thank you very much for the advice.
I have started creating additional tables and already starting to make allot more progress.

Thank you once again
Regards
Natural