Update queries (Access 97)

Topic

New Reply

Hi – can anyone help me with this? I have some VBA code which is first of all creating a table called PricesHold where information from a larger table has a company code “D”. At the moment the database then changes that company code to “G” via an update query:

“dbs.Execute “UPDATE tblPricesHold SET tbl PricesHold.Company = ‘G’ WHERE (((tbl PricesHold.Company)=’D’));”

What I am now being asked to do is to create 3 companies, G, A and B from the original company ‘D’ data (essentially taking the information from D, and creating the same information 3 times, but with different company codes. Anybody got any ideas how I can create an update query that does this, or will I have to work around??

Regards to all. Marion

Reply | Quote

Replies

One solution would be to use a Make Table query and simply set the Company Code as an expression. But Make Table queries don’t do expressions very well, so you would have to run an update after the fact to change field names in each table. A better solution would be to Copy the existing table and then paste it with the option to NOT copy the data. Then create an append query which copies all of the records for the “D” table, but uses an expression to set the new company code, and run that query 3 times. (I made an assumption here that you have to have 3 different tables. If you can do it all in the existing table, then all you need is the append query run 3 times with the different company codes.)

Reply | Quote

Thanks Wendell I managed to get a work around to this. Many thanks

Reply | Quote