Update query to search string and replace with row()

[Guest]

i used MakeTable query to split a table containing an entire year-worth of
data into 12 sub-tables, corresponding to each month in the year. In each of
the tables, I have a column for generic sales invoice number that I made up.
The generic sales invoice number looks lik "SO#_For_Month1" (for Month 1
table), "SO#_For_Month2" (for Month2 table), etc. As you can see, for a
given month, the same sales invoice number is used for all data.

I would like to use an Update Query and do a Search and Replace function to
replace the "#" sign in "SO#_For_Month1" with the corresponding row number in
the table. For example, row 1 in the data table would the "SO1_For_Month1",
row 2 would be "SO2_", and etc.

I want to use <Replace([DataColumnName],"#",ROW()>. However, the Excel
function ROW() does not work in Access.

Does anyone have any suggestions/recommendations?

Thanks.

 

[peregenem]

I would like to use an Update Query and do a Search and Replace function to
replace the "#" sign in "SO#_For_Month1" with the corresponding row number in
the table.

I see a lot of answers to this type of question that go something like,
"Tables do not have an order, you must use ORDER BY in a query." This
is not quite correct.

First, tables have a physical order on disk. In other products this is
called a 'clustered index'; in Access/Jet this the phrase PRIMARY KEY
is used to signify the effect of the non-maintained clustered index.
"Non-maintained" means that you must explicitly compact the file to
physically rebuild the index otherwise date/time order will continue.
Without explicitly specifying an order, this is the order you will get.
In case this isn't clear, this physical order has nothing to do with
the date model and is "non-relational".

Second, ORDER BY uses a cursor or to put it bluntly, is again
"non-relational". When you specify an ORDER BY you are specifying the
columns to give it a relative order. But how does the ORDER BY handle
duplicates? We are back to the physical order, non-relational again.

For a "relational" answer you need something in the data model to give
it the relative order you seek. See my post to your other thread which
uses Northwind's Orders table as an example.