Using compound primary keys

Not just a database problem
Oliver Metherell leads Super7, an attempt to make a first ascent on every one of the seven continents (we are talking mountain climbing here).

The current status is five done, two to go. Oliver presently maintains a mailing list in Word and feels now is the time to move up.

Excel and Access could both be excellent candidates, depending on your data, expertise and circumstances. I’ll demonstrate the Access path.

Oliver’s email list is stored in Word like this:
Foo ; foo ;

The first issue is that multiple addresses can be held in a single line in the Word document and we’ll need to standardise on one per line. This problem is most easily solved in Word using the Replace utility.

Set up the Replace utility with a semi-colon followed by a space in the ‘Find what’ box and type ^p into the ‘Replace with’ box.

This will find the semi-colon in the middle of a line and the ^p will replace it (and the space that follows it) with a paragraph break. This ensures all email addresses are on different lines.

Then run Replace again, but this time searching for just a semi-colon and leaving the ‘Replace with’ box blank. This removes the semi-colons at the ends of lines. The result is:
Foo
foo

Now we need two passes through the data with the Replace utility to find and remove (by leaving the ‘Replace with’ section blank) the < and > characters. The result is:
Foo foo@baa.co.uk
foo footoo@baatoo.com

Now for the data transfer. In Access prepare a table to hold the data. In our database, the table Mail has two columns: one is an AutoNumber primary key field and the other is called EmailAd.

Highlight the list in Word (Ctrl &A), copy (Ctrl & C), then swap to Access. In the datasheet view of the Mail table, click in the EmailAd column header and then paste (Ctrl & V).

Now we can use Access’ data manipulation capabilities to finish the task by removing the names. The key to solving this problem is that there is always a space character between the bit we don’t want (the name) and the bit we do want (the email address). All we have to do is to find that space character and then take all the characters from that point to the end.

Make a copy of the table and we’ll start work on it. Base a query on the Mail table and add the EmailAd field. We find the position (counting from the left) of the space character using a function called InStr (short for InString). In a fresh column, build an expression called PosSpace:
PosSpace: InStr(1,[EmailAd]," ")

InStr needs to know from where you want it to start counting, the column to be searched and the character you seek. We’ll start at the beginning of the string with character 1, though you can omit this argument as the function’s default behaviour is to start at 1. This is the SQL:
SELECT Mail.EmailAd, InStr(1,[EmailAd]," ") AS PosSpace
FROM Mail;

Now we know where the spaces are. There is another function called Mid that will chop up a string. You tell it the start position and how many characters you want to chop out. Now, as a first guess we might try:
CleanEmail: Mid([EmailAd],[PosSpace],100)

This says: take the string which is in EmailAd (Foo foo@baa.co.uk), go along to the position shown in PosSpace (which happens to be 4) and then chop out the string from there to the end; technically, it chops out from there to position 100, but Oliver had no strings longer than 100 characters.

This works but it includes the space from which we are counting, so:
CleanEmail: Mid([EmailAd],[PosSpace]+1,100)
works better.

It is left as an exercise for the reader to work out how we could isolate the name and put it in another field ­ hint: a function called Left is very handy here.