Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Bradford, Yorkshire, England
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query help reqd (ACC 97)

    Is it possible to create an update query which can loop through data?

    My scenario is that I have 2 outlets that are updating 2 standalone databases. I now need to be able to merge the data from the 2 sites. The main table to be populated with the records contains a transaction number. There can be more than one row with the same transaction number. I need to be able to "update" the transaction number in the new data to follow on in sequence with the main data set?

    Unfortunatley the new data cannot all have the same transaction number, as this is used for reporting.

    Any help would be greatfully received

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

    Re: Update Query help reqd (ACC 97)

    Can't you use an AutoNumber field as transaction number? It'll increment automatically, and is guaranteed to be unique.

    If not, could you please explain in more detail what you want to accomplish?

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Bradford, Yorkshire, England
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    Hi Hans thanks for the quick reply

    A bit of background, the data records stock transactions, but each stock item needed to be recorded as an individual record. The same transaction id is created for each stock item that is taken at the same time.

    eg a typical records consist of
    trans id
    contractor id
    stock item ref
    quantity

    What I have been asked to do is import data from an identical standalone database but update the trans id for each records so that they follow in sequence of the main datatable (the one they are being imported to) They could potentially have data with the same transaction id.

    Hope this makes sense, I know what I want to do - I just dont know how to achieve it

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    <P ID="edit" class=small>(Edited by NYIntensity on 20-Jul-05 01:21. Edited to add an example.)</P>Would two tables offer a better setup? Something like this:

    Table 1:
    Transaction ID - Primary Key
    Contractor ID

    Table 2:
    Transaction ID - Not a primary key, but linked to table one, so on a subform, etc you will be able to see all stocks related to this transaction
    Stock Item Ref
    Quantity

    Does the example attached sort of do what you'd like?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Bradford, Yorkshire, England
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    Hi Jeremy

    2 tables would probably be the best solution however this would involve reeingineering the whole database. I will try to attach a sample of the database to see if that explains a bit better what I am trying to achieve.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    Okay. Through queries/etc hopefully with dummy data we could make this a fairly painless process... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Bradford, Yorkshire, England
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    Hi Jeremy

    I have attached a very small sample of the db, this includes the data entry form that user uses to enter the data. A sample table of data that I wish to update and append.

    I want to update the the transid of the data of the "data to be appended" I presume this will be via an update query.
    I would like the updated transid of the records to continue to follow the sequence of the data that i am appending to

    Hopefully this makes sense

    Thanks in advance for any help, guidance etc

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

    Re: Update Query help reqd (ACC 97)

    What is the table you want to append to?

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Bradford, Yorkshire, England
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    Hi Hans

    tblStockDistribution

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

    Re: Update Query help reqd (ACC 97)

    You can update TransID in the Sampledatatobeupdatedandappended table and then append the records to tblStockDistribution, or you can append the records and update TransID while doing so. In both cases, the expression to calculate the new TransID is:
    <transID>+DMax("TransID","tblStockDistribution")-DMin("TransID","Sampledatatobeupdatedandappended") +1

  11. #11
    Lounger
    Join Date
    Jan 2001
    Location
    Bradford, Yorkshire, England
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query help reqd (ACC 97)

    Hi Hans

    Thanks for the help so far. I am now 95% there. That gives all the new records the same "updated transid".
    Would it be possible to "loop" through the data so that if the transid increases in the "sampletobeupdated table" it also increases when the update query is run.

    eg data to be updated transid is 10211 - new updated record transid is 26518
    data to be updated transid 10212 - new updated transid is 26519 and so on

    Im presuming I will have to create a routine where I enter the old transid and update it for each record

    Thanks so much for the help given so far.

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

    Re: Update Query help reqd (ACC 97)

    I'm sorry, but that doesn't make sense to me. The idea is that you adjust the TransID once to be able to append the records to tblStockDistribution and still have unique TransIDs. Trying to update the TransIDs continually would cause a large overhead and is superfluous.

Posting Permissions

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