Combining two text columns into one in a table

Galfredus

New member
Local time
Today, 15:24
Joined
Aug 16, 2013
Messages
2
Hello!

I have a query that I hope is very simple to answer - I'm not experienced at working with databases but I have been given the task at work of exporting an old Lotus Approach database into Access. I think the above query is the key to me doing this.

My Approach database contains records relating to nearly 800 sites in London. There are radically different amounts of data held about each site. The database contains a lot of different tables, each containing a different class of information. Not all the sites listed in the database have information in all of the tables.

The unique thing that holds it all together is the unique site reference number. Unfortunately this is split into two separate columns in the Approach database, "Reference" and "Suffix". Not all sites have a suffix. (The purpose of the suffix is to identify sub-sites which are subordinate to the main site reference, but need to have their own individual records.) Because many sites have no suffix, most of the fields in the second column are blank.

In order to link all the tables together in access I need a Primary Key which is unique to each site. In this case the reference/suffix number is the obvious (only) candidate. Access help tells me that there is no problem using two different columns to create a primary key. The problem I face is that it doesn't like the fact many of the fields in the second column are blank.

My solution to this is to combine the two columns into one. That would give every site a unique reference, and none of the fields in the combined column would be blank. Can I work out how to do it? Can I heck! All I want is a new column that displays the reference and suffix (if any) in a single field, no spaces.

All advice on this matter is gratefully received - non-technical explanations particularly welcome.

Thanks!

Geoffrey
 
Hello,

You can fill all blank values in the second column using an Update Query with the function Nz :

Code:
UPDATE YourTable SET YourTable.Field2 = Nz([Field2],0);
Here, all blanks will replace by 0. You can choose the character to use in place of blank.



Good continuation
 
Thanks madefemere! I love the way there are always at least two different ways of achieving the same outcome with this thing. Thank you for taking the trouble to reply.
 

Users who are viewing this thread

Back
Top Bottom