Lookup multiple values and use both separately

[spudracer]

I have two tables one with all the personnel (tblPersonnel) I'm tracking, another with different report names (tblReports).

For each report, there are different years that it's valid for.

On the reports table, I have three columns, ReportID, Report_Title, and Years_Valid

I'm using a lookup on the personnel table that stores the report name as the ReportID.

What I want to do is in a query use the Years_Valid column to automatically generate review dates bases on dates already in the database.

I've done a lot of different searches, but nothing has been able to really nail down what I'm trying to do.

 

[BlueIshDan]

Could you reword this for me please
There's just something little missing in your description of what you're looking to do.

 

[spudracer]

I really don't know of a better way to word it. Basically, the table with the data I'm trying to create the lookup value for has three columns, ReportID, Report_Name, and Years. I want to be able to not only use the ReportID to pull the Report name, but also use the years for a query I've got.

Instead of doing that, I'm just using an Iif statement to return the years. I'm trying to make this as automated as possible, in the event that something needs added or changed and while I didn't want to use the Iif statement, it's the only way I can make it work for the time being.

 

[BlueIshDan]

Is your years field a delimited string value?

 

[spudracer]

They are 10, 5, 0

 

[BlueIshDan]

Show me your current code, maybe I can get a better understanding with that. lol

 

[spudracer]

I have no code for it. I'm just going to use the IIF statement. I don't see these years changing anytime soon and I've got enough in there that it won't need messed with. Thanks for attempting to assist!