Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Don't Allow Duplicates (2003)

    I have a table of Part No's and Serial No's which are typed in on a form. We have had an occasion where the serial numbers have been duplicated, and I have been asked we could run some sort of check. The table is like

    Part No. Serial No
    1A BA 1-30
    1B BA 1-37
    1C BA 21A -38A
    1A BA 31-40

    As you can see, the same part number must have follow on serial numbers, but a different part number can have a serial number that has already been used. This means that we need to do a comparison on the part and the serial number.
    The two main problems are that the Serial No's are text, and we need to be alerted if a user types in say 1A as part number, and BA 33-BA37, as this would fall in the range of BA31-40 as above. Hope this makes sense and hope someone can give me any suggestions.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    Being a relational database, Access will only recognise things like "31-40" as items in their own right; it will not recongnise "31-40" as the range of numbers 31, 32, ..., 40. Ideally, you should have one record for every combination of Part No and Serial No. You can then use indexing to make sure there are no duplicate combinations.
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Don't Allow Duplicates (2003)

    This might be quite complicated to achieve given that you have entries such as 21A-38A. Ordinarily I would have said either redesign the table to have a start and end serial number or just parse out the numbers. In the case of 21A-38A, does that mean that 22B falls within that range or do you only include 21A, 22A, 23A...etc?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    The last part of your reply is correct, we only include 21A, 22A, 23A....etc.
    I don't know if it makes any difference to the problem, but each month the second letter of the prefix changes so next month, we will use BB instead of BA.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Don't Allow Duplicates (2003)

    Say that you have BA 1-30
    Is it OK for someone to enter BB 3-10 next month, or should this still be compared with BA 1-30?

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    Yes, that would be ok. But if someone entered BB 5-8 after someone had enter BB 3-10, we want it to give an alert.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Don't Allow Duplicates (2003)

    Is it feasible to split up your serial number field into 3: Code, start and end? We can work around it if not, but I think it would be easier if you can.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    The problem is that the data aleady exists, so splitting it would be complicated. There wouldn't be any objections if it could be done without disrupting the rest of the data.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Don't Allow Duplicates (2003)

    It shouldn't be a problem to fix the old data in one go assuming it is always of the format "two letters, space, some numbers and/or letters, hyphen, some more numbers and/or letters"? If it can vary, then we will need to know all the possible permutations.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Don't Allow Duplicates (2003)

    You might like to test the attached and see if it seems to do what you want (note: there is no error handling in there; you may wish to add some). If it does, I suggest you make a copy of your database, change the table design and then update the existing data (you will note I actually split it into 4 fields to cope with the 21A-28A type scenarios). If you need any help with queries to do the updates, let us know.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    I've been told that the only reason the letters after the numbers are there at all, is because they realised they have duplicated numbers at the last minute. If we can get a system in place, there will only be a need for the first two letters. There will be occasions however, when 3 numbers are used (AB 100 - 110, ....etc)

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Don't Allow Duplicates (2003)

    The numbers should not be a problem as I assumed they would be long integers. The issue will be when tidying up the old data, if you want to get rid of the letters, what do you replace them with so that your serial numbers are unique? I suspect there may be a manual process involved for that - you will probably just end up giving them different numbers, but will need to determine which numbers are assigned to which entries.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    That could be the problem. Although we don't need letters in future, the ones already done, need to remain for traceability.

  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Don't Allow Duplicates (2003)

    Is this table just to convert the serial numbers? I typed in a number that was already in the table, and it allowed me to do so, adding it to your table.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Don't Allow Duplicates (2003)

    No, it was designed to prevent duplicates. Can you confirm what you typed in so I can test it? (I assume you typed it in to the first text box?)
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 3 123 LastLast

Posting Permissions

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