Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: How to check against duplicate values in a subset of data

    Hi all, again. Hopefully one of these days I can answer one of these rather than always ask.

    So here is my issue:
    You have an Access DB (I know, I know ) that tracks problems (Issues). An "Owner" may be assigned. So if Little Jimmy is the owner, he is responsible for the outcome. The issues table has a one to many to an ownership table which has a many to one to an owner table. Each Ownership relates to one owner and one issue. An Ownership also has a priority number assigned. So Little Jimmy and Tom Thumb could both be assigned to the broken computer but Little Jimmy has first priority and Tom Thumb is his back up.
    How do I prevent a user (from a form) for picking Little Jimmy, typing in the priority of 1 then picking Tom Thumb and typing in one again?
    There will be a lot of priority 1's in the table and multiple of them could be placed to one owner but on different issues. So, I'm guessing I need to, maybe, create a recordset that represents the Ownership table as filtered on Me.IssueID then with each update of the priority field, check against other priorities in the recordset. If it matches, throw and error.

    Does this sound right?
    Can I do this without a recordset? it's a very low traffic thing so it wouldn't blow anything up if I requeried the records each time.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Why mot make two combo boxes? Let's say the tech's names are a,b,c, and d. Requerying cb1 will give a list of all 4 names, while keeping cb2.Enabled = False. When "b" is chosen as the 'main', cb1's AfterUpdate can make cb2.Enabled = True, cb2.SetFocus, and then cb1.Enabled = False (you have to do it in that order; you can't disable a control (cb1) while it still has the focus). Requerying cb2 will activate it's row source, which will include a "WHERE tech <> '" & Forms!YourFormName!cb1.Value & "'" clause, to prevent the user from picking the same guy twice.

    I do this all the time; it works wonders for me.

    Sam

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    I think I'm following you but I think you are addressing a different issue. I probably wasn't very clear.

    What I understand you are suggesting is a solution to the possibility of the same tech being assigned to one issue multiple times. Honestly not something I had thought of but a good error check that I will need to add in.

    What I am actually trying to stop is multiple people who are assigned, being assigned at the same rank of ownership. Most of these issues are long standing ones, so you might have Tech A being the primary but in the event he is ill you call on Tech B, so she is also assigned but with a priority/rank of two and so on. I built it as a seperate table and it is a subform so that it could extend on for as many folks as you care to assign.

    Let me know if I totally misunderstood your suggestion, as I have been known to do that before.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I do the same thing with prioritizing production lines for products.
    I have a text box where the user can assign priority. I use the
    field's BeforeUpdate event to make sure that they aren't entering a
    priority number that is already in use. If the number's already in use,
    they get a nice little message, and I cancel the update.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    That sounds like what I am going for, it's the how that is stumping me. I'll try to crack it over the weekend and ask for some more breadcrumbs if I can't get it.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by starkmann
    I think I'm following you but I think you are addressing a different issue. I probably wasn't very clear.

    What I understand you are suggesting is a solution to the possibility of the same tech being assigned to one issue multiple times. Honestly not something I had thought of but a good error check that I will need to add in.

    What I am actually trying to stop is multiple people who are assigned, being assigned at the same rank of ownership. Most of these issues are long standing ones, so you might have Tech A being the primary but in the event he is ill you call on Tech B, so she is also assigned but with a priority/rank of two and so on. I built it as a seperate table and it is a subform so that it could extend on for as many folks as you care to assign.

    Let me know if I totally misunderstood your suggestion, as I have been known to do that before.
    Why not kill two birds with one stone? You can use the two combobox combo, and TELL the user that the tech he picks fron cb1 will automatically be assigned priority 1 (from VBA, for example, or some other way), and TELL the user that the tech he picks in cb2 will automatically be assigned priority 2. That way, he can't pick wrong, and you have the control you need.

    Sam

  7. #7
    Join Date
    Feb 2007
    Posts
    348
    Well, that makes sense. I would want to sort the data by priority anyway with my approach. However, it seems that your approach is either limited by the number of list boxes I make (not extensible) or returns me to the same problem since it would still have to be in a continuous subform. It could be that, once again, I am making it much harder than I need to.
    For most of the cases there will be one and only one owner but there will be exceptions that will track up to five users. Theoretically it could go higher in the future.

    I'm open to suggstions of different ways of approaching it. I haven't managed to work out how I address the subset of information that is held in the subform.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Edit: accidentally hit post and am now too tired to finish the code - sorry <3
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by georgev
    Edit: accidentally hit post and am now too tired to finish the code - sorry <3
    d'oh :-)
    no worries

  10. #10
    Join Date
    Sep 2006
    Posts
    265
    Call me pugnacious but I would first create two Unique key combinations:

    Issue + Technician
    Issue + Proirity

    i.e the specific technician can only be on the Issue once and a specific Proirity can only be be on the Issue once.

    This way the data looks after itself obviously the entry form may need trapping for on duplication. To me data integrity is the essence.

    An aside as a technician and I'm feeling tired and can be bothered you can't downgrade your proirities to issues and elevate others because of the key conflicts.

    Simon

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    why not have a 3 part key?

    SomeTable(TechnitionID, IssueID, Priority)
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by georgev
    why not have a 3 part key?

    SomeTable(TechnitionID, IssueID, Priority)
    ouch, sadly I thought I had hear/read that you could make a concatenated key in Access. After you mentioned it, I googled it and found I was very wrong.

    Thanks very much for the lesson
    oh and I learned what pugnacious means today

    EDIT
    I got all excited and didn't think to test before I thought I was done. The concatenated primary key is cool but I could still have two different people assigned to the same issue at the same priority level.


    I'm trying to think of how to set up "two keys" as simon suggests, I'm not sure I see how that would work. It seems like it fixes the issue but how do you assign two keys to one table?
    Last edited by starkmann; 05-15-07 at 11:10.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe this is called a multiple composite key..?
    Someone please correct me
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by SimonMT

    Issue + Proirity

    Simon
    That was the solution right there. Took some thinking to get back to it. Made it a chore to upgrade the DB but it did exactly what I needed it to do.

  15. #15
    Join Date
    Sep 2006
    Posts
    265
    The other use for Unique Keys (used in conjuction with Duplicate records) is normalising a database.

    The other Utility that is use to to analyse the Database and look at the report for missing indexes. This identifies fields that would benefit the database with an Index.

    These tools keep us honest.

    Another tip I read is to remove the VB off Forms and Reports and put it into a Module, as it is more efficient. And here With CodeContextObject you can forget Forms!YourFormName!cb1.Value when it can be expressed simply as:

    .[cb1].Value the bracket are supposed to be more efficient I would be interested if this is true. I even have to remove all the Combibox RowSource in to a Lookup Module as I had a situation where "Too many databases were open".

    I'm glad this fixed your problem.

    Simon

Posting Permissions

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