Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    18

    Unanswered: How do you lock num to name?

    I want a specific number to be associated with a particular customer at all times. If a customer is deleted, the associated number is deleted forever as well.

    I have a little (primary) key symbol against both num and name columns in the cus table (Design View) - which I thought would lock them together as a composite key, but nooooooooooooo it didn't do any such thing.

    If I sort either column, the wrong number ends up next to the customer name.

    How do I lock them together?

    Thanks - rev

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Usually if you sort a column it sorts the entire table by that column, keeping each record complete...how exactly are you sorting? And how do you have two primary keys in the same table?

    What you would want is to have the autonumber (or other number) set up as your primary key, and not the customer name.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    When you have two or more fields as part of your primary key Access will only allow unique combinations of those two or more fields. Meaning, that the same customer number could appear x times in your table as long as the customer name for each number is slightly different. Examples:

    1234 John Williams
    1234 Jon Williams
    1234 John William
    1234 J. Williams
    1234 John W.

    All of the above are allowable since they are not quite the same. The purpose of a Primary Key is to uniquely identify one (and only one) record in a table.

    What does your data look like? What is the purpose of lcoking the number and customer name together?

    If set the Customer number to an AutoNumber field then whenever you add a customer a unique number will be generated. When the customer number is deleted it will never be reused. The problem with customer names is that if your user's are not diligent and thorough then you will probably end up with duplicates due to typos, laziness, etc.

  4. #4
    Join Date
    Aug 2010
    Posts
    18
    When the customer calls and gives me his cus_num, I want it to find the correct customer. That's all.

    My cus_num column isn't connected, locked, bound (I don't know the term) permanently associated with the cus_name.

    John Smith has cus_num 0001

    I can sort either name or num and John Smith could end with 0398.

    I would expect the column sort to affect the whole table but it isn't.

    How do I fix it?

  5. #5
    Join Date
    Aug 2010
    Posts
    18
    Chris:

    I was in a hurry before - read your post again. Yeah! The whole table should sort by whatever column you choose, because all the info is associated with that row.

    I thought that was default behavior but it's not happening...

    DC:

    >What does your data look like?

    Assume 3 records:

    Cusnum Name
    001 John Smith
    002 Fred Jones
    003 Jane Doe

    I can sort Cusmum 'Large to small' and get this:

    003 John Smith
    002 Fred Jones
    001 Jane Doe

    All customers (except Fred in this case) have the wrong number.
    Last edited by revnice; 08-14-10 at 09:42.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what a few programs do is generate a customer number out of the name (whether thats company, person, whatever and add a suffix which makes that unique)

    eg

    REVNI12 'that would indicate there are 12 REVNI****
    KUNKL06 'that would inidcate that there are at least 6 KUNKL

    an alternativre

    failing that use an autonumber column that is the primary key, and don't make it a composite key
    also install manual procedures that force/encourage users to make certain that there is no account under a different name
    so when a user searches for a name display all name that match or contain those words. if you want to be a smarty pants also consider using a soundex or double metaphone 'fuzzy match' algolrythm
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2010
    Posts
    18
    Healdem:

    >use an autonumber column that is the primary key, and don't make it a composite key

    That seems like it should work. I'm going to set it up that way and see if I still get the strange sorting behavior. Maybe that behavior was due to something I did and wasn't aware of.

    I'm new and I'm a lot of tinkering in an effort to figure things out. I'm certain I should be able to sort by any column without messing up the info in the rows. That sounds like it should be default, native functionality.

Posting Permissions

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