I want to transcend myself from Ms Access to MS SQL Server.
This is like comparing Apples to Horses. The two applications do not do the same thing. Access is a RAD (rapid application development) tool that is used to create data-centric applications. It is not a RDBMS. SQL Server is a RDBMS and it's task is to hold and manipulate the data. People frequently confuse Access with Jet (.mdb) and ACE (.accdb) which ARE database engines but they are desktop engines not server based. They are used by Access to hold all the objects of an application so Access is 100% dependent on them for the application objects. However, data for your application can be hosted by any ODBC compliant RDBMS. SQL Server is most common since they are both MS products but I've used IBM's DB2, Oracle, Sybase, and a handful of lesser known options.
So when you say "transcend" yourself are you saying you want to leave Access as the FE tool or do you want to start using SQL Server to host the data for your Access applications?
If you are comfortable with Access SQL, you can muddle through with SQL Server SQL. The level Access uses is 1992 I think and is very basic. All RDBMS' have added lots of features since Access was developed although they still support the original basic level.
Take a look at the link I posted earlier and use that as a starting point. Install SQL Server express, which is fee, and download the SSMA tool to upsize the tables.