Build a field-lookup-query using the value of another field.

Question

Hello!

Creating a mini-database with access, i came across this problem:

For the background, i have two tables:

 Table: Items        and    Table: Actions
ID(PK)    Name             ID(PK)    Name
------------------         ----------------
 1        Thing1            1        Move
 2        Thing2            2        Delete
 3        Thing3

I created a query that lists available actions for each item:

 Query: AvailableActions
Item_ID    Action_ID
------------------------
 1          2                 //Thing1 can be deleted
 2          1                 //Thing2 can be moved
 2          2                 //Thing1 can be deleted
 (no more records)

Now i want to populate a third table that lists the history of objects

 Table: History
ID(PK)    Item_ID    Action_ID
----------------------------------
 1         1          2
 2         1          2
 3         2          1
 4         2          2

So i'm trying to make a lookup-field for Action_ID, where i can only pick values that are allowed for the choosed item. However, be it in design mode or SQL mode, i can't get the value of that field.

Do you have any hints?

Answer 1

Before you sort out the UI (mop the floor...), ensure you have the required constraint on the table (...fix the leak) e.g. ANSI-92 Query Mode SQL DDL:

ALTER TABLE History ADD
   CONSTRAINT fk__history__AvailableActions
   FOREIGN KEY (Item_ID, Action_ID)
   REFERENCES AvailableActions (Item_ID, Action_ID);

...assuming you already have the required unique constraint on AvailableActions (Item_ID, Action_ID).

Answer 2

If you want a list of actions that can be applied to Item X then you can generate this with:

SELECT Actions.ID, Actions.Name FROM Actions INNER JOIN AvailableActions 
    ON Actions.ID = AvailableActions.ActionID WHERE Actions.Item_ID = X

When you talk about deleting "Thing 1", do you actually intend to delete the record from the table or does that record proxy for something else (like a disk file). If you actually delete it, you will have trouble establishing a PK / FK relationship between Items and AvailableActions if that was your intent.

Answer 3

Well as i stated in comments, the only way i achieved this goal is by adding a sub-form to the Items form. (unable to specify this with the structure only, as those Available_actions are computed depending on that same History table)