Clinical trial patient management and MS Access

I think Access may be the wrong tool, at least for the BE.

If you are in USA you have to be HIPAA compliant when storing patient data with PII. Your hospital will have a HIPAA officer whose job is to worry about people like you collecting patient data without oversight.

In other jurisdictions other rules may apply (e.g. GDPR in Europe). Don't skirt them! Could be a career-ending move. Rather find out what the rules are, and work transparently within them.

Access BE is not safe for sensitive data. However, Access can be a front-end to data securely stored in SQL Server.

Good to hear you are considering data privacy from the very beginning.

.

What sort of "documentation" are you talking about?

.

Since you are interested in PowerPivot (and presumably the other "PowerTools" here is a link to a collection of articles and free webinars you can view to get a preview of the course.

.

No, I was not thinking you were importing from some other format data system. I was asking if you wanted to import the data from the current spreadsheets into the new system?   PowerQuery can be used to do that if you wanted to, but it also can be used to import the data from the current spreadsheets into the new system.

Power Tool Courses – Wiki – Learn

.

*** Wiki with more specific information and examples *** .

The following Wiki has a collection of links for the various “advanced” Excel “Power” tools. There links to free webinars, short articles and a few free and paid ebooks.

.

I like to download the webinar replay recordings for future reference, and download the example workbooks.  If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that).

.

These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price).

.

Wiki: Get Started with Get & Transform > Tables > PivotTables > PivotCharts > Dashboards ... – PivotTable Wiki

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620

Thank you for your insightful comment! 

I should have added this, but HIPAA compliance is always at the forefront and we have in house compliance and security specialists. All PHI abstracted is stored on shared drives hosted on the organization’s network (cannot be accessed on our local networks) and we use Office 365 enterprise wide that’s HIPAA compliant (executed BAA, enterprise wide protocols, etc.). We have permission from on high to store PHI within 365. 

Excel is useful in a lot of ways for what I’m currently doing. Im currently in some Excel/VBA courses, and I have been told Im trying to use Excel as a relational database and that’s when I was pointed on the road to Access. Ive actually signed up for some upcoming PowerPivot + data model courses, but I didnt think they were meant for documention? Cery interesting if so.

Also to clarify, none of the abstraction I deacribed is done through importing from EMR or elsewhere. It’s done manually through data entry into forms Ive made within Excel (another reason I felt the need to shift to Access).

It certainly is a reasonable candidate for Access or Excel. Excel has also had a lot of features that allows it to be used as a simple data base system.  But do you really want to DIY it.  With sensitive patient medical information, that you are sharing with other people in your office (and outside of your location?) information security is a prime consideration. You might want to consider hiring a consultant to do this for you. Check out their credentials, in the real world, before hiring someone.

Either way you have to identify your "tables".  A table is any repeating data. A good way to identify table candidates is paper forms.  Within a form, if you have a block of repeating information in a grid, that is likely to also be a table.

Rather than retyping the same repeating information you use "keys" to link the various tables (both excel and access).

Would it be useful to import data from the current spreadsheets?

Here are some articles on how to use Excel as a database.  The same basic concepts apply to Access

PowerPivot – Data Model - Excel Data Model allows you to store and analyze data without having to look at it all the time. Think of Data Model as a black box where you can store data and Excel can quickly provide answers to you.

Because Data Model is held in your computer memory rather than spreadsheet cells, it doesn’t have one million row limitation. You can store any volume of data in the model. The speed and performance of this just depends on your computer processor and memory.

10+ steps to using Excel 2013's new data modeling feature           2013 11 14              By Susan Harkins

https://www.techrepublic.com/blog/10-things/10-steps-to-using-excel-2013s-new-data-modeling-feature/

Use Excel's new data modeling features to help you turn data into meaningful business information.

If you’re importing foreign data or sharing a workbook, the data often ends up in several sheets. As a result, turning data into meaningful information can be difficult. That’s where Excel 2013’s new data modeling capabilities can help even casual users. By building a relationship between sheets, Excel 2013 makes summarizing data spread across multiple sheets easy.

This article is for users unfamiliar with the feature and trainers supporting Excel users. This feature isn't for serious database developers, and please don't blast me for Microsoft's unfortunate choice for naming this new feature.

.

Create a Data Model in Excelhttps://support.office.com/en-us/article/Create-a-Data-Model-in-Excel-87E7A54C-87DC-488E-9410-5C75DBCB0F7B****A Data Model allows you to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables and PivotCharts. A Data Model is visualized as a collection of tables in a Field List, and most of the time, you’ll never even know it's there.

Before you can start working with the Data Model, you need to get some data. For that we'll use the Get & Transform (Power Query) experience, so you might want to take a step back and watch a video, or follow our learning guide on Get & Transform and PowerPivot.

.

!****Power Pivot - Overview and Learninghttps://support.office.com/en-us/article/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80-028a6d2432ed

Excel for Office 365, 2019, 2016, 2013

Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.

Power Pivot is available as an add-in for Excel, which you can enable by following a few simple steps. The underlying modeling technology found in Power Pivot is also found in the Power BI Designer, which is part of the Power BI service offered from Microsoft.

.

Import Data into Excel, and Create a Data Modelhttps://support.office.com/en-us/article/tutorial-import-data-into-excel-and-create-a-data-model-4b4e5ab4-60ee-465e-8195-09ebba060bf0

The sections in this tutorial are the following:

.  *  Import data from a database

.  *  Import data from a spreadsheet

.  *  Import data using copy and paste

.  *  Create a relationship between imported data

.

Extend Data Model relationships using Excel, Power Pivot, and DAXhttps://support.office.com/en-us/article/tutorial-extend-data-model-relationships-using-excel-power-pivot-and-dax-cf7197d3-1938-490e-93fb-20371e8dd67a

In this tutorial, you use Power Pivot to extend the Data Model, create hierarchies, and build calculated fields from existing data to create new relationships between tables.

.  *  Add a relationship using Diagram View in Power Pivot

.  *  Extend the Data Model using calculated columns

.  *  Create a hierarchy

.  *  Use hierarchies in PivotTables

.

Create Map-based Power View Reportshttps://support.office.com/en-us/article/tutorial-create-map-based-power-view-reports-62ecd29a-91b8-4e5c-b3b7-4aef9f7ef962

In this tutorial, you use that extended Data Model to build compelling reports that include multiple visualizations using Power View.

.  *  Create a Power View report

.  *  Create calculated fields for Power View and PivotTables

.  *  Set field defaults, table behaviors, and data categories

.

Create Amazing Power View Reports - Part 2https://support.office.com/en-us/article/tutorial-create-amazing-power-view-reports-part-2-361b1fca-4429-42a2-94a2-e4d5f3e7d170

In this tutorial, you learn how to create additional interactive Power View reports. When you publish these reports and make them available on SharePoint, these visualizations are just as interactive as they are in this tutorial, for anyone viewing them.

.  *  Create Multiples Charts

.  *  Build Interactive Reports using Cards and Tiles

.  *  Create Scatter Charts and Bubble Charts with Time-based Play Visualizations

.

Incorporate Internet Data, and Set Power View Report Defaultshttps://support.office.com/en-us/article/tutorial-incorporate-internet-data-and-set-power-view-report-defaults-a0b627f4-edcb-42fa-9815-646077064a7a

In this tutorial, we extend and optimize the workbook with more data, interesting graphics, and prepare the workbook to easily create amazing Power View reports.

.  *  Import Internet-based image links into the Data Model

.  *  Use Internet data to complete the Data Model

.  *  Hide tables and fields for easier report creation

.

PowerPivot Help TopicsThis link is the home page for links to A LOT of articles and tutorials. Well worth digging through****https://support.office.com/en-us/article/power-pivot-help-241aac41-92e3-4e46-ae58-2f2cd7dbcf4f

Power Pivot provides advanced data modeling features in Microsoft Excel. Use the resources below to learn about how you can use Power Pivot to help you gain new insights into your data.

.

!Welcome to PowerPivot            2011 05 22https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/gg413471(v=sql.105)

Welcome to PowerPivot for Excel. This section includes an introduction to PowerPivot, how to install PowerPivot, and where to find sample data and videos.

.  *  Introducing PowerPivot for Excel

.  *  Learn About PowerPivot Capabilities

.  *  PowerPivot Capacity Specification

.  *  Install PowerPivot for Excel

.  *  Get Sample Data for PowerPivot

.  *  Watch Videos About PowerPivot Tasks

.  *  PowerPivot Overview for IT Administrators

.

PowerPivot User Interface         2011 05 22

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/gg413438(v=sql.105)

Take a tour of the PowerPivot for Excel interface. This section includes an overview of the UI, a glossary of terms, and the UI reference with information about dialog boxes and interfaces found in the Excel window and in the PowerPivot window.

.  *  Take a Tour of the PowerPivot UI

.  *  Excel Window: PowerPivot Tab Reference

.  *  PowerPivot Window Reference

.  *  The PowerPivot UI in Windows XP

.  *  PowerPivot for Excel Glossary.Introducing PowerPivot for Excel

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/gg413497%28v%3dsql.105%29

PowerPivot is an add-in for Microsoft Excel 2010 that enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance.

.

After you have the design setup in PowerPivot, you can use PowerQuery to import and manipulate data and use Excel (display) tables to format data and Pivot tables to generate summary reports.  Slicers/Filters on the tables and pivot tables allow you and users to select specific information to view.