I have a newbie question about creating a join relationship between two tables on something more than just the primary/foreign keys. Basic setup is that I have a Groups table that contains inventory grouping information. A group could be "Nuts & Bolts", "Adhesives", "Bulk fiber", etc. There are many inventory Items in each group. This is simple enough, crate a Groups table with a GroupID primary key, and an Items table with an ItemID primary key and GroupID foreign key that references back to the Groups table. So far, no problem. But here is where I'm losing it:
Each group record also has a Level (Number) field. All "raw material" items are assigned to a group whose Level <= 5. Any item assigned to a group whose Level > 5 is considered a "WIP item" that may have subcomponents (also from the Items table). How can I create a join relationship that reveals ONLY items assigned to a GroupID whose Level > 5?
In SQL I would write something like:
CREATE VIEW ItemsWIP
AS SELECT ItemID, G.Level, ...
FROM ITEMS I,GROUPS G
WHERE G.GroupID = I.GroupID
AND G.Level > 5;
I tried creating a global field in Items with a constant value of 5 (g5), along with a new table occurrence of ItemsWIP, linking it back to Groups with the foreign key, but with the additional AND condition that Groups(Level) > Items(g5). This doesn't seem to work, as a layout based on the new ItemsWIP occurrence now shows <Index Missing> for the related Groups record fields.
Also, is my thinking of a "table occurrence" to be functionally equivalent to SQL views even a valid concept?