Universal (employee) list to use in any table

[Scott Pon]

I have an employee table, where the employee has a status of "Active" or "Inactive".  I'm trying to create a Value List of Active Employees that I can use anywhere within the database, in multiple tables. 

 

I though I could create a table "TableA" with a Calc Field "Static Active" = "Active"  then create a relationship between TableA->Static Active with Employe Status.  Then create a value list based on TableA.  but that did not work.  I end up with a blank dropdown box.

 

In the same database, I have multiple tables that are going to need this value list.  I originally created multiple instances of the Employee table and linked to each table. but that's multiple relationships, rather than 1 universal relationship

 

But is there a better solution?  Let me know if you need more explanation

 

Thanks

Scott

[Wim Decorte]

Never duplicate data.  If you feel the need for it, then typically something is wrong in your design.

 

You can create a global field in the employee database.  Say it's called gActive.  Set it to the value "active"  Now create a new TO (not table!) of the employees and make a relationship between employees and employees2.  Your new global field to the status field.

 

Base your relationship on that relationship and you will get all active employees.  You can use that value list from anywhere in your solution.

[comment]

The simple solution here is to define a calculation field in the Employees table =

Case ( Status = "Active" ; EmployeeID )

and base your value list on this field.

[bruceR]

I agree with all the  suggestions. It is possible that that the OP meant more table occurrences. Bad enough; but not the same as duplicated data (new tables).

[Scott Pon]

Never duplicate data.  If you feel the need for it, then typically something is wrong in your design.

 

You can create a global field in the employee database.  Say it's called gActive.  Set it to the value "active"  Now create a new TO (not table!) of the employees and make a relationship between employees and employees2.  Your new global field to the status field.

 

Base your relationship on that relationship and you will get all active employees.  You can use that value list from anywhere in your solution.

 

Yes: I'm not duplicating data. 

I thought that was what I was doing.  So I'll try it again.

Thanks for the suggestions.  I will let you know how it goes

[Scott Pon]

Never duplicate data.  If you feel the need for it, then typically something is wrong in your design.

 

You can create a global field in the employee database.  Say it's called gActive.  Set it to the value "active"  Now create a new TO (not table!) of the employees and make a relationship between employees and employees2.  Your new global field to the status field.

 

Base your relationship on that relationship and you will get all active employees.  You can use that value list from anywhere in your solution.

 

IT WORKS!

I don't know why that didn't work the first time I did it.  I must be over thinking it.  Thanks for your help!