Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    14

    Unanswered: Update Seq Number from one table to another

    HI,
    Thanks for viewing my question. I have a table (table_A) with a numeric sequence number that I need to update that number from another table (table_B) that has many records. I need the highest value sequence number in table_B to update/replace the sequence number in table_A.

    Thanks in advance for any help.

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    more clear details pls

    this post has been here for a few days without any reply, i did read but not understand exactly what you meant. can you pls give us some more details

    cheers
    qha_vn

  3. #3
    Join Date
    May 2003
    Posts
    14

    Re: more clear details pls

    Originally posted by qha_vn
    this post has been here for a few days without any reply, i did read but not understand exactly what you meant. can you pls give us some more details

    cheers
    Sure can .... Table_A has a sequence number (let's say 1011) that was used in table_B as a starting sequence number that was incremented for however many records are in table_B (varies). For example, there are 5 records in table_B - the 1st record will have a sequence number starting with 1011, the next record will be incremented to 1012, the next 1013, and so on until the last record which will be 1015). I need that last sequence number ((1015) + 1) to replace the sequence number (1011) in table_A so that the next time it's run the new starting sequence number will be 1016. I'm hoping to do it with an update query. I hope this make's it a little clearer.

    Thanks for the reply.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is it good to have the two tables talking to each other in the way you suggest????

    anyway, some thoughts:

    you can find the top number with:
    DMax("[theFieldName]", "theTableName") 'use your field/table names!!
    and +1 will give you the next.
    if you are multi-user, i can see this scheme giving duplicate sequence numbers if the timing is unlucky.

    is it better to add a tableC which contains an autonumber field: tableA and tableB take the next number available from tableC?
    tableC could also keep who, why, when, whichTable stuff as well.
    then .addnew with !who=YourName allocates a sequence number exclusively to you. the alternate form of the DMax function:
    DMax("[theFieldName]", "theTableName", "[who] = 'YourName'")
    will give you YOUR new sequence number whatever other users are up to.
    afterthought: so will myNewSeq = !theSequenceField

    must you share the sequence number between two tables?
    why not a continuous sequence in tableA. tableB has a lookup to the tableA sequence plus it's own internal sequence number: the full sequence number of tableB is the combination of the two, so instead of:
    tableA: 1, 2, 3, 6, 7, 8, 10
    tableB: 4, 5, 9
    you have:
    tableA: 1, 2, 3, 4, 5, 6, 7, 8, 9 etc
    tableB: 3.1, 3.2, 8.3 (if you autonumber tableB internal sequence)
    (or tableB: 3.1, 3.2, 8.1 if you 1+DMax tableB)


    izy
    Last edited by izyrider; 06-13-03 at 12:18.

Posting Permissions

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