Merging multiple rows into one row with several columns

[Oxydo]

Hello everyone,



In a long-term project to update a legacy system we need to create a duplicate output file (output) of the old system, using values made by the new system (input).

This means the following;

1. Several rows from output have to be combined into one row with several added columns in input.
2. Criteria for 'merging' rows are based on the values of several columns in input, if these all match the row can be merged.
3. When there are over 4 identical rows in input for one merge, the data from any of these rows is fine. Result in output is limited to 3 extra columns.
4. A final row in output needs to display the true amount of rows in input.

I've added a simplified and shortened example of the two files in question as an attachment to this posting.

My first instinct, based solely on experience with excel macro's;
1. Concatenate the rows that need to be identical for the merger. Save in temporary row.
2. Create a new table. For each row check if the value in the temporary row has been used before, then write the value in the proper column and update the counter. I'll put it in code tags, only for readability since i dont know the actual code.

if used before (or value found in output) = 1 then
  if col2 is null then
  write value to col2
    elseif col3 is null then
    write value to col3
    else write value to col4
    endif
  else write new row
  endif
increment counter in col5 +1

This would be my introduction into access programming so im a bit at a loss as to how to proceed. Most of the above is for clarifying purposes only. True question is if and how the desired outcome can be attained. Would kindly appreciate any and all input you can provide.

Kind regards,

Oxydo

[June7]

You want to concatenate data from related records. Another way of putting it is you want related records data to display across page instead of down.

This reference http://allenbrowne.com/func-concat.html shows how to concatenate related data to a single value. Not quite what you what. In your case, instead of building a string you want to write to individual fields of a new record. This next reference shows how to rearrange the data for this 'across' output http://forums.aspfree.com/microsoft-...ry-322123.html.

Why do you want to permanently modify data structure like this? This appears to denormalize the data.

[Oxydo]

Thanks for your reply. Ill look into it tomorrow.

As for your question;

We have implemented a new front office system. The table as described in output used to be generated by the old system, and is being used in various processes. During a transition phase, length of which is determined by budget and time constraints, the old table is needed to keep those processes going, while others switch over to the un-adjusted, raw data.