Automate

 View Only

  • 1.  Queries pulling too many lines of information

    Posted 09-17-2021 15:59
    I am working on a query that can pull the information related to a material number. For example a material may have inspection plans attached to it.

    The idea way to see would be: Material number ------ > inspection plan 1, 2 ,3 etc. 

    But what actually happens is an entirely new row is created with all the material information replicated just a different inspection plan 

    Material number ----> inspection plan 1 
    Material number ----> inspection plan 2
    Material number ----> inspection plan 3

    Since I have many tables this ends up creating a new line for every difference, leaving me with 16 rows when only one is needed 

    Is there a workaround to this, so that I could all inspection plans (as an example) could be on one cell on the same row as the material as opposed to creating a new row for each output 

    Thank you. 


    ------------------------------
    Michael Codreanu | Specialist, Supply Chain Master Data
    Apotex Inc | 4166240306
    ------------------------------


  • 2.  RE: Queries pulling too many lines of information

    Employee
    Posted 09-20-2021 10:05
    Hi Michael,

    As you have found, the data gets stored in many tables in the backend - it's 'normalized' to optimize storage.  When you join them, you will end up with multiple rows if your material has multiple inspection plans.

    There are several ways you can consider reformatting the:
    • using an Excel pivot table  or a reporting tool your company may have where you can rollup the output
    • Another way to reformat would be using vlookups .  Example, I ran two queries, one on PM notifications with equipment and one on equipment class characteristics.  If I had ran them all together, I would have a line for every characteristic.  Instead, I used formulas in query 1 which output the notification with equipment that applied vlookups, and then ran the query for the characteristics on a separate page.  The vlookups pulled in the characteristics so that I had them all in 1 row.

    I hope that gives you a few ideas.

    Best Regards,
    Sigird

    ------------------------------
    Sigrid Kok
    PSE | Winshuttle NA
    ------------------------------

    Original Message