Database example is attached.
This database is chopped down in order for me to try and resolve the current issue.
Test4.zip
1. I have a table called BizTermXref that contains a two references (ChildBizTerm_ID & ParentBizTerm_ID) to another table, BizTerm (BizTerm_ID). Due to the fact that i have normalized my data, i will need to reference the data from the other tables in succession.
2. So far, the "weirdquery1" shows what i am looking for, but if you look at the tables, 3 of them are not real, but are an accident where i just created the same objects but i suppose they are different instances. (if this has a name, please pass along, i would like to understand how access does this)
3. Query1 is just me trying to show what i am trying to accomplish and it essentially does nothing.
4. I tried using a 1:1 table and place all childbizterm types in their own table, as well as the Parentbizterm types, but one type of bizterm can be either a childbizterm or parentbizterm.
I realize i have the info i am looking for through the query. but I need referential integrity due to the normalizing of the tables, as well as the query data.
Thanks
Wayne Hilburn