Combine several fields into 1 Field

Hey folks,
I have a task of creating a database for our group and I am not an Access Guru.

I would like to make a query that will combine several fields into one field.

Basically what we are doing is we are downloading data from out customer and putting into an Access database so that we can analyze it make it useful for us. However, the customer data is not the best. Our customer web portal is comprised of several manufacturing plant database systems and they are different. So some data is comon and some is not.

Currently we have 1400 records that are downloaded and some of the records look almost the same but are not and need to be treated as seperate. However, one problem we have is that the data does not have a "uniqe identifier" or anything acting as a primary key.

Through Excel we have been able to use several years of data to create a new field that seperates these similar records. Basically we are going to need to use this uniqe number to later compare two similar tables and look for data that has changed. However, my first step is getting the unique number.

I have a table ([DAILY_SKPI_UPDATE]) with these fields and I would like to combine them into just one field.
[DATE] = 11/21/2008
[NAMC] = TMMK-VEH
[QPR/QPI Number] = 1400-3-317-0001
[Tag Number] = 644379
[Record Number] = RO232445 (NULL value is posible here)

I want to write a query that will keep all the data in the table as is and add 1 field on the end that is names [SCRAP RECORD NUMBER] **text only field** and in the example above would result would look like this.

[DATE] = 11/21/2008
[NAMC] = TMMK-VEH
[QPR/QPI Number] = 1400-3-317-0001
[Tag Number] = 644379
[Record Number] = RO232445
[SCRAP RECORD NUMBER] = 11/21/2008TMMK-VEH1400-3-317-0001644379RO232445

Create your new field in the table design view, once that is done, go into the query builder, add your table, and change the query type to an update query (see picture if needed). From here you select the field that you wish to update (which will be your new field). Then in the "Update to:" Cell, right click and go to the "build" option an expression calculator opens up. on the left there are folders. go to tables and then to your table that your working on. From here you will see all the fields for that table. double click on a field name that you want, then type "&" (without the quotes), then select your next field. once you are done you will see the complete expression in the box above. It should have each of your field names with an & between them - [field1] & [field2] & ect....
Run the query and this should be what your looking for.

OK that makes sense.

Question: Do you know how this will treat a NULL field value? I basically want it to ignore it like it does in excel if you use [xxx]&[sss]&... function.

or will this leave a blank space?

Yes, that is exactly what I needed.
However, As expected I am getting huge amount of spaces where I have fields with null values.

You will need to add the nz function to your criteria to eliminate the NULLS from populating. Here's a link on the nz function syntax.

Alan

A nice little site to explain the NZ function.

http://www.techonthenet.com/access/functions/advanced/nz.php

I actually got it to eliminate the blanks with the TRIM Function. Here is the SQL Syntax for those visiting in the future.

UPDATE SkpiUpdate SET SkpiUpdate.ScrapRecordTag = TRIM(SkpiUpdate!Date) & TRIM(SkpiUpdate!NAMC) & TRIM(SkpiUpdate!TagNumber) & TRIM(SkpiUpdate!QPRQPINumber) & TRIM(SkpiUpdate!RecordNumber) & TRIM(SkpiUpdate!PartNumber) & TRIM(SkpiUpdate!Type);

SkpiUpdate = TableName
ScrapRecordTag = Field that I am updating in the Update query.

In the code TRIM(SkpiUpdate!XXXX)
XXXX = The field names that are being combined into ScrapRecordTag.