If, Then, Else

[Crystal]

I use a table in access to track production of items as they move through each department, with each column heading being the department name, and the value in each field being a date of completion for that department. I would like to have a new column, called STATUS, and a second one called STATUS DATE, where the STATUS field contains the last department with a date value, and STATUS DATE contains the actual date. In other words, I have items, BLUE, RED, GREEN, AND YELLOW, and I have departments, A, B, C, D. I want to be able to look at item BLUE and see that the current status is C, and the status date is whatever date. Can anyone help please?



As a side note, I have accomplished this in Excel by using the IFS formula in Excel, where it checks whether one or more conditions are met, and returns a value corresponding to the first true value.

[kd2017]

It sounds like your table is not normalized, it sounds like what's referred to around here as 'excel brain'. That's not a knock it's just that access and excel handle these kind of tasks differently.

Using your example you should have
1. an items table
2. a departments table
3. a junction table that links an item to a department, also with a date

Then you would simply run a query and get the latest department and date for an item