join string and text(rephrased)

[Thedon123]

i have wo sets of data.

C code: 1111 and store code :1121

i wish to join these together automatically in another field so as to give

Place : 1111-1121

This code will then form the primary key in the table
how would i do this

cheers

 

[BLeslie88]

2 ways....

Hey bud... 2 ways, I'll give you easiest.

tblNewField(0) = Var1 & "-" & Var2
or
tblNewField(0) = txtVar1 & "-" & txtVar2

This is my preferred tool of choice using the:
Left()
Right()
and Mid() Functions...

Hope this helps....

Regards,

 

[cogent1]

Create a new field in your table to receive the data. Call it Place.

Create an update query and drag the new field into the grid.

In the UPDATE TO: line, put
[C Code] & " - " & [store code].

Square brackets are obligatory!!

leave criteria blank.

Run the query to update the table.

Edit the table in design view and specify Place as the primary key. This will fail if you have duplicates.

You can now delete the two fields you no longer need from your table.

 

[Thedon123]

i sort of understand whhat you are getting at in both the posts.

The problem is i already have data in the field i want to create.

Background. IT is a old database i am trying to update.

It has these fields

parent id: 1111-5555 these is what i am trying to automate
county id:1111 this is id for all the shops in a certain county
store id: 5555 shops are identified by tis id.#

so if i want to search for a shop i would enter 5555 or 5444

but now i want to enter another shop in county 1111. eg 4444

so i enter 1111 in the county box adn 4444 in the store box.

I want this to automaticalyy be placed in the parent id as 1111-4444.

i am probably asking the same question again but please help.

 

[Pat Hartman]

The problem is that you should NOT be storine the ParentID. Fields that can be directly calculated from other fields in the same row should NEVER be stored. They should be calculated in the query that is used as the recordsource for a form or report. Use a two field primary key that includes CountryID and StoreID. To do this, open the table in design view and click on CountryID to highlight it. Then hold the Cntl key and click on StoreID. With BOTH fields highlighted, click the key icon to set the two fields as a compound primary key. Primary keys and indexes may contain as many as 10 fields.

To create ParentID in a query:

Select CountryID & "-" & StoreID As ParentID, .....
From YourTable;

There are occasionally performance reasons for storing summary data but this is not one of them.

 

[cogent1]

Now that you have explained the matter in more detail, I can only agree with the last respondent. I thought you wanted a one-off way to merge two fields.

And indeed, if the combination of county and store is unique and you use both fields for identification only, you could still merge them to give one field.

If either the county or the store data is used independently in other queries, use a double primary key as suggested and use the calculation given to display both fields separated by a hyphen where the ParentID is required.

 

[Thedon123]

thanks