Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2003
    Posts
    104

    Unanswered: To Unique or not To Unique

    Hi All,
    I have an interesting problem. The following Image should Help you understand what I am trying to do.

    NOTE: I have tried the following two queries to try ti implement a solution, but none of them work because it will not allow me to add more than one person under operator ID 101 (for example), which is something i NEED to be able to do.

    Ok, here are the queries:

    Creating a unique index...
    Code:
    CREATE UNIQUE INDEX NoDupeActives
    ON OPERATOR
    (
    [Active],
    [Operator ID]
    )
    Removing the unique index...
    Code:
    DROP  INDEX NoDupeActives on OPERATOR

    Can Anyone offer any insight?

    NOTE: Essentially what I am trying to acheive here is I work for a University, and they want to keep track of EVERYONE who ever had access to a student information system, so they want to track more than one person under any given operator ID, but only one of them could be "active" at any given time

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: To Unique or not To Unique

    Originally posted by shassouneh
    Hi All,
    I have an interesting problem. The following Image should Help you understand what I am trying to do.

    NOTE: I have tried the following two queries to try ti implement a solution, but none of them work because it will not allow me to add more than one person under operator ID 101 (for example), which is something i NEED to be able to do.

    Ok, here are the queries:

    Creating a unique index...
    Code:
    CREATE UNIQUE INDEX NoDupeActives
    ON OPERATOR
    (
    [Active],
    [Operator ID]
    )
    Removing the unique index...
    Code:
    DROP  INDEX NoDupeActives on OPERATOR

    Can Anyone offer any insight?

    NOTE: Essentially what I am trying to acheive here is I work for a University, and they want to keep track of EVERYONE who ever had access to a student information system, so they want to track more than one person under any given operator ID, but only one of them could be "active" at any given time
    You need to split off the OPerator to another table ...

  3. #3
    Join Date
    Mar 2004
    Posts
    29
    You are saying that you have more than one user for your OperatorID?

    If you are designing both systems I would re-think that strategy, however if you are just responding to a need created by more users than your 3rd party software has licences for then...

    I would create a table called Users with a UserID Primary Key and the OperatorID as the foreign Key. If your University really wants to keep its access tight (which it really can't with multiple users on one logon, but anyway) then I would add information to the Users Table that sets the schedule upon which each user would be logged on to your third party system.

    Aside from that if your University is trying to iron out the loop holes in their security, they have created an uphill if not impossible battle for themselves by allowing more than one user on the same ID, good luck.

  4. #4
    Join Date
    Nov 2003
    Posts
    104
    My supervisor, which is an I.T specialist, specifically wanted the multiple operators under one ID as a feature. you see, lets say Jane gets assigned an operator ID of 101 and she works from Jan 1st to June 30th, then Alfred works form July 1st to December 31st

    then you would have

    Operator ID: 101
    Name: Jane
    Date created: January 1st
    Date Suspended: June 30th

    Name: Alfred
    Date created: July 1st
    Date suspended: December 31st

    and so on. Essentially the OP Id is used to keep track of users with certain access privelages, etc..

    So i have no choice. I HAVE to have the ability of more than one user per Op ID.


    Thanx for the replies. I appreciate them

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    k i understand what you have there...but whats the aim of your query?

  6. #6
    Join Date
    Nov 2003
    Posts
    104
    Jerrie, Please read above (my very first post in this thread).

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    lol i did read it; however, i didn't understand what you meant by 'adding index' or 'removing index'? i didn't understand that code neither....

    care to explain?

  8. #8
    Join Date
    Nov 2003
    Posts
    104
    Ok. this is essentially What I am trying to acheive (look at the data fragment below):

    OP ID Name Desc.... (Other Feilds) Active (yes/no)
    101 Jessie Kewl No
    101 CrazyKid SomeIdiot Yes

    and so on

    The idea is you have several people under one Operator ID (this has to be so. My supervisor specifically asked for things to be set up this way). However, ONLY ONE PERSON could be active at any time. The way I have my "Active" set up is a check box that can be either checked yes or no.

    Any ideas how I cna acheive this?

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    k i got it; so you want to achieve the ticking off part?
    or you want to set up this kinda system?

  10. #10
    Join Date
    Nov 2003
    Posts
    104
    I want to acheive the following (take a glannce at the picture below)

    see the picture...
    http://shassouneh.homestead.com/file...checkboxes.jpg

  11. #11
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    k i get it totallly; so wouldn't the active current user have a date_suspended thats null or empty?

  12. #12
    Join Date
    Nov 2003
    Posts
    104
    Not necessarily. because you CAN have say 5 users employed at the same time, but only one of them is allowed access through the Operator ID at any given instance of time.

    So you can Have

    Op ID user Name Active? Date Created DateSusp.
    101 SillyBilly No 11-Mar-99 [Empty]
    101 HappySally No 12-MAY-96 [Empty]
    101 MartianLeader YES 1-JAN-02 [Empty]
    101 QuackAttack No 2-SEPT-95 [Empty]
    101 StupidIdiot No 3-AUG-00 [Empty]
    101 WhyBother No 19-JUN-01 [Empty]
    101 BuggerOff No 3-JAN-02 [Empty]
    101 TheyFiredMe No 1-JAN-91 1-JAN-92


    Note: The Last record shows (theyFiredMe) has a suspended date because he/she is no longer employed, but still being kept track of

  13. #13
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    so could you be as specific as possible in listing the criteria for that active? checkbox in being checked?

    this is what i know so far (i think)

    To make active? checked:

    1. Their datesuspended must be empty (is null)

    what other criteria is there?

  14. #14
    Join Date
    Nov 2003
    Posts
    104
    The criteria is that ONLY ONE person under the op id is allowed to be active. So if SillyBilly is made active, then MartianLeader's checkbox is unchecked.

    Aditionally, as you mentioned (correctly), if someone gets suspended, then he/she can no longer be active. And if they ARE active, then their check box is unchecked and they are no longer active.

  15. #15
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    so you have a few options here:

    a. In the query, you type in the name of the person you want to 'activate' and the person who is currently active is unchecked and the person you entered is 'activated'

    b. In a form, a list of all possible activees (possible activees being those who have a datesuspended as null) is shown, with the 'active' person highlighted; you select the person you want to activate, and the previosly 'active' person is 'deactivated' and this new selected person is 'activated'

    c. a random person who is a possible activee is 'activated' while the currently 'active' person is 'deactivated'; this is random

    which is it that you want? i have trouble figuring out what your query needs done....thats all

Posting Permissions

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