Concatenating 2 table fields to form another

[jim11]

Hi all,

Ive got a table called tblClientCar, this is its structure:

ClientCarID
ClientID
CarID

I want to use an append query to concatenate ClientID and CarID together and putting the concatenated value into ClientCarID. How would i do this?

Ive tried using a form a two combos so when i select the clientID from the 1st combo and then the CarID from the 2nd combo, then run the append query to write the concatenated value into ClientCarID of the table. But it says writing 0 rows to table and the ClientCarID isnt concatenated when i check the table. This is the query sql syntax i was using:

INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientCar
WHERE (((tblClientCar.ClientCarID)=[Forms]![frmClientCar].[Combo6] & "" & [Forms]![frmClientCar].[Combo8]));

I also tried another way. The 2 combos add the selected values into ClientID and CarID in the table tblCar. So i made the append query slightly different to concatenate ClientID and CarID to form ClientCarID. This is my query sql syntax:

INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT tblClientCar.ClientCarID, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientCar
WHERE (((tblClientCar.ClientCarID)=[ClientID] & "" & [CarID]));

I get the same result as the 1st query. What am i doing wrong?
Is there another way of doing what im trying to do? Whats the correct sql syntax for doing this?

Thanks
Jim

 

[neileg]

Why? There's no need to store the concatenated data if the fields are in the same table since you can concatenate on the fly anytime you want the value.

 

[jim11]

I know that. But im trying something here and need to know howto do it. U see ClientCarID is the primary key for tblClientCar. And i want to do it this way. Instead of the ClientCarID field been autonumber i want to make it concatinated between ClientID and CarID. It might not be the best way to go about it, but i need it this way. For example if ClientID was 1 and CarID was 10 then ClientCarID would be 110.

Please, i just need info on howto do this?

Thanks
Jim

 

[neileg]

If that's what you want, and it seems reasonable to me, the better way to do it is to make the ClientID and CarID into a composite key. Again there's no need to store the value. Look at Access help on composite keys.

This would also avoid the problems of confusion between Client 11 with Car 10, and Client 1 with Car 110!

 

[jim11]

Yeh a composite key would work, but i really need to do it this way because ive been told to. I know there would be confusion etc etc, but is it actually possible to do it my way and concatinate clientID and carID to form the ClientCarID. All im asking is howto do it? Do i use a query? If so how? I really need it working this way you see, as ive been told to do it this way and cant change it now.

Thanks
Jim

 

[jim11]

Its ok, ive figured it out myself.

INSERT INTO tblClientCar ( ClientCarID, ClientID, CarID )
SELECT [ClientID] & "" & [CarID] AS Expr1, tblClientCar.ClientID, tblClientCar.CarID
FROM tblClientMachine;


Thanks any way m8
Jim

 

[neileg]

Well all I can say is that you've been told to do it the wrong way, it won't work in practice and you'll end up with a mess. Go back to whoever told you to do it this way and tell them they are forcing a bad solution on you.

Nevertheless, the attached db will give you a solution thats based on a couple of lines of code behind the ClientID combo. Enjoy!

 

[jim11]

thanks m8,

i think your right, doing it this way can end up in duplicated entrys if im not careful. Ill try and sort it out.

Thanks alot for the help.
Jim