"Relational Databases in Excel" - Pivot Tables

[AstToTheRegionalMGR]

Hi!

Does anyone have experience using "Relational Databases" in Excel? It might only be available in Excel 2013 but I'm not sure. Anyway, it is a feature of pivot tables where you can create a data model and collect data from multiple tables in your pivot table using foreign keys.

I'm having trouble with it. I have two tables containing all my company's employees in each of our database systems (ADP and a homegrown database using SQL Server). I have a field in my first table (ADP) that contains unique identifiers (UID's) for each record (employee - no duplicates), and my second table (homegrown database) contains the same list of employees, but many of them have multiple accounts in our homegrown database, thus the UID's are duplicated in this table. I am trying to create a pivot table, based on the ADP table, showing each employee from ADP and all their matching accounts (call this field user_id) in the homegrown system based on the UID field. When I try to do this, the pivot table just shows me each UID in column A, and then shows me every single user_id before showing the next UID. I've created a relationship between the two so I'm really not sure what I'm missing.

Any help is appreciated! I've attached a sample. Example - Cell A40 '3120500264' should only show three user_id's - 4989, 3726, 4566.

[tony h]

I haven't got time to look at this at the moment (and it isn't something with which I am familiar) but I think it might need the relationship setting up on the data tab.

[AstToTheRegionalMGR]

I haven't got time to look at this at the moment (and it isn't something with which I am familiar) but I think it might need the relationship setting up on the data tab.

tony h - I have relationships set up based on 'emplid_value', but that hasn't solved it. It does seem to work correctly when I set 'user_id' to both Rows and Values (count of), but then I end up with an extra column showing the count. I can work with this, but it's not ideal. Let me know if you figure it out!