Update Query help reqd (ACC 97)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

(Edited by NYIntensity on 20-Jul-05 01:21. Edited to add an example.)

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?

Reply | Quote

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.

Reply | Quote

Okay. Through queries/etc hopefully with dummy data we could make this a fairly painless process…

Reply | Quote

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

Reply | Quote

What is the table you want to append to?

Reply | Quote

Hi Hans

tblStockDistribution

Reply | Quote

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:
+DMax(“TransID”,”tblStockDistribution”)-DMin(“TransID”,”Sampledatatobeupdatedandappended”)+1

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote