Help with creating a custom content type

Do NOT use lookups on tables.  They are difficult to work with and are essentially a crutch for someone who doesn't know how to make a query.  Combos on forms are a much better choice.

You seem to not have settled on your platform.  Access is a desktop application although you can link to SharePoint lists.  However the SharePoint lists themselves are problematic since SharePoint is not a relational database and will impact how you design the Access app.  SharePoint lists will need to be pretty small - no more than a few thousand rows each unless big strides have been made in the technology.  Access linked to Jet/ACE or SQL Server is fine with millions of rows so SharePoint is very limiting.  The only thing it gets you is the ability to share an Access FE with people outside your LAN.

Do you actually need web access for this application?  If so, take Access off the table as a FE.

If you want a desktop application, then Access will be fine.  In that case, you would use the concept we call cascading combos to have one combo control the contents of the "next" combo.  For example, choosing a state in combo1 will limit the cities in combo2 for the state in combo1.

Hi thanks for the response.  This project will indeed need web access. It is a redesign of a previously created custom list from 2007.  It used the access SharePoint web app  to store data.  I did try using a similar method from what i viewed in the original.  Mine how ever was just using an excel document library where users could update the data and save changes back to the document library.  I also created a group & status field within the library so users group the the excel files and see what their current status was.  To me this was the best way to go and easier to manage. Unfortunately I'm working with older users who are not comfortable with coming out of SharePoint to make changes & are requesting that if function like the access web app.  An alternative would be to install web app sever onto our farm and they could edit the excel files online, but time is the key restraint.  Thus what led me to start looking into using methods such as lookup to link a group/status list to the actual content list.

You can have an Access Client/Server database that links to SharePoint lists.  The issue is that the Access app is not integrated with SharePoint and they will have to start the Access app outside of SharePoint, just as they would if it were linked to local tables or SQL Server.

Are the users actually remote, meaning that they do not have access to your LAN?

If you want the app to be integrated with SharePoint as it was originally, your only option is to run your own SharePoint server so you can control the version.  This is pretty risky since MS has already decided that they are going to abandon AWA's so you will not get any support.

If the users really are remote, you can still use an Access app but you would use Citrix or RDP to serve it to the remote users.  The advantage of this configuration is that the Access app can link to either Jet/ACE or SQL Server or SharePoint (but why) and the app will work the same whether it is being opened by a user on the LAN or logged in via Citrix or RDP.

Just a followup. I've decided to abandon trying to use the SharePoint list.  The requirements are way too complex and require the use of a database. To resolve this I've decided to use Access and SharePoint Access services to store the page and create queries.  Although Microsoft is ending support for Access, this will allow me time since we will not being going to 365 for at least a year and this project requires something much sooner.  Eventually I'm considering just migrating everything to a SQL DB & creating query pages in asp.

SharePoint will stop supporting AWA in April of  THIS YEAR so you don't have a year.

This feature will be retired from Office 365 and SharePoint Online. We will stop creation of new Access-based web apps and Access web databases in Office 365 and SharePoint Online starting in June, 2017 and shut down any remaining web apps and web databases by April, 2018.

If you run your own SharePoint site, you might have some breathing room but ONLY if your IT department is willing to not update it until you are ready to give up the AWA.

On-premise SharePoint Server with Access Services    If your organization has an on-premise environment with SharePoint 2016, Access Services, and SQL Server 2016 you can move your Access web app from Office 365 or SharePoint Online to your on-premise environment. For more information, see Move an Access web app from Office 365 or SharePoint Online to an on-premise SharePoint Server.

https://support.office.com/en-us/article/access-services-in-sharepoint-roadmap-497fd86b-e982-43c4-8318-81e6d3e711e8

I dislike SharePoint lists but your Access Desktop app can continue to use them.  It is only AWA that is being deprecated.  Not the ODBC link to the SharePoint lists.