Data Validation – Rule?

Topic

New Reply

I have struggled with how to ask this question as I am not sure what the method is for solving it. I want to say validation, but its probably not.
In the database there are two fields – Client Priority and Stage . Depending on what is selected in these fields the “status” can only be as shown in the matrix i have attached below.
Where do I start to put this in place?

45992-matrix

I am using Access 2010.

Reply | Quote

Replies

I don’t understand how Client_Priority and Stage are related. For example what if Client_Priority is “Low” (which yields an “Active” Status), but State is “Closed – Withdrawn” (“Closed” Status)?

Reply | Quote

I don’t understand how Client_Priority and Stage are related.

Building on Mark’s comment just from the business POV:

First, it looks to me like the last 2 Priority choices ‘on hold’ and ‘awaiting’ should be in the Stage table–aren’t they ‘stage’ descriptions rather than priorities? A High Priority job could easily be at an Awaiting Client stage, couldn’t it?

Second, what is the purpose at all of the Status field in the Priority table? And if it has some purpose, then it would be different to the Status field in Stage afaics. If they are 2 different things, then you must name them uniquely to avoid lots of confusion for end users, in reports, etc.

Third, it looks to me like your 3-4 fields should be in one table with 3 fields, plus 1 Job field and the Primary key field.
Key – Task – Priority – Stage – Status
001 – Job1 – Standard – Review – Active

Is this a new DB you’re making from scratch, or an existing one you’ve taken over?

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

Lugh and Mark – thank you for your responses. My apologies for the delay in responding. I have been away.

I completely agree with your comment Lugh and have taken that into account. I now have a much simpler table (attached) Now I need to inlcude Mark’s switch expression, but for the new table and some advice on how and where I apply it.
46078-table

Thankyou. Kerry

Reply | Quote

Good point. That’s the kind of discussion I needed to hear.

Actually there is no relation ship between client priority and stage. What I want to make sure of is that if “On Hold” or “Awaiting Client’ are selected the user is prompted or reminded to change the status to “Inactive” and for “Stage” should prompt or remind the user to change the Status to Closed. All the rest do not matter.

Reply | Quote

If Client Priority Status can be derived from Client Priority, and Stage Status can be derived from Stage, why have the user update these Status fields rather than doing it automatically?

Reply | Quote

Status = Switch(Left([Stage],6)=”Closed”,”Closed”,[Client Priority]=”On Hold”,”Inactive”,[Client Priorty]=”Awaiting Client”,”Inactive”,True,”Active”)

Reply | Quote