Hi Keith. Its a bit hard to follow your description. Perhaps you could post some screen shots of the relationships and give some actual examples to provide a bit more clarity? Chris
I'll try Chris.
I have a table call Menu Choices
It has a DDCategory Field, a DDID Field which is stored in the other table, and a DDDescription field that explains what it is.
Than I have the main job tracking table
That stores a number for the status of different processes, programmed, programQC, pulled etc.
In my layouts I want to display the description from the menu choice table not the status number.
The DDID number can be in the table multiple time, once for each DDCategory. To get the correct description to display I need both to form a relationship.
The original job tracker table does not contain category fields. I have been adding those and set them to a static value with a calculation. Than create a separate relationship for each category.
This works, I just have many occurrences of the same table in my relationship graph. Is there an easier way to do this?
I hope this is more clear
Hi Keith. That makes more sense now - thank you.
Just to clarify:
From your screnshots, if the value in Programmed is 3 and the Category is 2 then the description is "Printer". Is that right?
In your job tracking table screenshot you've got a field called Category15 and a field called Category37. For a particular record - when looking up the description for the value in Programmed - what category would you choose? Does each job tracking record only relate to 1 specific category?
If you only have 1 category for each record then the relationships become easier. See attached sample file.
We're getting closer.
Categories and Processes.zip 67.5 K
This may be a sort of challenge to "make all value list into one table".
You are reading that correctly. If the Programmed was 3 and category 2 the description would be Printer.
To look up the description for Programmed you would use category 15.
Different fields need different categories to find the correct description which is my problem. Category 37 is what is needed to find the description for reship (which isn't showing in the screenshot).
Making all the different table occurrences is working I just thought there might be a more elegant way of doing it