combine multiple records based on common field

[AndyTilia]

I have two tables, simplified here:

1) tblVessels
1000+ records
fields: Vessel_Name, Incident_Date

2) tblProblems
2000+ records
fields: Vessel_Name, Incident_Type, Incident_Severity, Incident_Cause

*There's a one-many relationship from tblVessels to tblProblems. Ex:
tblVessels: Titanic, 9/18/2001
tblProblems: Titanic, Sinking, Total Loss, Iceberg
Titanic, Gear Loss, Severe, Vessel Sway
Titanic, Loss of Life, Severe, Drowning

*I'd like to work instead with only one table; adding a third field to tblVessels like this: Vessel_Name, Incident_Date, Notes. And then combine the 'many' side of the relationship into that new field so my new record is:

tblVessels: "Titanic", 9/18/2001, "Sinking-TotalLoss-Iceberg/GearLoss-Seve-VesselSway/LossLife,Sev,Drown"

You see? The separators in the new notes field dont matter, just as long as I can look at the 'many' related records in one field (prob. type memo)

I realize that I am taking someone else's very well designed database and totally breaking DB rules by wanting to do this-- but its for the sake of simplicity! Thanks in advance for any help---

Andy

 

[Nancy2]

Your right! You are denormalizing your database.

Question - are you looking for ease in data entry or in reports? If reports, you probably could create a report to emulate this format.

If data entry, and you really want to do this, I would:

1) Add the new field to the Vessels table
2) Create a vba routine which loops through the problems table and updates the new field in the vessels table - each update should concatenate the existing value to the value you are feeding it from the problems table.

 

[AndyTilia]

Nancy2, Of your 2 suggestions, I think the second is better--

Why?? I'd like to sin as such, not necessarily for data entry reasons, but because I'm pulling this info from someone else's DB. My 500+ existing Vessel records have this sort of information in a more narrative form in the Notes field.

For instance:"Kimton", 3/14/1998, "aground in hurricane, hull damage is extensive, some debris scattered on beach"

I'm bringing in the records described above into my existing DB.... Also, I'm accessing my DB through ArcView GIS software and I havent yet found a way to look at subform/subreport info there.

Does this make sense? Does it still sound like a kooky thing to want to do?

I have limited VBA experience; Nancy2 do you or anyone out there have a simple outline I could follow??

Thanks--

 

[Nancy2]

Ok -- quick shot at it - might need some debugging. You need to put this code in a module. And, I assume you added a field names Problem to tblVessel.

Good luck.


Sub ConcatenateVesselProblem()

Dim dbCurrent As Database
Dim rstProblems As Recordset
Dim strFeedProblem As String
Dim strFeedVessel As String


Set dbCurrent = CurrentDb
Set rstProblems = dbCurrent.OpenRecordset("SELECT Vessel_Name, Incident_Type, Incident_Severity, Incident_Cause FROM tblProblems&quot


rstProblems.MoveFirst



Do Until rstProblems.EOF


'This brings all the problem fields together with spaces in between

strFeedProblem = rstProblems!Vessel_Name & " " & rstProblems!Incident_Type & " " & rstProblems!Incident_Severity & " " & rstProblems!Incident_Cause

strFeedVessel = rstProblems!Vessel_Name

' This updates tblVessels with problem if the vessel_name matches

dbCurrent.Execute "UPDATE tblVessels SET Problem = " & strFeedProblem & "WHERE Vessel_Name = " & strFeedVessel & ""

Loop ' brings it back to the top to start on next record


End Sub

 

[Nancy2]

Oops- about to delete the code from my window when I noticed an error -- it needs a MoveNext prior to the loop

end of code needs to be:

dbCurrent.Execute "UPDATE tblVessels SET Problem = " & strFeedProblem & "WHERE Vessel_Name = " & strFeedVessel & ""

rstProblems.MoveNext

Loop

Sorry!

 

[AndyTilia]

Thanks for the help, Nancy2--

I've gotta run now but I'm going to try it out first thing next work day (Thurs)-- Looking at the VBA code: I feel a good learning experience coming up!

Andy

 

[Nancy2]

A couple more hints if you are brand new:

1) Try this on a copy (sure you already know that)
2) From the copy, delete all but one or two vessels - so you can debug with limited data
3) From the module window, click on the sub name and click Run, Go (or use the toolbar) to run it.
4) To debug it, insert the word stop in your code - when it runs it will step through the code - hit f8 to run through each line. You can check what is in the variables by hovering the mouse over them - they will appear in yellow.
5) If you get stuck in the loop or just want to get out of the code, press CTRL Break.