+ Reply to Thread
Results 1 to 12 of 12

Reporting from 2 tables and using relationships

  1. #1
    Registered User
    Join Date
    08-18-2017
    Location
    USA
    MS-Off Ver
    2013, 2016
    Posts
    23

    Reporting from 2 tables and using relationships

    Good Morning, I am working on a file to report cycle times for work processes. I also need to account for any downtime between those cycles and list the reasoning why. The attached file shows what I have so far. Sheet 1 is my data entry and on Week_MonthS I have the other table in AM:AN to create the relationship. The issue I am having is in sheet Chamber1. I have an example of what I want the pivot table to do below on Line 22. Is there a way to do this?
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Reporting from 2 tables and using relationships

    There is no relationships, so you need to create them

    one way you can do is merge two tables and create PivotTable from merged table

    Sheet: Source
    Sheet: Pivot
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    08-18-2017
    Location
    USA
    MS-Off Ver
    2013, 2016
    Posts
    23

    Re: Reporting from 2 tables and using relationships

    When I tried making a relationship earlier it told me the type was a mismatch. I tried to build a relationship between the reason and the down time between the tables.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Reporting from 2 tables and using relationships

    You can't do that this way you did, because vlues in columns (week-week, reason-reasoning) aren't unique.

    edit: you mean you tried merge two tables earlier? Or I misunderstood....
    Last edited by sandy666; 09-18-2017 at 01:37 PM.

  5. #5
    Registered User
    Join Date
    08-18-2017
    Location
    USA
    MS-Off Ver
    2013, 2016
    Posts
    23

    Re: Reporting from 2 tables and using relationships

    So would I just insert the Week/Reason into the bottom of the table for each chamber?

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Reporting from 2 tables and using relationships

    Noooo

    You've Ex2013/2016 so probably there is PowerQuery (2013) or Get&Transform (2016). Add first and second table to Workbook Queries then Merge these two into one. Then create PivotTable from merged table.
    To see it in my example: Data - Show queries

    My example has nothing common with your data except values

  7. #7
    Registered User
    Join Date
    08-18-2017
    Location
    USA
    MS-Off Ver
    2013, 2016
    Posts
    23

    Re: Reporting from 2 tables and using relationships

    Okay, so I was able to get PowerQuery onto my office computer. I have the connections made but the information is being repeated for each week on the pivot table, any help and explanations on what I did wrong with the connections would be fantastic!
    Attached Files Attached Files

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Reporting from 2 tables and using relationships

    Probably you merged queries by Week not by Reason/Reasoning
    try select in 1st query : Reason, in the 2nd query : Resoning, Join kind : as is
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-18-2017
    Location
    USA
    MS-Off Ver
    2013, 2016
    Posts
    23

    Re: Reporting from 2 tables and using relationships

    My Launch Editor shows: Merged Table: Week_Reason - Joining Reasoning & Chamber_1 - Joining Reason. Join Kind: Left Outer

    I then have expanded the Chamber_1 query to select the information for the pivot table. I guess the only thing that is not working correctly is the information is still being reported exactly the same for each week..

    EDIT** I mave have figured it out, if I select to to merge by Reason and then by week it looks like it is coming out to what I want it to. I will test it in my main file and will update the post with a solved if it works out.
    Last edited by dsharman; 09-19-2017 at 04:12 PM.

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Reporting from 2 tables and using relationships

    you can't merge queries by columns with different kind of data or you'll get any result which will not be correct
    With my last attached file is that what you want ?

  11. #11
    Registered User
    Join Date
    08-18-2017
    Location
    USA
    MS-Off Ver
    2013, 2016
    Posts
    23

    Re: Reporting from 2 tables and using relationships

    Here is the file with how the data should be and is represented now. I am not sure if it helped sorting the rows or not, but it works.
    Attached Files Attached Files

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Reporting from 2 tables and using relationships

    You merged 2nd query to the 1st query
    I did it 1st query to 2nd query
    That depends which table will be added first, and which second and it will be merged
    I selected bigger table as first.
    it's quite different

    source sorting doesn't matter if source (query in this case) will be used in PivotTable

    but this is technical details. I'm glad if it works for you
    Last edited by sandy666; 09-19-2017 at 05:13 PM.

+ 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. Calculating fields after combining tables with relationships
    By tyip0217 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-07-2017, 03:08 PM
  2. [SOLVED] Creating relationships between tables (excel 2003)
    By LukeM82 in forum Excel General
    Replies: 1
    Last Post: 08-10-2016, 04:13 PM
  3. Using Relationships in Powerpivot
    By kiddles13 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-04-2016, 04:57 PM
  4. How to link two tables together with relationships
    By ks100 in forum Access Tables & Databases
    Replies: 2
    Last Post: 02-19-2015, 11:25 AM
  5. Replies: 10
    Last Post: 11-03-2013, 02:24 AM
  6. [SOLVED] Survey reporting with pivot tables
    By jlanzi in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-06-2012, 03:45 PM
  7. Replies: 6
    Last Post: 10-14-2011, 12:34 PM

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