Jump to content


Photo

Relationship Pivot Tables

Reformat or Merge

  • Please log in to reply
3 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 21 March 2017 - 04:25 PM

Hello,

 

I will offer an attachment that you will find familiar as I have been trying for months to get a Relationship OLAP PT constructed from three WS's....Game, Pitching...Play by Play. No matter what I've done I can not get the PT to function with integrity. I get partial results but usually with Excel apprising me that 'new relationships must be created.'

I've noticed that when I go back and look at my three columns that the Pitching Column always goes out of order on the Play by Play WS. I've highlighted it in red in the attachment. Other than that I can not find reason why the PT always breaks down.

 

I was wondering if reformatting one or more of the three columns in each of the WS's in some way would render me a workable format?

Do you think that the incorrect order that the Play by Play WS displays in the Pitching column means anything?

Is merging the WS's into one big WS a viable solution?

 

Thank You

 

JPK1066

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 22 March 2017 - 10:57 AM

Hi JPK,

 

I've taken a look at your attachment but it doesn't contain any relationships so I can't determine whether they have been set up correctly.

 

It sounds most likely to me that this goes back to the fact that pivot tables are only designed to summarize information and are not designed to work in the same way that a database query would. Pivot tables allow you to very easily get totals, averages and counts, but are not intended to extract individual records.

 

If you'd like to attach an example with relationships in place I can examine them, but it looks like you really want a queryable database, and this isn't something you can easily create in Excel.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 90 posts

Posted 24 March 2017 - 10:50 PM

The Relationships are:

1st                                                                  2nd

     Table                                                              Table

     Pitching..................Gm ID(Foreign)                Play by Play...................Pitching ID(foreign)

 

     Related Table                                                 Related Table

     Game                     Gm ID(Primary)               Pitching ID....................Pitching ID (Primary)

 

I have it functioning but with various disconnects. How do I know what is connected and what isn't without trial and error?

Is there a means of making a preference for a set of constraints used in a PT so you can recreate it without copying or writing it down? This would include moving columns around in the table created and not having to do so every time you leave the table and come back.

 

Thank You

 

JPK1066



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 25 March 2017 - 11:34 AM

Hi JPK,

 

From what I can tell, this is the relationship model that you've created:

 

datamodel.png

 

I can't see any problems with this model, but it's possible that problems may be caused if you try to access data without taking into account the 'bridging' between tables. Data from the PlayByPlay table can't be directly related to data in the Game table, but must first extract its related Pitching records.

 

An explanation of relationships and relational data can be found in the video lesson: Expert Skills Lesson 6-1 Understand primary and foreign keys.

 

You can create a backup of a pivot table by simply copying and pasting it. The copied version will not be connected to the original and will retain its settings. You can use this technique to easily create many pivot tables with the same settings.

 

Copying and pasting is covered in the video lesson: Essential Skills Lesson 3-3 Cut, copy and paste.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users