Hi there, after posting a forum yesterday on the sensibilities behind MVF fields - I have come to the realisation that I need to redesign an inherited db in order to support SQL hosting down the line. The dB is going to attempt to map skills and qualifications held by my company's staff. Here is how the current db is designed. Supporting images can be found attached.
- 5/7_SAPPersonnelData is an excel sheet, linked to the current db.
- tbl_Personnel is populated via an append query (qry_personnel_RefreshData) directly from linked table 5/7_SAPPersonnelData on open via macro control, with this data being scrubbed on close. This ensures that this personnel data is up -to-date, as it often is changed and updated on the excel sheet.
- There are five other supporting tables in the dB. tbl_Civilian Skills and Qualifications acts as the primary table, with tbl_Qualifications, tbl_registrations, tbl_languages, and tbl_selfclaimed all providing refined data. I have redrawn my dB in this manner as I had previously utilised MVF, and realised that these are not compatible with SQL hosting later down the track.
- tbl_Civilian Skills and Qualifications relates to the other supporting tables through relationships, via Pk and FK. For example, it relates to tbl_personnel via the unique ID of a employee#. I cannot use the auto-number as this variable, as on the append (delete and append) this number changes. The supporting tables of qualifications, registrations, languages, and selfclaimed are related to tbl_CivilianSkills also through Pk and FK. Each supporting table holds the refined data behind each category. IE. tbl_languages has two columns, an autonumber ID and a list of 150 top spoken languages.
- This data is assigned to a contact in the tbl_CivilianSkillsandQualifications via number fields (see images).
- So far, so good right? I did some testing and found that I could assign skills/languages/qualifications to staff via this emthod (adding a number to their record in tbl_CivilianSkills, and would be able to design a form that could run this for me, utilising word selections, rather than numerical).
- However, the issue i have is that if I look for results on who holds what skills, it only shows me a record if all fields have a data entry (IE. a number in each field of tbl_CivilianSkillsandQualifications). This isn't much use as I would like to pull reports up on things such as "find me a record that matches the following criteria - (gender = M (from tbl_personnel) + french (from tbl_languages) + plumber (from tbl_registartions).
- Is this achievable? Or have I mis-designed my relationships? I have attempted to attach as many supporting images as possible - and am open to all, and any recommendations.