I recently stumbled across the Data Model tools in Excel 2016 and want to know if I can use it to create a relational database structure in Excel with the data tables setup on the tabs of an Excel workbook. Can anyone point me to a tutorial, textbook or, better yet, an example database that gives an example of how to properly do this?
I'm comfortable with Excel VBA, know RDB theory and table design, and have created RDB's using database software like Access. Now I've managed to set up a three table RDB Data Model in Excel 2016 with two many-to-one links through a linking table that allows me to define many-to-many relationships. I've set up the RDB tables using Excel tables on separate workbook tabs and added them to the Data Model and defined relations. I'm not interested in linking to external databases and just want to limit the RDB to a single Excel workbook.
Now I'm struggling with some basic concepts, like:
- How does the data input/output flow work between the workbook tabs and the Data Model?
- How to I create the Excel Data Model equivalent of Access input and output data layouts so that I can enter data in multiple tables from one layout rather than moving from one Excel tab to another?
- How do I display linked records from multiple tables in an output or input layout (a layout containing a scrolling box showing the multiple linked records, for example).
- Is it possible to tab from record to record within an input layout?
I've tried searching the Excel forums without much luck.Any help would be appreciated.
I'm comfortable with Excel VBA, know RDB theory and table design, and have created RDB's using database software like Access. Now I've managed to set up a three table RDB Data Model in Excel 2016 with two many-to-one links through a linking table that allows me to define many-to-many relationships. I've set up the RDB tables using Excel tables on separate workbook tabs and added them to the Data Model and defined relations. I'm not interested in linking to external databases and just want to limit the RDB to a single Excel workbook.
Now I'm struggling with some basic concepts, like:
- How does the data input/output flow work between the workbook tabs and the Data Model?
- How to I create the Excel Data Model equivalent of Access input and output data layouts so that I can enter data in multiple tables from one layout rather than moving from one Excel tab to another?
- How do I display linked records from multiple tables in an output or input layout (a layout containing a scrolling box showing the multiple linked records, for example).
- Is it possible to tab from record to record within an input layout?
I've tried searching the Excel forums without much luck.Any help would be appreciated.
