Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    56

    Unanswered: Doc number series - grouping, etc?

    Hello All,

    I'm stumped regarding the best (cleanest, easiest) way to handle some functionality:

    I have one table that tracks document titles and reference numbering. I'm trying to create for the user a dropdown list that shows the next available reference number for the series. I hope that the following example data will make this clearer.

    TN_Subject TN_Folder TN_Doc Title
    1000 0001 001 Doc A
    1000 0001 002 Doc B
    1000 0002 001 Doc C
    2000 0001 001 Doc E
    2000 0001 002 Doc F
    2001 0001 001 Doc G

    The list I'd like to return for the user in a drop-down control is:
    1000 0001 002 Doc B
    1000 0002 001 Doc C
    2000 0001 002 Doc F
    2001 0001 001 Doc G

    (I actually filter the list on the Subject series (i.e. if user selects elsewhere 1000 series, only those records with Subject # 1000-1999 are shown). Right now, the user must make sure that he/she selects the max Folder or Doc. number, otherwise dupes are created.

    This goal is to allow the user to select the max ref. number for an existing document or folder series to which they want to assign a new document. Example, he/she selects 1000-001-002, indicates Folder or Doc increment, and system returns new ref number for use (if Folder increment, is 1000-002-001; if Doc increment, is 1000-001-003)

    I think I can do this in a series of sub queries. I'm also thinking I can create a table (per multiple queries/update steps) that is recreated when user begins this step but that seems excessive.

    I'm not an expert VBA coder so please try to keep any kind replies as simple and detailed as possible. Eager to hear from all the experts. Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by paulzak
    I hope that the following example data will make this clearer.

    TN_Subject TN_Folder TN_Doc Title
    1000 0001 001 Doc A
    1000 0001 002 Doc B
    1000 0002 001 Doc C
    2000 0001 001 Doc E
    2000 0001 002 Doc F
    2001 0001 001 Doc G

    The list I'd like to return for the user in a drop-down control is:
    1000 0001 002 Doc B
    1000 0002 001 Doc C
    2000 0001 002 Doc F
    2001 0001 001 Doc G
    Not much I'm afraid. Why are A and E excluded? And how are the returned records "the next available reference in the series"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    56
    Thanks for the post. I know this may seem a bit complicated to others...

    A & E are excluded because the next sequence has already been used (Doc B and Doc F) The idea is that just like in a file cabinet, when inserting new files WITHIN existing files, you go to the last related file folder the slip in the new file. Here, the user is locating the last record number and assigning (slipping) in the next correct record number (either by incrementing FOLDER number or DOCUMENT number, which ever is correct for that document.

    Here's more background:

    Form already covers a scenario where the user just wants to assign a record the next avail. SUBJECT tracking number, such as 2002-0001-001. (BTW, I also have an ID field for each record, which is an autonumber counter. These numbers are for physical file management/reference purposes.)

    I'm now working on a second scenario which would be (per the example data above) when the user indicates that the record is in the 2000 FOLDER series, but needs to assign the record to one of several existing FOLDERS or a new FOLDER. Say they later need to go and add 1000-0001-003. I'm trying to make that easier for them to see the options and pick the correct one. Basically, I want the list to show all existing FOLDERS within the selected SUBJECT series, and then for each FOLDER, show the last used DOCUMENT. I know this is confusing.

    Since my post, I've gone and changed the table's Key to include each of the three fields so duplicates will be caught and disallowed. I'm still looking for some way to graphically show the user the list of record numbers so he/she can most easily see what to assign to the record. Any other thoughts?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just thinking through the rest but thought I'd commit this to post before I forget.
    Quote Originally Posted by paulzak
    Since my post, I've gone and changed the table's Key to include each of the three fields so duplicates will be caught and disallowed. I'm still looking for some way to graphically show the user the list of record numbers so he/she can most easily see what to assign to the record. Any other thoughts?
    Not necessary - you can create a further unique index that isn't the primary key - it is an alternate key of the table. In fact it is necessary in the case where you have a surrogate key (as I think your first design had). You can have n unique indexes - the only limit would be the physical limit imposed by Access - I confess I have never had cause to know what this would be.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Something like:

    Code:
    SELECT TN_Subject, TN_Folder, TN_Doc, Title
    FROM MyTable A
    WHERE TN_Folder = (SELECT MAX(TN_Folder) FROM MyTable B WHERE A.TN_Subject = B.TN_Subject) 
    AND TN_Doc = (SELECT MAX(TN_Doc) FROM MyTable B WHERE A.TN_Subject = B.TN_Subject)
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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