How to concatenate 3 fields?

[nicoboss]

Hi,

I am a beginner in access.
I have several access files. They all contain only 1 table inside named table1. In this table, there are 9 text fields with millions of entries.

In table1, the first 3 fields are named "ID1", "ID2", "ID3".
The other fields are named "Valeur1", "Valeur2", "Valeur3", "Valeur4", "Valeur5", "Valeur6".

I need to concatenate the fields "ID1", "ID2", "ID3" to build one and only "ID" field which will contain unique values.
We should get the values in the "ID" field by concatenating the values of "ID1", "ID2", "ID3" using this format : ID1_ID2_ID3 which means that the value in "ID1" field should be followed by "_" followed by the value in "ID2" field followed by "_" followed by the value in "ID3" field.

The other fields "Valeur1", "Valeur2", "Valeur3", "Valeur4", "Valeur5", "Valeur6" should remain unchanged.

I have been told it was easy to do but I have googled a lot about that and I still can't find how to do that.

Can anyone help me please?



Thank you

Nicky

[June7]

Expression in query.

NewID: ID1 & "_" & ID2 & "_" & ID3

[nicoboss]

Hi June7,

Thank you for helping me.

This is the exact formula?
Should I create the field NewID before?
Or just paste this formula somewhere and run it?

[June7]

It is example of expression in query builder Design View. Use whatever you want for the alias field name. I used NewID just as an example. This is basic Access functionality. Have you completed an introductory tutorial book?

It does not modify data in the table. It just manipulates it in SELECT query.

[nicoboss]

It does not modify data in the table. It just manipulates it in SELECT query.

but after creating the "ID" field, I need to remove the "ID1", "ID2", "ID3" because my final table needs to fit a given format (1 ID field + 6 "Valeur1", "Valeur2", "Valeur3", "Valeur4", "Valeur5", "Valeur6" fields).
So I am not sure this is the right way to go. What do you think?

[Bulzie]

Just to ask from your OP

I have several access files. They all contain only 1 table inside named table1. In this table, there are 9 text fields with millions of entries.

So you have multiple databases and they all have 1 table in them like you described above and each table has millions of records in them? Is new data going into those database tables still? Once you create the dataset with the combined ID field, what then will you do with that data?

[nicoboss]

I need to create a new table, not just a query.
The aim of all this is to use this final access table as an input for a software which only accepts access files containing only 1 table with 1 ID field and 6 other "Valeur1", "Valeur2", "Valeur3", "Valeur4", "Valeur5", "Valeur6" fields.

[nicoboss]

thank you guys, I got it !

[Bulzie]

Maybe just add a new field/column to the table called NewID (text field). Then create an Update query, add the table, select NewID field in the column and in the UpdateTo box, put the calculation June7 gave: ID1 & "_" & ID2 & "_" & ID3

Run it and it should create that field for you.

[nicoboss]

I used a "create table" query. thanks a lot for the help !