Updating from Calculated Field

[BNPMike]

This is a basic question.

I want to pull out some substrings and load two new fields. I get my values from:

SELECT Interface_List.ID,
Interface_List.Identifier,
InStr(1,[Identifier],'_') AS Len1,
Mid([Identifier],1,([Len1]-1)) AS T1,
Len([Identifier]) AS LenId,
Mid([Identifier],([Len1]+1)) AS Tk2,
InStr(1,[Tk2],'_') AS Len2,
Mid([Tk2],1,([Len2]-1)) AS T2
FROM Interface_List
ORDER BY Interface_List.Identifier;

If I now turn this into an update query and try to set eg NewField to T2, I find Access has removed all the calculated fields from the SQL so doesn't know what I'm up to.

How do you reference a calculated field in an Update?

[Note: I can work around this by creating a temporary table, joining to that and then referencing actual fields, but I'm sure there is a neater way]

 

[Andrzejek]

Did you try:

[tt]UPDATE Interface_List
SET NewField = Mid(Mid([Identifier],(InStr(1,[Identifier],'_')+1)),1,(InStr(1,[Tk2],'_')-1))[/tt]

Have fun.

---- Andy

 

[BNPMike]

I did think of that approach. However I am a believer that for functional languages (like SQL) you should take advantage of the visibility of what is going on, and burying values in complex functions is just making de-bugging more difficult.

The other reason is that it's so basic, it must be possible without swerving around the place.

 

[Andrzejek]

The question is: did it work for you?

And I agree with you: it shouldn't be difficult. And usually it is not. That’s all depends of how you design your DB and your tables and your fields.
The Identifier field in your Interface_List table looks to be pretty complex, with many pieces that you need to ‘extract’ from it. If you would have a table where every piece of your Identifier would have its own separate field, you may be a lot better off. You can always concatenate the fields to get the whole Identifier (in a query, for example), but it is a lot more difficult to ‘split’ the field into usable chunks.

Just my opinion...


Have fun.

---- Andy

 

[BNPMike]

Ah! It's coming back to me.

What you do is use a view (ie a Query). Save the select query as a Query and then create another query which joins your base table to your query. You can then refer to the calculated fields as if they were real fields and use the values to update your base table

 

[Andrzejek]

What you do is use a new table where you can keep all elements of your Identifier. I don’t know what your Identifier looks like, but if it is something like ‘[tt]ABC_14_XY567[/tt]’ or ‘[tt]XYZ_20_CD789[/tt]’:

Identifier table[pre]
ID (PK) IDTF_A IDTF_B IDTF_C IDTF_D
12 ABC 14 XY 567
13 XYZ 20 CD 789[/pre]

Then based on this table you can create a view (query) where you can concatenate elements of your Identifier and include underscores _ if necessary.

Have fun.

---- Andy

 

[BNPMike]

That doesn't really change anything if you think about it. I'm talking about splitting a field up; you're suggesting concatenating several fields. Either way you have the issue of, if you choose to use intermediate calculations, how do you refer to them in your Update statement?

The source data is not mine. This is often the case in corporate life, so you can't control how it comes in and you don't want to change it more than necessary, to help traceability. Also it is less than optimally practical to always fully normalise your data, so it is not unusual to see database fields that are derivable from other fields despite the increased risk of data inconsistency.

I must admit I first thought of using a view when trying to find a solution on the Internet and being reminded about this business of data duplication. Access's queries are one of its strengths in that queries are more powerful than views in some other databases (eg for updating joins). It solves our problem elegantly but is slightly irritating nevertheless, as in theory the optimiser can take the logic out of the 'view query' and superimpose it onto the rest of the update query, so why can't we do that? I accept that we can't say for certain the optimiser doesn't create a temporary table from the 'view query' but from what I've read about Jet data manipulation, I would doubt it does.

 

[dhookom]

BNPMike,

If this statement was in your first post "The source data is not mine" it might have saved Andy a fair amount of effort in suggesting a solution.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

That’s OK.
Sometimes (in my mind) I operate in a ‘perfect’ world where I can change anything. I know that often that’s not the case. And that is OK.
Creating a view or a query it’s a way to go, in my opinion. That saves ‘dissecting’ the Identifier every time it needs to be taken apart.


Have fun.

---- Andy