Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    31

    Unanswered: Executing queries on click of a button

    I am trying to generate a reference number within an access form.

    The form has about 7 combo boxes which have various categories, and these categories have two characters associated with them, for example "Call Handling" is CH.

    There'll also need to be a unique number so when there are 7 values the same, it increments.. I.e. CH-CC-1, CH-CC-2, CH-CC-3 etc.

    This is then stored as a unique reference number (URN) in the main table.

    What I thought of doing, was to split it into two fields, a categorisation, and number field. The number field can be declared as an autonumber, and then the categorisation is filled with the CH-CC etc.

    However, I'd really like it so the autonumber is only incremented for that category, so: CH-CC1, CH-CC2, CG-FF1, CG-PL1 etc.

    I thought about running a query every time its about to be added, finding the highest number for that category (i.e. SELECT number FROM ... WHERE [Category] = 'CH-CC' etc.). Is this the best way of doing it?

    Either way, anyone have any suggestions?

    Thanks,
    Paul

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    The values CH-CC=1, CH-CC=2 are you trying to count the instances of CH-CC?

    Is there another reason you do not want to store the ID from the Call Handling table in the main table?

    Normalisation would suggest that instead of adding one number you add the relevant ID(s) for Call Handling Table to the main table. In this way if the CH is changed to AH all CH values would change.

  3. #3
    Join Date
    Mar 2002
    Posts
    31
    Kind of

    I'm trying to generate unique numbers in sequence, however, they need to be sequenced for that category code.

    So, CH-CC-1, CH-CC-2 etc. Then, once a new category code is added: CH-GG it goes back to 1 and increments again.

    In the end I found out how to execute a query, so I managed to determine the previous highest number for a given category code, incremented it by one and then stored that code in a field.

    Not sure if its the best way of doing it, I always feel dubious of when I have to bodge it together

Posting Permissions

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