Table Joins - Should Be An Easy Solution

[MCuthill]

Hi Everyone,

I feel foolish even asking, but my mind refuses to co-operate this morning, so here goes my question:

I have 3 tables (in this scenario)

tbl_BudgetItem
~~item_financetype
~~item_financesub

tbl_FinanceType
~~fint_id
tbl_FinanceSubType
~~fints_id
~~fints_type
~~fints_fint_id

I have joins as follows:
1) tbl_BudgetItem.item_financetype to tbl_Financetype.fint_id

2) tbl_Financetype.fint_id to tbl_FinanceSubType.fints_fint_id

3) tbl_FinanceSubType.fints_id to tbl_BudgetItem.item_financesub

what I want is for only the subtype associated with the parent type chosen to be available on tbl_budgetitem. For example, if a finance type of "Asset" is chosen, only those subtype relateing to Asset are available to be chosen, ie: Equipment, buildings, etc.

Is this a Join issue, or do I use a query as my Combo-Box reference instead of the table?

Thanks All,

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[fneily]

Seems to me that since you have no more info for fint_id in the tbl_FinanceType, then get rid of it. You over Normalized. Plus try to keep names consistent from table to table. So it seems you should have two tables:
tblBudget_item
item_financetype
item_financesubID

tbl_FinanceSubType
item_financesubID
itme_financesubInfo
etc.

Primary key for tblBudget_item is both fields so multi-field primary key.
You can then have comboboxes for both fields on the entry form for tblBudget_item.
So tblBudget_item will keep the association between financial types and subtypes.
The combobox for item_financetype can be a list.

 

[MCuthill]

sorry fneily,

in my haste of posting i missed a field in tbl_FinanceType, there are 2 fields, fint_id & fint_name.

I was using a structure like this to allow linking via number, but displaying the textual value...

I'm not sure if this information changes your response or not.

Thanks for your assistance,

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[fneily]

No. 'Cause all you have is a list of financial types: Asset, Debit, etc. so just use the name and forget giving them a number. One, it's confusing, and again it's over normalization. Sorta like if you have regions East, West, Central, MidWest. You wouldn't make a table to hold just the words. So in tbl_BudgetItem, you'd see the actual name, not a number. Also if you do a search on the Access forums, you'll see you should stay away from primary keys that are autonumbers or integers.
Let's take a look at the tbl_budgetItem. It would then be able to hold:
Asset Building(it's associated item_financesubID)
Debt Building
If the building is fully paid for, it's an asset.
If the building has a mortgage, it's a debt.

 

[MCuthill]

Thanks for the clarification fneily, much apprecitated.

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"