Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Data Validation - Rule?

    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?


    matrix.jpg


    I am using Access 2010.
    Attached Images Attached Images

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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)?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Star Lounger
    Join Date
    Jul 2013
    Location
    Murphy, NC
    Posts
    66
    Thanks
    0
    Thanked 8 Times in 8 Posts
    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?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Status = Switch(Left([Stage],6)="Closed","Closed",[Client Priority]="On Hold","Inactive",[Client Priorty]="Awaiting Client","Inactive",True,"Active")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    kerryg (2016-11-28)

  7. #6
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    620
    Thanks
    166
    Thanked 77 Times in 68 Posts
    Quote Originally Posted by MarkLiquorman View Post
    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.
    ~
    Windows 10 Pro x64 1607; Office 2016 (365 Home) x32; Win Defender, MBAM Pro

    ASRock H97 Anniversary; Xeon E3-1231V3 (like i7)
    Gigabyte GeForce GTX 970; 12GB Crucial DDR3 1600
    Logitech MX Master mouse; Roccat Isku kb

  8. The Following User Says Thank You to Lugh For This Useful Post:

    kerryg (2016-11-28)

  9. #7
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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.
    table.jpg

    Thankyou. Kerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •