Unanswered: Update Seq Number from one table to another
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.
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
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.
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
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)