replace data

[steve111]

hi,

I have 4 tables with a field that holds a "batch number" there can be many batch numbers in these fields

I want if possible to replace a batch number from all my tables that contains that data and replace it with another batch number in one go.

is a macro the only way to do this
if so any help would be appricated
I would like a macro that asks me the batch number to replace and then asks me the number to replace it

steve

 

[CJ_London]

if this is a one off, I would write a query which would be something like

UPDATE myTable1 SET BatchNo=[Enter New Batchno] WHERE BatchNo=[Enter Old BatchNo]


You will need one for each table

 

[namliam]

Simply run 4 update queries... either from a macro or VBA

Bigger issue at hand is the obvious design flaw.... data shouldnt be stored multiple times unless it is a key field
and keyfields shouldnt be changed....

 

[steve111]

hi

the reason there are more than one table is
1. sales create it for ref to a sale as orders numbers are "rolling on" numbers
2 purchasing use it to state what parts were purchased against it
3 stores use it to allocate parts against.

but this is my first database that does so much

steve

 

[steve111]

hi ,

can you please explain where I put this

UPDATE myTable1 SET BatchNo=[Enter New Batchno] WHERE BatchNo=[Enter Old BatchNo]

and do I change my table 1 to my table name

steve

 

[smig]

I 'll go with namilan.
Creating an update query is easy, but seems something is wrong in your design.

As for the question how to create the update query - use the query builder, and set the query as an update one (default is select query).
Put your table in and select the field you want to update.
Put the new value in "update to ' and the old value in the "criteria".
Run the query.

 

[CJ_London]

can you please explain where I put this

In a query - create a new query, go to the SQL window and paste the code in

and do I change my table 1 to my table name

Of course - and Batchno if that is different

 

[steve111]

hi I have put this in the sql but it is not working
have I put it in wrong?

SELECT [Order Details].BatchNo
UPDATE [order details] SET BatchNo=[Enter New Batchno] WHERE BatchNo=[Enter Old BatchNo]
FROM [Order Details];

steve

 

[steve111]

hi ,

I need to be able just to run the query and be asked for the batch number to be replaced and be replaced by

steve

 

[CJ_London]

check my post again - there is no FROM element

 

[steve111]

that's great
you re teaching me a lot I really appreciate it

steve

 

[CJ_London]

no problem!

 

[namliam]

The design should be fixed IMHO
Having one additional table that holds
Key - BatchNo

The multiple tables then have a FK to this table and the BatchNo is centralized in one spot
Possibly central things like BatchDate and BatchSize and more like those things should go into this table as well.

 

[steve111]

ok thanks

this did it in one input

UPDATE ([order details] INNER JOIN [Supplier P/O detail] ON [order details].BatchNo = [Supplier P/O detail].[batch No]) INNER JOIN [Supplier Orders] ON [Supplier P/O detail].[batch No] = [Supplier Orders].[batch no] SET [order details].BatchNo = [Enter New Batchno], [Supplier P/O detail].[batch No] = [Enter New Batchno], [Supplier Orders].[batch no] = [Enter New Batchno]
WHERE ((([order details].BatchNo)=[Enter Old BatchNo]) AND (([Supplier P/O detail].[batch No])=[Enter Old BatchNo]) AND (([Supplier Orders].[batch no])=[Enter Old BatchNo]));

once again
thanks

steve

 

[namliam]

Personally dont like the auto inputs of queries, would make the user input data on a form....

This would also allow you to show the data on the same form before changing it....

Still this is a stop gap measure that doesnt really fix the issue IMHO
Looking at your SQL batchno seems to be your PK, changing your PK is a bad BAD idea...
Instead use a autonumber as your database PK in every table and use your operational PK only for the user and only once... This way you can easily change it anytime anyplace.