Concatenate data within the same query

[Rockape]

Hi all,

Grateful for assistance;

I have a table with two fields.

Table1 = Tel, Remarks

I would like a query which concatenates the data in Tel into the Remarks field, eg.

12345, Happy to be alive

the end result in the Remarks field should be:
Your old tel 12345: Happy to be alive

This should be simple but i'm just hitting a brick wall.

Thanks

 

[maxmangion]

to concatenate you can use the &

="Your old tel" & [TelFieldName] & ": " & [RemarksFieldName]

 

[Rockape]

Thanks for prompt reply,

Should I write this out in the criteria in the design view of the query?

 

[maxmangion]

Hi

No, not in the criteria row. In your query next to the two fields on the top pane i.e. the Tel and Remark, use the third column to create the calculated field and put above suggestion.

 

[boblarson]

Thanks for prompt reply,

Should I write this out in the criteria in the design view of the query?

Not the criteria but as a new field:

YourNewFieldName:"Your old tel" & [TelFieldName] & ": " & [RemarksFieldName]

 

[Rockape]

Hi all,

is there no way to concatenate without having to create a third field?

Grateful if could be done!

 

[boblarson]

You have to create a new field (otherwise it will just do it for you by saying Expr1, etc.). But you can remove the other fields from the query.

 

[maxmangion]

you can untick the check box in the Show row and you won't see when you run the query

 

[Rockape]

hi Bob,

Thanks again.

My remarks field is already in use in several forms. Are you therefore suggesting that I create a third field.

Make a table using this third field

Then use an update query to update the original table with the concatenated data?

Regards

 

[maxmangion]

you shouldn't be storing a calculated field i.e. concatenating the tel and remarks fields as a single field in a table.

As suggested you should create the third field in your query and then use that field elsewhere i.e. forms etc.

 

[boblarson]

hi Bob,

Thanks again.

My remarks field is already in use in several forms. Are you therefore suggesting that I create a third field.

Make a table using this third field

Then use an update query to update the original table with the concatenated data?

Regards

No, I'm talking about a new field within the QUERY. You can create your own fields in queries you use and you don't have to include the individual fields in the queries if the table is in the query. So, for example if I had a table selected in the query and it had two fields - LastName and FirstName and I wanted to concatenate them for showing then I can just have a field I create by putting the concatenation into the field area of the QBE grid and do something like:

FullName:[FirstName] & " " & [LastName]

and that would create a new field, named FULL NAME, within the QUERY (not the table) that I can then use in my forms and/or reports.

 

[Rockape]

Hi Bob,

Thanks again, I think I understand what you mean.

Is there some other way of doing this without concatenation, i.e. I would like to copy the contents of the tel Field onto the contents in the Remarks field.

Perhaps copying the five characters in the tel field and copying them to the corresponding Remarks field!

Any clues.. or suggestions

 

[boblarson]

I would not copy data into the remarks field as that violates relational database concepts. What is difficult about concatenating as needed? You can actually create a single query with all of your table fields and then with the concatenation as an additional field to be able to use with all of your forms and reports instead of the table so that you don't have to do it more than once.

 

[Rockape]

Hi Bob,

Thanks for your comments. I'll do it your way.

Regards

 

[Rockape]

Hi bob,

Grateful if you could look up my posting in vba and modules re selective updates.
Thanks