Linking tables to make Pivot Table Excel 2010

[DavidAustin]

Hi all,

I have a spreadsheet which has two data sources on. Table 1 has data relating to patients who have been discharged from the wards at the hospital I work at and Table 2 has the list of all the wards at the hospital. I can make a pivot table from table 1 easily however it will only list the wards present in table 1. Some of the wards may not have discharged patients for some time but are still required on the final report. The only way I thought of doing that would be to link the two tables together but I don't know how to/ if it's possible do it in Excel 2010.

All help would be appreciated as always!

[hvsummer]

only Database can have relation ship, you could study bout power pivot or access.
or just simply your idea like "lookup information from table 2 to add to table 1"

[zmbd]

DavidAustin
This is not an easy task to accomplish.
Two links:

+MS: Consolidate multiple worksheets into one PivotTable report

+ PDF: PivotTables and PivotCharts – Multiple Sheets Prairie State

If neither if these sources help, please post back.

[DavidAustin]

Hi zmbd and hvsummer,

I cannot use Access for this project and I am running Excel 2010 so power pivot is not available either.

I have previously tried both of the linked methods and cannot get them to work unfortunately. I've spent quite a lot of time searching for ways to get it working and can't find anything. I tried using MS query to join the tables but in the end pivottable this gives blanks for dates corresponding the unused wards, which when hidden would disappear from the report.

My previous "solution" for this problem was to use a vast range of sumproduct formulas which would take ages to load and constantly give ref errors when data was removed so I'm just looking to streamline the whole process!

[zmbd]

Ignoring the blank cells, did either of the linked to methods return the correct data?

[DavidAustin]

No, it came out with data that was merged randomly between the two tables. Could it be that the tables are not in the same format?

Table 1 is: ID, Name, DoB, ward, discharge date
Table 2 is: wardname, serviceline of ward, location

[zmbd]

Table 1 is: ID, Name, DoB, ward, discharge date
Table 2 is: wardname, serviceline of ward, location

Is the common field between these two sheets
t1!Ward::t2!Wardname

if so, is the same value used in both fields? If not, then you will have great difficulties in matching the data. Hence why in the RDMS setting, the Primary-Key of the table is the same data cast as it's Foreign-Key in the related table.

One more tutorial type link I have for doing PT across worksheets... when we moved to Off2013 I found this link for making PT using named tables... Advanced PivotTables: Combining Data from Multiple Sheets
However, I moved away from this construct a long time ago once I had a grasp on the relational-database and normalization and only do this for those without Access that don't want to install the runtime. :)

Too bad this wasn't just blank cells,
You can set the pivot table to display a value...
Right click inside the PT
PT-Options>Empty Cell as:> enter what to display in the correct format for the value cells (ie. use a zero ( 0 ) for numeric fields, not "x" etc...) and this might have solved your report quandary.

You might also consider using the "table format" in Excel2013, it helps to keep the data ranges set when adding or removing rows/columns. This format can be a pain to use; however, for simple pivot tables, this has really made them much easier for me as the data is a bit more dynamic and the PT only needs to be refreshed to get the new data appended within the table.

[DavidAustin]

Yes, Table 1's ward matches table 2's wardname. I have checked and the same values are used in both tables. As previously stated, I only have Excel 2010 not 2013 (which is a shame because I'm sure 2013 would be able to do this!). As there is sensitive patient data, we are restricted in creating databases with the information in otherwise I would definitely have this up and running in Access in no time!

[zmbd]

well.... 2010... I do like that version; however, we're asking a workbook to do the task of a database.

Let me think about this some more. In the meantime, maybe someone else will have an approach. I think we may end up with a VBA method under ACC2010.

ON a side stream:
As for Excel vs Access... IMHO... your IT-Security team has the wrong concept for data protection. Unless overridden by Group Policy, the Office Distribution Wizard, or compatibly mode via options, Access2007(+) can use reasonably strong encryption and as of ACC2010, and ACC2007 w/compatibility-SPK IIRC, the default Windows Cryptographic API is used( Microsoft: About cryptography and encryption in Office 2010 ); thus, once the database is encrypted - not password protected which is easy to defeat -, assuming the newer Win7->Current OS) the encryption should be fairly strong provided a reasonable password is used (lc,uc,#,@) (( Password Haystack: How Big Is Your Haystack... and how well hidden is YOUR needle? ))
At work, we use encrypted drives, vpn, and thumbdrives to keep the data secured from a hardware side.
(Honestly, when I've inherited old databases and workbooks, I have a list of the top passwords used, I can usually brute force by hand within 15 minutes - scary that! Now the network passwords... yuck so long and so much entropy and cycle limits against prior passwords - makes my head hurt sometimes to come up with a new passphrase and I have to have all my thumbdrives connected or they wont sync to the network after the new password is changed - sigh)

Show your IT guys the article about the default CSP and maybe they will let you use Access.

[DavidAustin]

Hi zmdb,

The main reason I haven't tried using Access is because every month we get sent an updated spreadsheet with all the discharges for the previous month. The spreadsheet I'm currently working with imports the data from this updated spreadsheet and adds it to my data table. I have had experience of importing data from Excel into Access and have found it to be flaky at the best of times. Once this spreadsheet is made, I'm leaving it to another member of staff who isn't particularly IT literate so I'm trying to make this as simple as possible for them (e.g. click a button which updates the pivot table with the latest information then exports it into a word report).

If I have to make it into Access I will but it will get rid of all the other stuff I've done so I would rather not go down that route unless absolutely necessary.

[zmbd]

I do not advise using this method.
Workbooks are not databases nor were they
ever intended to be a database
There are very cleaver methods to link to and import data in to Access from Excel. Normally, I create a secondary database to hold a temp table and validate the data in that table, then dump to the main tables in the backend.
There are other methods such as parsing each row or cell of data for data type and validity.

With that said:

Ok - back at work today and I know that we had done something like this in the past with some old workbooks that I subsequently moved to Access; thus, I went back to some older documentation from my Office2003 days and found a reference I had on doing this based on a book by T. Zapawa, so I searched on the name hoping to come up with something and lo-and-behold - I not only found a link to the book, I also found a tutorial!

Just to be sure that this is what we're after, I followed the tutorial and was able to recreate the MockRDMS we were using - two caveats... MS-Query must be installed, which it should be by default with Office2010, and I just recreated my old MockRDMS using an Office2013 - so anyway the link:
http://exceluser.com/formulas/msquer...ional-data.htm

Now a few tweaks that I encountered here while following the method. Because this is based on the Office2003 and I am in Office2013 I had to do the following.

+ Named ranges - no change. I highly recommend staying with the alphanumeric - no spaces - underscore as the only special character.

+ Step for creating the new connection. Do not follow this step as written. The connection wizard choked on the xlsx file; however:
++ Instead, in the databases tab, scroll down to and select {Excel Files*}
++ Double check that the option to use the wizard - Is NOT selected. My first time thru this I didn't catch that and the results were a mess.
++ Once you have selected {Excel Files*} [OK]; the file dialog will open, select your "back end"; follow the remaining steps for creating the query and relationships

The only issues I see with this is that the query is hardcoded to the path and file to the back end workbook and the range names are hard coded to the current dataset. There maybe some VBA that could be employed here to name ranges and establish the query and connection.

SO, I've worked a tad of VBA for this, it compiles and works on my PC w/ Office 2013 - once you modify it for your setup let me know... obviously, you'll need to setup the paths and SQL strings.


To use this:
Two workbooks.
1) "Back End"
Sheet1: People
A1-D1
people_pk, people_firstname, people_lastname
A2-C27 has various names
(A2)1001, Alpha, Zebra
(B2)1002, Beta, Yoke
etc...
(A27)1026, Zulu, Able

Named Range: People_Range
A1 - C27

Sheet2: Data
a1-C1
data_pk,data_number,People_fk
2001,100,1001
2002,200,1001
2003,300,1001
(...) further entries for people_fk 1007,1018,etc...

Named Range: Data_Range
A1 - C100

Save. I named this: "964602_excel_2010_MockRDMS_BackEnd.xlsx"

Open a new workbook.
Insert a new module or insert this code into the "thisworkbook" object.


Do not duplicate line 1 in the following if it already exists within your module

Option Explicit
 
Sub Example_MockRDMS()
Dim zWB As Workbook
Dim zCN As WorkbookConnection
Dim zPT As PivotTable
Dim zptsheet As Worksheet
Dim zFP As String
Dim zFN As String
Dim zDBQ As String
Dim zDefaultDir As String
Dim zSQL As String
Dim znewnames As String
Dim zemergency As Integer
'
On Error GoTo zerrortrap
'
    Set zWB = ThisWorkbook
'
'
    Set zptsheet = zWB.Worksheets.Add
'make sure the new sheet has a unique name
    znewnames = "NewPT" & Format(Now(), "YYYY_MM_DD_HH_mm_ss")
    zptsheet.Name = znewnames
'
'Here's where you'll want to seup your paths and file names.
'observe the format in each... it is importaint the the back-slashes be correct!
    zFP = "C:\Users\USERNAMEHERE\Documents\_Databases_Programming\Bytes_Work\964602_excel_2010_MockRDMS"
    zFN = "\" & "964602_excel_2010_MockRDMS_BackEnd.xlsx"
'
'build the connection string
    zDBQ = "ODBC;DSN=Excel Files;" & _
        "DBQ=" & zFP & zFN & _
        ";DefaultDir=" & zFP & _
        ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
'
'build the relational SQL for the named ranges in the mock backend database
'following the above tutorial, you should be able to 
'get this from your first setup.
'I've built a simple mock example here.
    zSQL = "SELECT people_range.people_pk" & _
        ", people_range.People_LastName" & _
        ", data_range.data_pk, data_range.data_number" & _
        " FROM data_range data_range, people_range people_range" & _
        " WHERE people_range.people_pk = data_range.People_FK" & _
        " ORDER BY people_range.People_LastName, data_range.data_pk"
'                              ^^^^^
'Notice, you will not be able to sort on two fields
'in the Excel query editor..... (-_-)
'
'Reuse the variable here to create a unique connection
    znewnames = "DC_MockRDMS_BE" & Format(Now(), "YYYY_MM_DD_HH_mm_ss")
    zWB.Connections.Add2 _
        Name:=znewnames, _
        Description:="Connection to ExcelFile as Mock RDMS backend", _
        ConnectionString:=zDBQ, _
        CommandText:=zSQL
'
'because I'm changing the connection names...
    Set zCN = zWB.Connections.Item(znewnames)
'
'ok, now for the magic insert the new pivot table in to the newly created worksheet
'
'Reuse the variable here to create a unique connection
'technically, we can use the same name on different sheets so that PivotTable1 could be on twenty different
'worksheets... @_@
    znewnames = "PvtTbl" & Format(Now(), "YYYY_MM_DD_HH_mm_ss")
    zWB.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=zCN, _
        Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:=zptsheet.Cells(1, 1), _
        TableName:=znewnames, _
        DefaultVersion:=xlPivotTableVersion15
    zptsheet.Cells(1, 1).Select
'
'make our life easier when refering to the new piviot table!
    Set zPT = zptsheet.PivotTables(znewnames)
'
'OK... now here is where we start setting up the row, column, and field information.
'this is a very simple example.
    With zPT.PivotFields("People_LastName")
        .Orientation = xlRowField
        .Position = 1
    End With
    zPT.AddDataField zPT.PivotFields("data_number"), "Sum_of_data_number", xlSum
'
'ok, hide the field list if visible... this worked in Excel2003 anbd in Excel2013...
    If zWB.ShowPivotTableFieldList Then zWB.ShowPivotTableFieldList = False
    '
zcleanup:
    If Not zCN Is Nothing Then Set zCN = Nothing
    If Not zptsheet Is Nothing Then Set zptsheet = Nothing
    If Not zPT Is Nothing Then Set zPT = Nothing
    If Not zWB Is Nothing Then Set zWB = Nothing
Exit Sub
zerrortrap:
    Debug.Print "ErrS: " & Err.Source & vbCrLf & "ErrN: " & Err.Number & vbCrLf & "ErrD: " & Err.Description
    '
    'prevent errortrapping loops... sometimes Shifu's-an-idiot :)
    If zemergency > 100 Then Exit Sub
    zemergency = zemergency + 1
    '
    Resume zcleanup
End Sub

The partial output from my example files:

 Row Labels    Sum_of_data_number
Chance           7500
Ink              12300
Tare             13200
Zebra            2100
 Grand Total     35100

[DavidAustin]

Wow, that looks like it could be what I'm after - does it definitely list all objects even if they have no data attached? EDIT: I have also just realised this is the same website I used a couple of weeks ago to do this and it didn't work in producing the final table as it wouldn't list all of the wards in the pivot table as required

I have started going through the tutorial (selecting the excel files option like you have said) but once I have been taken into MS Query, my tables do not appear in the add table box - any ideas? EDIT: just found out it has to be converted to a named range rather than a table

[DavidAustin]

I've been going through your vba and edited it so that zFP is my file path and zFN is the filename (adhering to the same format as your example). I have also pasted in the SQL from my original go (see below) and then ran the code. I am getting an error in the zWB.Connections.Add 2 section of:

ErrS: VBAProject
ErrN: 438
ErrD: Object doesn't support this property or method

The SQL I've been using (copied from microsoft query):

"SELECT DataTable.RioID, DataTable.Name" & _
        ", DataTable.DOB, DataTable.dischdate, Ward.Ward" & _
        ", Ward.ServiceLine, Ward.Location" & _
        " FROM {oj Ward Ward LEFT OUTER JOIN DataTable DataTable" & _
        "ON Ward.Ward = DataTable.Ward}"

[zmbd]

subtle change- a space between the quote and the ON predicate; thus,

"ON Ward.Ward = DataTable.Ward}"

goes to,

" ON Ward.Ward = DataTable.Ward}"

See if that works.
The MS Query uses a little different flavor of SQL than Access; however, the spaces are still the little minefields that await to terrorize us!

TBH - we're a tad out of my depth here now... (@_@)
Part of the reason I moved things to Access.

[zmbd]

Revised SQL

    zSQL = "SELECT people_range.people_pk" & _
        ", people_range.People_LastName" & _
        ", data_range.data_pk, data_range.data_number" & _
        " FROM {oj people_range people_range" & _
        " LEFT OUTER JOIN data_range data_range" & _
        " ON people_range.people_pk = data_range.People_FK}" & _
        " ORDER BY people_range.People_LastName, data_range.data_pk"

Returns a result of:

Row Labels      Sum_of_data_number
Able    
Baker    
Chance                 7500
Duff    
Ed    
(... etc ...)
Grand Total    35100

Right click on the table
Pivot table Options
Layout and Format tab
For empty cells show
0

Row Labels    Sum_of_data_number
Able              0
Baker             0
Chance            7500
Duff              0
Ed                0

using the GETPIVOTDATA()
In a cell:

=GETPIVOTDATA("data_number",$A$1,"People_LastName","Able")

Returns 0

Whereas

=GETPIVOTDATA("data_number",$A$1,"People_LastName","Chance")

Returns 7500

-Z

[zmbd]

Once again, I advise using a true database for this type of work. Excel is not, nor is intended to be, a RDMS. Data can be easily corrupted/orphaned and updates to data can be horrible to complete.


Ok DavidAustin,

This is the revised code to return as described in my prior post. You should be able to take it from here. Please post back with any tweaks or improvements.

Also added a line to set the row header to something better than [Row Labels] default:

Remember not to duplicate line1 in your modules :)
This code has been tested in Office 2013 using the files as described in post#11

A few things I've thought of that I'll leave for later.

+ Ideally the code would check for an existing connection to the "back-end" workbook and either delete that connection or modify the connection.
+ Using the file dialog ( insights>Select a File or Folder using the FileDialog Object ) one could allow the user some control to locate the desired file.
+Hmmm...

Option Explicit
 
Sub Example_MockRDMS()
'This code will make a connection to the specified workbook
'this workbook must have named ranges. In this case people_range and data_range
'the SQL relates the data from one range to the other.
'
Dim zWB As Workbook
Dim zCN As WorkbookConnection
Dim zPT As PivotTable
Dim zptsheet As Worksheet
Dim zFP As String
Dim zFN As String
Dim zDBQ As String
Dim zDefaultDir As String
Dim zSQL As String
Dim znewnames As String
Dim zemergency As Integer
'
On Error GoTo zerrortrap
'
    Set zWB = ThisWorkbook
'
'
    Set zptsheet = zWB.Worksheets.Add
'make sure the new sheet has a unique name
    znewnames = "NewPT" & Format(Now(), "YYYY_MM_DD_HH_mm_ss")
    zptsheet.Name = znewnames
'
'Here's where you'll want to seup your paths and file names.
'observe the format in each... it is importaint the the back-slashes be correct!
    zFP = "C:\Users\[USERNAMEHERE]\Documents\_Databases_Programming\Bytes_Work\964602_excel_2010_MockRDMS"
    zFN = "\" & "964602_excel_2010_MockRDMS_BackEnd.xlsx"
'
'build the connection string
    zDBQ = "ODBC;DSN=Excel Files;" & _
        "DBQ=" & zFP & zFN & _
        ";DefaultDir=" & zFP & _
        ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
'
'build the relational SQL for the named ranges in the mock backend database
'simple
    'zSQL = "SELECT people_range.people_pk" & _
        ", people_range.People_LastName" & _
        ", data_range.data_pk, data_range.data_number" & _
        " FROM data_range data_range, people_range people_range" & _
        " WHERE people_range.people_pk = data_range.People_FK" & _
        " ORDER BY people_range.People_LastName, data_range.data_pk"
'
'returns all of the last names in the named range people_range
    zSQL = "SELECT people_range.people_pk" & _
        ", people_range.People_LastName" & _
        ", data_range.data_pk, data_range.data_number" & _
        " FROM {oj people_range people_range" & _
        " LEFT OUTER JOIN data_range data_range" & _
        " ON people_range.people_pk = data_range.People_FK}" & _
        " ORDER BY people_range.People_LastName, data_range.data_pk"
'
'Reuse the variable here to create a unique connection
    znewnames = "DC_MockRDMS_BE" & Format(Now(), "YYYY_MM_DD_HH_mm_ss")
    zWB.Connections.Add2 _
        Name:=znewnames, _
        Description:="Connection to ExcelFile as Mock RDMS backend", _
        ConnectionString:=zDBQ, _
        CommandText:=zSQL
'
'because I'm changing the connection names...
    Set zCN = zWB.Connections.Item(znewnames)
'
'ok, now for the magic insert the new pivot table in to the newly created worksheet
'
'Reuse the variable here to create a unique connection
'technically, we can use the same name on different sheets so that PivotTable1 could be on twenty different
'worksheets... @_@
    znewnames = "PvtTbl" & Format(Now(), "YYYY_MM_DD_HH_mm_ss")
    zWB.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=zCN, _
        Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:=zptsheet.Cells(1, 1), _
        TableName:=znewnames, _
        DefaultVersion:=xlPivotTableVersion15
    zptsheet.Cells(1, 1).Select
'
'make our life easier when refering to the new piviot table!
    Set zPT = zptsheet.PivotTables(znewnames)
    zPT.NullString = "0"
'
'OK... now here is where we start setting up the row, column, and field information.
'this is a very simple example.
    With zPT.PivotFields("People_LastName")
        .Orientation = xlRowField
        .Position = 1
    End With
'Change the rowheader title to something better than the default [row labels]
    zPT.CompactLayoutRowHeader = "Last_Name"
'
    zPT.AddDataField zPT.PivotFields("data_number"), "Sum_of_data_number", xlSum
'
'ok, hide the field list if visible... this worked in Excel2003 anbd in Excel2013...
    If zWB.ShowPivotTableFieldList Then zWB.ShowPivotTableFieldList = False
    '
zcleanup:
    If Not zCN Is Nothing Then Set zCN = Nothing
    If Not zptsheet Is Nothing Then Set zptsheet = Nothing
    If Not zPT Is Nothing Then Set zPT = Nothing
    If Not zWB Is Nothing Then Set zWB = Nothing
Exit Sub
zerrortrap:
    Debug.Print "ErrS: " & Err.Source & vbCrLf & "ErrN: " & Err.Number & vbCrLf & "ErrD: " & Err.Description
    '
    'prevent errortrapping loops... sometimes Shifu's-an-idiot :)
    If zemergency > 100 Then Exit Sub
    zemergency = zemergency + 1
    '
    Resume zcleanup
End Sub

Lines 42 thru 48 are from the prior posting returning a simpler dataset. I've left them here for academic review - normally I would delete these in a production setting.

[DavidAustin]

I'm not going to lie, this looks well outside of my comfort zone for something I thought would be "simple". If I had to use Access for this, how would I make it produce the report in dataform that I need?

All I need is something that is like a pivot table but will show all of my wards on the rows without having a blank dates column at the top!

[zmbd]

The vba should be straight forward at this point and will handle most of the hard work. All you need to do is modify for your file locations and named ranges, etc...


To do this in Access - the Basics would be:
Design your data tables using at least 1NF normalization
( Insights>Database Normalization and Table Structures )

at least two data tables?
t_wards - PK and only the information directly related to the wards
t_w_admissions - PK, FK to t_wards, and ...

Without a generic copy of the data you are working with it's almost impossible to advise.

- to go into this in depth please start a new thread in the Access/VBA-Q&A forum( home > topics > microsoft access / vba ) you can always insert a link back to this thread for context :)