Welcome to the forum..
So, I have to say that I would revise your field names. "Date" and "Name" are reserved words in Access and shouldn't be used in object names. "ID" is a poor name for a field especially if every table has the same PK field name.
Here is how I would name the tables:
Code:
tblCategory
----------
CatID_PK (Autonumber)
CategoryName (Text)
tblSubCategory
----------
SubCatID_PK (Autonumber)
CatID_FK (Long)
Show (Boolean)
SubCatName (Text)
tblBudget
----------
BudgetID_PK (Autonumber)
SubCatID_FK (Long)
BudgeDate (DateTime)
BudgeAmount (Double)
tblTransactions
----------
TransID_PK (Autonumber)
SubCatID_FK (Long)
Store_FK (Long)
TransDate (DateTime)
TransAmount (Double)
TransNotes (Text)
Is the design I am looking at the best one for the implementation?
"Best" design is relative. The best design for me might not be the best design for you.
Is the only way to get this design to work with VBA?
You might be able to use just queries, but that depends on what you want to do with the budget and what you want to see.
I am using an Excel spreadsheet I made years ago. It has months horizontally and categories down the left side. I can see what I planned to spend (budgeted) and what I spent (actual) for the month. (there are total rows)
I can collapse rows so it doesn't display details. But I don't have the detail that you seem to want. I buy things from the store and it gets entered under "Groceries", not broken down to hamburger, Pepsi, paper towels, etc. A lot of things get entered under "Misc".
But I find this an interesting idea.