I understand - and have created - a self-referencing table that contains Categories & Subcategories. SO, you get a table that looks like this:
Categories Table:
id, primary Key autoNumber
category, text
parentID, foreign Key, number
categories.ID has a one-to-many relationship with categories.parentID
==Here is what I'm having a really tough time understanding==
I also have a Transactions table used to track a purchase, and two columns to record the Category and its Subcategory. Think "Automobile: Gasoline", or "Healthcare: Prescriptions".
Transactions Table:
id, primaryKey, autoNumber
payee, shortText
Category, foreign key, (number, comboBox)
Subcategory (number, comboBox)
categories.ID has a one-to-many relationship with Transactions.Category
I can't find the appropriate way to populate Category and Subcategory in the Transactions table.
So far, I was able to pull all distinct categories from the Categories Table with a SQL statement similar to the example below. (Any Category with a NULL parentID is considered a root category).
SELECT *
FROM Categories
WHERE (((Categories.[parentID]) Is Null))
ORDER BY Categories.category DESC;
My problem is finding a way to get Transactions.Subcategory to list only the subcategories associated with the chosen root category.
I don't know how to do this in Access.
Any tips, advice, etc., would be very much appreciated.
