Copy multiple record from one table into one record in another table
I have a problem I can't seem to figure out, and was hoping somebody here could help. I have a database of skeletons, that contains a table into which records were added through a portal. The table (FracturebyBone) records observations of fractures per long bone, and each observation is one record. The fields are SkeletonID, FractureID, Bone, and FractureCode - i.e. each SkeletonID will have multiple Bone records: left humerus, right humerus, left ulna, and so on.
I need to copy these records into a new table, (FracturebySkeleton), where each SkeletonID has only one record - i.e. the new fields should be SkeletonID and then one field for each bone (HumerusLeft, HumerusRight, UlnaLeft etc). I want to copy the FractureCode into the corresponding fields in the new table. The tables are related through the SkeletonID.
Is there a calculation I could use in the FracturebySkeleton bone fields that would automatically copy matching records to the correct field from the FracturebyBone table? I tried setting the fields up as calculations like this (using the FracturebySkeleton::HumerusLeft field as an example):
If ( FracturebyBone::Bone = "Humerus Left" ; GetAsText ( FracturebyBone::FractureCode ) )
but it only works for the first field in the table, the others remain blank.
Thankful for the help!