Combine related records into one field

[AlanP]

Our current relational database has three separate 'notes' tables. Each note is it's own separate record and is related to a master record number (MRN) in the main table (employee information).

We now want to combine all notes for each user (out of all three notes tables) into a single field (one single notes field). Notes contain date (timestamp), who added it, the note itself.

So the new text note field will combine all three notes found in all three tables into one like so (for each employee):

Notes for John Doe

11/1/2009: wahtever

11/2/2009: from second notes table

11/3/2009: from third notes table

Thanks in advance!

[Peter (duksis3)]

Use calculation:

Notes=notes from relation table1 & notes from relation table2 & notes from relation table3 with Text result.

If in each relational table you have several records with notes like "the same ..." before mentioned calculation make global field and fill it with note from each record via looping though them.

Look in sample file.

Notes.fp7.zip

[LaRetta]

Peter's method is a worthy one ... but I must ask ... why aren't the notes combined into one table to begin with (with only a field holding the EmployeeID or category or whatever makes them different)? If they were in one table, there would be no need to combine them. And the (potential) issue I spot is you might want the notes in order of date. Not good and not easily possible with three tables.

If the notes was ONE table, a simple sort and NO globals, calculations or additional relationships would be required. Just a consideration ...

UPDATE: Any time you have multiple tables holding the SAME type of information, it usually indicates that they should be combined into ONE table with a Category or Type field (or EmployeeID field) to indicate why they are different.

Edited November 18, 2009 by Guest
Added update