It's been a productive week, after resolving the SQL questions, I rebuilt all the tables that were in Access into SQL (currently 6 tables).
After looking at the tables for normalization issues, and not liking some of the layout, I took out a ticket details table (because SQL text 16 will allow massive amount of text). Now the design will be for a continuous text field for initial problem entry and subsequent tech update entries. This makes the layout nicer, but another reason for the change was that multiple entries into a ticket caused me troubles on the reporting side (multiple rows shown for only on ticket number due to the one-many relationship with ticket and tech update entries).
I'm still using Access .adp file to access the SQL, and have customized some of the queries in SQL Analyzer, some in Access, I had to recreate the forms, and I'm doing customized navigation menus.
With the core infrastructure in place and stable now, I'm going to be looking at restricting editing per logins, creating a more stand alone (as in not opening full Access) look to the app, create a master form for all data entry, query, reporting functions.