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.
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