Results 1 to 11 of 11
  1. #1
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47

    Redesigning dB as SQL compatible - how best to relate my various data tables? *Incl. Current db Dsgn

    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.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No images attached. You can do up to five. Or zip them and upload that instead.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Archive.zip

    Apologies, had to change devices in order to upload the images. PS. this post is a follow on from your recommendation to me yesterday, on another forum. So thanks for already steering me in the right direction!

  4. #4
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Update: I have reconfigured the relationships, and now tbl_personnel acts as the main table, and the other feed back to it via the SAP#_ID PK/FK. Attached is a new image of my relationship configuration.

    Click image for larger version. 

Name:	reconfigured_tbl_relationship.jpg 
Views:	47 
Size:	88.0 KB 
ID:	34649

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Boost View Post

    • 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.
    • 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.
    Quote Originally Posted by Boost View Post
    Update: I have reconfigured the relationships, and now tbl_personnel acts as the main table, and the other feed back to it via the SAP#_ID PK/FK.
    It looks like when updating the personell table, you delete all records from table Personell, and append them anew from table 5/7_SAPPersonnelData - hence all employees getting a new personell_ID. (Depending on number of employees, and on frequency the Pesronell table is updated, this may even cause for personell_ID the limit for integer values to be reached somedays!) I would use 3 queries instead! And a date field e.g. DateOfLeave in Personell table, which by deafault is empty (the employee is currently employed). In your Excel table must be some identificator field (like personell_SAP) for employee.
    1. The query which enters the current date into field DateOfLeave in table Personell for all employees, not found in table 5/7_SAPPersonnelData and having this field empty (marking that the employee is not working from this date anymore, but allowing to have his/her employment info to be available for reports about past periods);
    2. The query which updates all fields in table Personell (except personell_ID and personell_SAP) where DateOfLeave is empty with data from table 5/7_SAPPersonnelData;
    3. The query, which adds into table Personell from table 5/7_SAPPersonnelData all employees, for which Personell_SAP doesn't exist in table Personell.

    Now you can use personell_ID as FK in any tables.
    An additional bonus - when in future is decided to change the identificator for employees (personell_SAP) for some reason (such things happen for weirdest reasons) , then you have to update it only in one table in your database (in Personell).

    Quote Originally Posted by Boost View Post
    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.
    This is a weirdest part of your database! Looking at your relationships image from last post, you can have several records from table [Civilian Skills and Qualifications] linked to employee. For every record in table [Civilian Skills and Qualifications], you can register 4 different qualifications - one from every secondary table. Or you can have a row for every separate qualification, leaving other 3 fields empty. And whenever employee has several qualifications of same type (e.g. knows 3 languages), it needs separate records for every such qualification. It is a very bad practice to allow entering same info in two cardinally different ways!
    Btw, looking at your image, I can assume that every qualification in those 4 tables is determined with 2 fields - an ID, and a text field. So why not to have a single qualifications table instead, with additional field for qualification group (qualification, registration, self-claimed, language). E.g. Qalifications, QualificationID, QualificationGroup, Qualification. Then rename your table [Civilian Skills and Qualifications] to EmployeeQualifications, and change it's structure to EmployeeQualifications: EmployeeQualificationID, personell_ID, QualificationID. In form where you display personell information, you'll have a linked continuous subform where all qualifications with qualification group are displayed. Or you have a tab control with a tab for every qualification group, and 4 subforms on those tabs.

    Additional remark. Don't use spaces and any special characters except underscore ("#", "/") in field or table names. It may cause at least difficulties, but sometimes problems too, when you are writing VBA code or creating queries (or you want to migrate your database) later.

  6. #6
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Goodmorning from the Southern Hemisphere, apologies for the delayed response. My job took me off the grid for a week or so. But back in today and will attempt to re-route my tables at your suggestions. Will provide an update later today letting you know how it's all going. How attainable is it to construct a form that can input data into both tables?

    EDIT : Reply to

    ArviLaanemets
    ,
    So why not to have a single qualifications table instead, with additional field for qualification group (qualification, registration, self-claimed, language).
    I'd love to do that, however my issue is that I'm trying to map out all these tables in SQL so that eventually the database can be hosted by SQL further down the track. I don't think I can have an all in one table, as it would require Multi-Value-Fields? (Please advise if there is a work around!) IE. Under languages I want to set the options to choose from, and I also want to set the choices behind the registrations column too IE. nurse, doctor, lawyer etc.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Boost View Post
    I'd love to do that, however my issue is that I'm trying to map out all these tables in SQL so that eventually the database can be hosted by SQL further down the track.
    I myself use only MS SQL Server as backend database for some years. No problems with it!

    Quote Originally Posted by Boost View Post
    I don't think I can have an all in one table, as it would require Multi-Value-Fields? (Please advise if there is a work around!) IE. Under languages I want to set the options to choose from, and I also want to set the choices behind the registrations column too IE. nurse, doctor, lawyer etc.
    So for qualifications you need
    tblQalifications: QualificationID, QualificationGroup, QualificationText, QualificationOptionID (QualificationOptionID being an integer value, which can be also empty. Later in SQL it will be SmallInt, which matches with Integer in Asscess)
    and
    tblQualificationOptions: QualificationOptionID, [QualificationID or QualificationGroup], QualificationOptionText. (It depends on your data structure, how you group qualification options, by QualificationID or by QualificationGroup - from your post QualificationGroup looks the right one.)

    No need for multivalue fields.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree that there is no need for MVFs. Furthermore these are not supported in SQL Server so I recommend you do NOT use them.
    Similarly, for attachment fields. If for any reason you are tempted to use them....DON'T
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Morning, thanks very much for both of your feedback. I have completed the following.

    - Constructed a total of two tables. One for personnel data, and the other for the Civilian Skills and Qualifications.

    - Constructed a query combining both the personnel data and the civilian skills and qualifications data, using the concatenate method of merging like fields (IE. merging and filing language choice, with language proficiency level).

    - I am now in the process of constructing a form by which I can 'smart search' my results (IE. search for a variable from the personnel data table, and a variable from the civilian skills and qualifications table (age + language & proficiency).

    - To achieve this I am following this tutorial (the closest thing to what I want to achieve). https://www.youtube.com/watch?v=3SYXsdj_1Og&t=264s

    - It is somewhat coming together, but who knows at this stage. Any recommendations on how to best achieve this - or an alternative method of searching and narrowing down my results?

    I have attached some supportive images of my db so far - looking at it this morning, I think I will need to add other tables to split up the Civilian Skills and Qualifications tbl, especially where there is a large list of the civilian skills from what you can choose from.

    Cheers

    Boost

  10. #10
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    Archive.zip

    Here is said attachment.

    Cheers

  11. #11
    Boost is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2018
    Posts
    47
    I failed to add something key to the above breakdown of my current progress...

    Inside tbl_CivilianSkillsandQualifications there are a list of variables (Administration ---> Trades). These values are in effect sub-categories of a particularly claimed civilian skill. Inside these variables, there is a refinement of the skills held within the wider sub-category. IE. in field Administration, there is Administration officer, administration manager, data entry operator, general clerk, office manager etc.

    The selection process behind how to indicate what refined skills that you hold within the subcategory (such as Administration) is completed through a form. Image attached identifying how I have started to achieve this.


    Click image for larger version. 

Name:	Capture.JPG 
Views:	12 
Size:	92.4 KB 
ID:	34761

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-11-2018, 02:37 PM
  2. help in redesigning my tables
    By osfar in forum Database Design
    Replies: 4
    Last Post: 04-26-2017, 02:52 PM
  3. Replies: 2
    Last Post: 08-06-2015, 12:47 AM
  4. Best way to relate these tables.
    By rosco2382 in forum Database Design
    Replies: 2
    Last Post: 03-10-2014, 03:56 PM
  5. I just DON'T know how to relate Tables - PERIOD!
    By djclntn in forum Database Design
    Replies: 6
    Last Post: 02-26-2012, 07:21 PM

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
  •  
Other Forums: Microsoft Office Forums