Can't edit due to calculated column. Workaround?

[kandiyohiv]

Table A has a field for Full Name (Bob Jones)
Table B has two separate fields for first and last (Bob and Jones). Table B's contents are actually based on an XML import so I can not just change the table structure.

I want to join the content from these two tables together based on full name.

So first I created a query on table B with a new field created with the expression =[First] & " " & [Last].

I then created a query with table A and query B where I joined the two full-name columns together.

This works perfectly. However, I can't edit the values in this query and I believe from the help files that it's because there's a calculated column in one of the tables.

Is there a way to get around this?

I thought maybe inside my ImportXML VB function once I imported the XML data I could populate an empty column with First + Last but I've read about 50 articles and they're all over my head and I can't figure out how to just concatenate the two columns together into one using VB. It seems if I did this I would avoid having to use a calculated column in a query and this would fix my problems.

Any help would be greatly appreciated. Thanks so much!

 

[Rich]

Why have the same data in two separate tables?

 

[boblarson]

If the query contains any "fields" that are created by concatenation, they will not be updateable. So, you need to base your query on only the tables in the field and in the recordsource of an UNBOUND control, show it as =[FirstName] & " " & [LastName] and you should have controls that show the individual parts for editiing (you could keep them hidden until you clicked a button or something for edit).

 

[kandiyohiv]

Bob,
Could you please expand on that. I don't understand.

 

[boblarson]

What I'm saying is that you can't add any "calculations" or "concatenation" within the query itself and have it be updateable (able to edit or write new data). So, if you want an updateable query, don't put in those concatenations. You say you can't change table B, but you should then change table A so that the names are split (should have been that way to begin with).

 

[kandiyohiv]

Bob,
To expand on that.... Are you referring to using a form when you're talking about an unbound control? I was only referring to updating the query in datasheet view--no forms involved.

 

[boblarson]

Yes I was, as I never work directly in queries or tables (less control that way). So, I don't think you'll be able to join the two tables and actually update one of them directly in the query. You might be able to create an update query, or if necessary, a make table query to create a temp table which you can use to update the table A.

 

[kandiyohiv]

I modified table A so that First and Last are in two separate columns just like table B. And then I built a query and joined First to First and Last to Last.

Is this what you were suggesting with "You say you can't change table B, but you should then change table A so that the names are split (should have been that way to begin with)." ? I didn't realize this would keep the data integrity which is why I was doing the concatonation method in the first place.

However, using this new method the query (in datasheet view) is still uneditable.

Can you offer any suggestion on how to join table A with table B and still keep the resulting table editable?

 

[Maximnl]

use update query

Make first update query for your table B and have the full names good. Then you can join.