My 2 cents:
Initial plan was to keep it to two tables
Mistake #1. Have as many tables as it takes to achieve the goal and abandon any such thinking. Without knowing much about the business, I can see tables for products, a lookup table for categories and maybe one for variants. Then there might be a junction table to join categories to variations and/or products to variations. It is not clear if every product can include any variant (thus 300 variations x 100 products = 30,000 "products") or not.
You would not use a navigation form; rather a tab control on a form. Tables come first, then queries to support forms, then forms and reports. IMHO don't put forms before queries as a form that turns out to be useless because of its recordsource is no good at all.
You've posted far more about how you think it should be designed than you have about the business info. That is the part we need to understand in order to provide meaningful suggestions or comments on your ideas.
Or do I need to go through and assign individual fields to each "Category" query that I will need to run
If a table contains a category field with a repeated category value for each attribute in that category, then 1 query retrieves every attribute based on the category criteria. There's no need for multiple queries for this.
I find it odd that any base item that can be produced in variations would ever have the same sku.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.