Update Matched Query

[tcjones]

Hi Team I'm wanting to build an update query based on the below criteria:

Table 01 - Cost Centres

cost_centre
C0123AA
C0123AB...
C0987AA
C0987AB...

Table 02 - Conversions

store | new_store
0123 | 1234
0987 | 9876

Where all records in table 01 will be updated if they have a matching record in table 02. Eg the query should transform the C0123AA record to C1234AA. Any help would be appreciated as it's fair to say this has been doing my head in...

Thanks!

 

[gemma-the-husky]

if the highlighted numeric part of cost centre is the only numeric part of the number, and your format is always letter, 4 numbers, 2 letters, then its easy. You can extract the number with the val function, You might find its better to use the mid function, to retain the lead zeros.

So have a query based on cost centre including the original key, and a column called keypart: val(costcentreid) {numeric} or mid(costcentre,2,4) {string}

you should now be able to prepare another query, joining this extract ref to the numeric part of the conversion table

now change this to an update query, and you should be able to update the old cost centre to the new one, by setting it to something like

left(costcentre,1) & newcostcentre & right(costcentre,2). That's why its easier if the format s always ANNNNAA. I would also create another column in the orginal table for OriginalCostCentre just in case anything goes wrong.

Out of interest, are your cost centres already used in relationships in your system, because this will cause a problem, if they are.


if the fornmat is always letter, number,
you should need a string som

 

[tcjones]

Thanks for your help I'm almost there... well sort of. The cost_centre field isn't in any relationships at the moment, and I've got to the point where my second query brings up the affected cost centres based on the join with the second table... However when I try to run the query I'm encountering an "Operation must be an updateable query".

The query looks like the below, where the qry_store_numbers contains a field with the original cost centre and the 4 digit store number stripped out.

Query 01 - qry_store_numbers

store | cost_centre
0123 | C0123AA
0123 | C0123AB...

Query 02 - update

UPDATE qry_store_numbers INNER JOIN tbl_conversions ON qry_store_numbers.store = tbl_conversions.store SET qry_store_numbers.cost_centre = Left([cost_centre],1) & [new_store] & Right([cost_centre],2);

Thanks again for your help!!