Divide the migration to 3 different parts:
- The governance & Process flow
- The content- migrating the data
- The interface
Governance, planning and process flow
First of all decide what is going to be migrated and create a migration map and a document containing all the information.
Before any migration to the cloud I would like to refer you to the following article:
The most important one is: do not forget the what? who? and why business users are going to use it.
The simple understanding of who access what? which users are going to use this application and how? have a basic process flow and well defined personas for your application. If it is big enough, user stories will be needed as well.
After reviewing those topics you would be able to figure out the SharePoint solution architecture vs the existing one in Access. The how will be cleared, should you use several lists? how about groups? sites? PowerApps? maybe several pages just for the view and some custom made buttons?
2. Migrating the data –
- Create the data repositories – the lists, you can export the Access data by tables to Excel.
- SharePoint, unlike Access, is not a relational Data Base, that means that the data should be not normalized and hierarchy’s are not going to work the same unless a custom solution is applied.
- The easiest way is using the quick edit mode on a pre-created tables – yes that means basically rebuilding the Access solution
3. Migrating the interface
Prepare the solution – the data is going to reside in SharePoint lists? the access is going to be through list forms? another 3rd party solution? What is the user interface going to look like?
I would recommend to use Power Apps in order to achieve the forms look and feel and enjoy the benefits of the flow for any business rules you have mapped in the first part. The Power Apps can be connected to several data sources, hence creating the illusion of a relational DB after all. A very important tip, make all your editing in Power Apps desktop application. The online version is a bit, sneaky ;-).