Merging data in a one-to-many relationship

N

NHAnimator

I have two tables: Table1 contains the fields "CaseDigits" and
"OldNotes". Table2 contains the fields "CaseDigits" and "NewNotes". In
Table2, CaseDigits is unique, but there may be numerous matching
records (on the CaseDigits field) in Table1.

I want to eventually eliminate Table1 by appending the OldNotes in
each record to the matching NewNotes field in Table2 (which may
already contain data).

Essentially, I want the following...

[Table2].[NewNotes]=[Table2].[NewNotes]&" "&[Table1].[OldNotes]

So if Table2.CaseDigits=111 and NewNotes="ABC"
and 2 matching records in are Table1.CaseDigits=111 and OldNotes="DEF"
and Table1.CaseDigits=111 and OldNotes="GHI",
then the final result should be
Table2.CaseDigits=111 and Table2.NewNotes="ABC DEF GHI"

This seems like a simple update query, but alas, I have no clue.
Suggestions?

Thanks very much in advance,
John
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

UPDATE Table2 INNER JOIN Table1 ON Table2.CaseDigits =
Table1.CaseDigits
SET Table2.NewNotes = Table2.NewNotes & " " & Table1.OldNotes
WHERE Table1.OldNotes IS NOT NULL
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR8RX8YechKqOuFEgEQJaawCg6kS60N99MSQdtWxuP+cOo+8Cyd4Amwbo
l1UHdrJdjWcREOi+UevA6Ehz
=X3jM
-----END PGP SIGNATURE-----
 
N

NHAnimator

MGFoster,

Thank you. Worked exactly right.

If I could bother you and/or the group for one last modification: The
OldNotes are appended in chronological order (oldest first). Is there
a way to modify the query so that the newest matching entries in
Table1 are appended first. That way, as the user scrolls down through
the new Notes section, they are presented with the combined notes in
newest to oldest order. (See below for my actual code - feel free to
correct. I have added CR/LF's for end-user readability.)

UPDATE [Main Table] INNER JOIN [General Log] ON [Main
Table].CaseDigits = [General Log].CaseDigits SET [Main
Table].GeneralLog2007 = [Main Table].GeneralLog2007 & Chr(13) &
Chr(10) & " " & Chr(13) & Chr(10) & "::: Notes from Old Section :::" &
Chr(13) & Chr(10) & "Date Entered: " & [General Log].DateOfActivity &
Chr(13) & Chr(10) & "Entered By: " & [General Log].PerformedBy &
Chr(13) & Chr(10) & "Subject: " & [General Log].Subject & Chr(13) &
Chr(10) & "Notes: " & [General Log].Notes;

Once again, thank you for saving me time and headaches.

John
 
J

John W. Vinson

I have two tables: Table1 contains the fields "CaseDigits" and
"OldNotes". Table2 contains the fields "CaseDigits" and "NewNotes". In
Table2, CaseDigits is unique, but there may be numerous matching
records (on the CaseDigits field) in Table1.

I would really suggest you reconsider!

Storing multiple notes, in chronological order, in a single Memo field is
probably not the best design. Might it not be better to have a Notes table
related one to many to Table2, with each note in its own record, together with
the date of the note (and perhaps the identity of the author)? This will make
it much easier to retrieve individual notes and the administrative information
about the note.
 
N

NHAnimator

I would really suggest you reconsider!

Storing multiple notes, in chronological order, in a single Memo field is
probably not the best design. Might it not be better to have a Notes table
related one to many to Table2, with each note in its own record, together with
the date of the note (and perhaps the identity of the author)? This will make
it much easier to retrieve individual notes and the administrative information
about the note.

John,

Thanks for the advice. Our current system was set up for the one-to-
many. However, we were storing a lot of information in the Notes
section which has since been incorporated into separate fields.
Therefore, our Notes are now usually only a line or two. Seeing them
all on-screen at once is usually not a problem and is easier than
cycling through a subform that may contain 5 or 6 or 10 records.
Additionally, to add a Note, the user hits an "Add Note" button which
automatically author- and time-stamps the note within (again, to save
the user time.)

Again,
Thanks.
 
J

John W. Vinson

John,

Thanks for the advice. Our current system was set up for the one-to-
many. However, we were storing a lot of information in the Notes
section which has since been incorporated into separate fields.
Therefore, our Notes are now usually only a line or two. Seeing them
all on-screen at once is usually not a problem and is easier than
cycling through a subform that may contain 5 or 6 or 10 records.
Additionally, to add a Note, the user hits an "Add Note" button which
automatically author- and time-stamps the note within (again, to save
the user time.)

Well, for me, that's an even stronger argument for using the separate Notes
table (with fields for authorID, timestamp, and text); if you use a continuous
form then no cycling is needed, you could see all ten notes onscreen
simultaneously (just as with the memo field).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top