Discovering Data Model

Before I left on a retreat, I was playing around with Access and data model. Pivot tables have something called data model and I was playing around with it to see how it works.

What I found was that it allows you to combine two tables, kind of like Access’ joining of tables and/or queries. This feature can work with Access tables and queries and could be a useful feature for those who do not know how to use Access. You might have to tell the person where the Access database is and also give that person access to the database.

But there is one aspect of this data model that may be put offing: you have to first create a separate pivot table for each table or query or any kind of data you want to use before you can use it in a data model. So you might end up with a bunch of tabs with useless pivot tables just so you can use the data in a data model.

To start a data model, you first insert a pivot table. In the very first box where Excel asks you where the data is and where you want to put the pivot table, there is a third item: “Add this data to the Data Model”. Every time you want to add a new data source to the Data Model, you start by creating a pivot table and then, in the “Create PivotTable” box, check the box asking you “Add this data to the Data Model”. After that, you will have to join multiple data sources by finding a field that is common between the data sources. You do that by manipulating the relationships in the PivotTable menu tab.