Bringing it all back home

Reader Sidney Payne (rockwoods@aol.com) used to store data in a now obsolete database that only supported text fields with a maximum of 60 characters. He wanted to store comments that were longer than this for each record, so they were stored across several such fields.

He has imported these comments into a series of text fields in Access and asks whether he can combine the contents of these fields into one memo field, other than by cutting and pasting which, strangely, he fails to favour. Possibly because he has several thousands of records.

An update query is the answer here. On this month's Personal Computer World cover disc the sample file, dbcsept01.mdb, contains a table called Table1 (OK, so my imaginative naming module was offline at the time), which contains three text fields and a memo field. The three text fields contain random text and the memo field is, as yet, empty.

To write the update query, start by building a new query in Design View and add in Table1. Now pop down the Query Type button from the button bar and select Update Query. The double inverted commas around spaces in the Update To cell simply add a space between the contents of each text field; you can, of course, leave out this refinement if it doesn't suit your data.

The SQL view shows that the SQL code is also quite straightforward:

UPDATE Table1 SET Table1.BigOne = [Field1] & " " & [Field2] & " " & [Field3];

When you run the query, Access pops up two messages to check you're happy for the query to modify data in the table. Answer Yes twice (you are working on a copy of your data, aren't you?) and then check the data in Table1.

The BigOne memo field should now contain a concatenated comment. The query is saved as BoltTextFieldsTogether in dbcsept01.mdb.

Incidentally, this query uses the & operator to concatenate the contents of the fields. You could also use the + operator to do the same job, but that has a sting in its tail. If you use + then, if any of the elements being concatenated are null (that is, contain no information), the result is null. The & operator, on the other hand, regards any null field as a zero length string, so the memo field will contain the text from all the other fields.

More togetherness

By one of those odd twists of fate, I received another email asking how to do something similar, albeit subtly different, to the above. Adrian de Souza (desouza@hayesbrook.kent.sch.uk) is looking at ways of generating school reports from a bank of comments along the lines of 'I am pleased to report that <> has been present for all of the lessons this term'. Given the current workloads imposed on his profession, I have every sympathy.

At present, Adrian has the comments in a memo field, the forename in a text field and wishes to insert the name into the comment. I thought about this one for a while, and I reckon that an easier answer is to split the comment into two parts and then concatenate these around the name - as in the FirstSolution table in dbcsept01.mdb.

This is easier than trying to insert a name into a memo field: Access does allow the required level of control over the blocks of text within memo fields - it is just more difficult to do it that way. However, there's still a problem with this single table solution. With the comment and the student names in one table, you'll have to create a new table of names and comments every time a report is required.

A much better and more flexible approach is to put the comments and names in separate tables and create a third table to bring them together in whatever combinations you require. This is not only in accord with the good old relational model, it also happens to be much more flexible, which is why the relational model was developed in the first place.

The first step would be to build a table to contain the comments and a second one for student IDs and names. These are the Comments and Students tables in dbcsept01.mdb. A third table, StudentComment, has a two-part primary key and acts as a joining table between the student and the selected comment. Set up relationships between the tables and add some records to the Student.

Comment table: student 1 gets comment 2, student 2 gets comment 1, for instance. The primary key will prevent the same student getting any given comment more than once.

Now you're ready to generate the complete comment. The SQL looks more complex:

SELECT [FirstComment] +" "+[Forename]+" "+[SecondComment] AS CompleteComment
FROM Students INNER JOIN (Comments INNER JOIN StudentComment ON Comments.CommentID = StudentComment.CommentID) ON Students. StudentID = StudentComment.StudentID;

But that is mainly because it also defines the joins between the tables. The query is available in dbcsept01.mdb as CompleteComment.

This may all sound like a very long way round the houses to reach the answer, but dividing the data between tables and bringing it together with a query gives a highly flexible database that can be extended in a variety of different ways.

For example, suppose that you added a field to the StudentComment table which stored 'Term' information - the allowable values being 'spring', 'summer' and 'autumn'. If you then included this in the primary key, you would ensure that no student received the same comment during the same term. To ensure that the same comment wasn't used for the same student more than once a year, you could substitute a 'Year' field as part of the primary key.

This multi-table solution looks horribly stark as a series of tables and queries; indeed, it cries out for a user interface. A GUI would enable users to choose comments and students from combo boxes, have time/date stamps entered automatically and include any other ease-of-use features you need.

User interface design

Which brings us neatly to the user-interface design that was covered in the June issue. This tiny database had three tables, joined in the same way as the ones above and demonstrates how you can get started building a GUI for this sort of data.

It is on the cover disc as train.mbd. At the time I said that the GUI wasn't perfect and asked for solutions from readers. The response from Steve Morton (smorty@www.smorton.co.uk) is chosen out of several that are perfectly suitable; thanks to all who sent in sensible replies.

Steve liked the article because he had struggled for ages with a similar dropdown list for his 'Squash' database. As a result of the June issue he did that and went on to solve the problem I set: how do you get the combo box to show only the courses for which a person is not currently signed up?

Steve used three queries. Query 1 is based on the table TrainingCourse and the query EmployeesAndCourses:

SELECT DISTINCTROW TrainingCourse.CourseName,TrainingCourse. CourseId,EmployeesAndCourses.EmployeeI FROM EmployeesAndCourses RIGHT JOIN
TrainingCourse ON EmployeesAndCourses.CourseId = TrainingCourse.CourseId
WHERE ((EmployeesAndCourses.EmployeeId= [Forms]![Employee2]![EmployeeId]));

This gives a list of all the courses for a particular employee. Query 2 is based on table TrainingCourse and Query1:

SELECT DISTINCTROW TrainingCourse.CourseId,TrainingCourse.CourseName,
Query1.EmployeeId FROM TrainingCourse LEFT JOIN Query1 ON
TrainingCourse.CourseId = Query1.CourseId;

This gives all the courses for a particular employee together with courses they are not down for. And Query 3 is based on Query 2:

SELECT DISTINCTROW Query2.CourseId, Query2.CourseName FROM
Query2 WHERE ((Query2.EmployeeId Is Null));

This selects the courses for which the employee has not yet signed up. A re-query command is then needed to refresh the dropdown list in the subform when a course was taken up, or a record deleted etc.

In the case of train.mdb this can be done with a macro: Macro1: Requery on Combo20. This is attached to the 'on got focus' of the combo box on the subform.

Steve himself points out that this can probably be solved with fewer queries, and he is right - but there is always a trade-off between elegance (in the sense of getting one query to do the work of three) and readability.

I often prefer readability because it makes the application so much easier to maintain. The complete solution is also on the cover disc as train2.mdb.

Once again, I would stress that this solution is not perfect and could certainly do with 'prettying up'. The forms can be made more attractive, the heading at the top of the sub-form needs work and so on. All of this is very important work - databases need to have attractive interfaces if users are going to be happy with them. However, the mechanics behind the interface seem a more appropriate subject for a database column.

CONTACT

Mark Whitehorn welcomes your comments on the Databases column. Contact him via the Personal Computer World editorial office or email: database@pcw.co.uk. Please do not send unsolicited file attachments.

The full version of this article, complete with screenshots, appears on page 228 of the September edition of Personal Computer World.