+ Reply to Thread
Results 1 to 3 of 3

Idea to converting to a good database that i can do dynamic filtering

  1. #1
    Registered User
    Join Date
    08-17-2020
    Location
    Singapore
    MS-Off Ver
    365
    Posts
    3

    Idea to converting to a good database that i can do dynamic filtering

    I have this employee training sop matrix which indicate the required sop training that employee has to attend.
    I like to know, how can i convert this matrix to a smarter database so i can apply those cool dynamic array staff such as filtering, pivot table.

    My main goal is to to be able to apply a dynamic filtering formula to filter a particular employee and see the overall training that this person need or maybe a specific training sop to see which employee that are required to attend.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: Idea to converting to a good database that i can do dynamic filtering

    The first thing that you need to do is normalize your data. You can do that by bringing your table into Power Query Editor and Unpivoting the columns containing the names. Close and Load to Native Excel. Next open up MS Access (this is your database system). Create a new blank db and import the normalized table into Access. You can now build a form for adding additional records and you can create queries to extract whatever information you desire. Attached is your original file with the data normalized. Additionally, I created an Access db which contains only the normalized table from Excel. Both are in the attached Zip file.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,430

    Re: Idea to converting to a good database that i can do dynamic filtering

    If you want to be able to sort, filter, analyse (pivot) and chart the data, you would need one record for each employee/course combination.

    For example:

    PHP Code: 
    Employee    Training SOP    Version No
    Tim    QP
    /003    1
    Tom    QP
    /003    1
    Henry    QP
    /003    1
    Julia    QP
    /003    1
    Tim    QP
    /004    2
    Tom    QP
    /004    2
    Henry    QP
    /004    2
    Julia    QP
    /004    2
    Tim    QP
    /005    1
    Tom    QP
    /005    1
    Henry    QP
    /005    1
    Julia    QP
    /005    1
    Tim    QP
    /006    3
    Tom    QP
    /006    3
    Henry    QP
    /006    3
    Julia    QP
    /006    3
    Tim    QP
    /007    4
    Tom    QP
    /007    4
    Henry    QP
    /007    4
    Julia    QP
    /007    4
    Tim    QP
    /008    5
    Tom    QP
    /008    5
    Henry    QP
    /008    5
    Julia    QP
    /008    5 
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Is it good idea to use Global variable?
    By lastnn30 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2021, 03:01 PM
  2. want idea to how can make this in userform with database for lot of thousthand data
    By mazan2010 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2017, 02:32 AM
  3. [SOLVED] Is using names with function get.workspace a good idea?
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2015, 02:45 AM
  4. Macro to go to a hidden worksheet. Good/Bad idea?
    By Barking_Mad in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2010, 02:07 PM
  5. Filtering/flagging/ any idea
    By rediff123 in forum Excel General
    Replies: 2
    Last Post: 09-29-2009, 07:50 PM
  6. [SOLVED] Protected Workbook - Not a Good Idea!
    By Karen in forum Excel General
    Replies: 2
    Last Post: 07-06-2006, 12:25 PM
  7. RecordSet & New Class - Good idea?
    By Alan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2005, 02:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1