Topics Search

Merging multiple rows into one row with several columns

Merging multiple rows into one row with several columns
Views: 39
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.
Sponsored Links:

Similar posts...


After finding duplicates, how to update row?

After finding duplicates, how to update row? Icon
I have a large employee table that is appended through 6 queries. After all of the append queries are run, there are tons of duplicate rows.

I want the duplicate rows to be condensed into one row per employee.

When all of the rows are appended, I end up with employees with multiple rows -- but one row has a value for ID1, one row has a value for ID2 and another row has a value for ID3, etc. There isn't a common business rule to indicate when a certain employee would have multiple rows so I can't make the SSN a primary key and have the append reject the duplicate rows.

Two Remove those rows in a table which has already occured for a particular value

Two Remove those rows in a table which has already occured for a particular value Icon
I have one table which consits of 5 columns . I have imported it in access database.

I have two columns say column A and D which consists of integer values and the values of the columns are say in the range 1 - 100 ( both the columns ). so there is a row say in column A value is 38 and in column D corresponding value is 37.

So now in the output there should not be any other record with having value 38 and 37 either in column A or D .ie. all those rows which contains these values other than this rows should be omitted.

Similarly for other rows and values also .I tried with the following query but didn't worked

Update query will not update my first row of data

Update query will not update my first row of data Icon
Is there a trick to why the update query will not update my first row of daya, but all other rows work correctly? I have 12 update queries that update 12 columns on table 1. There are 20 rows per column. Rows 2-20 update perfectly on all 12 columns, but row one on each query does not--all the same value (dollars). I've verified that the update query and destination table names are exactly the same as well. I'm stumped

Converting Rows to Columns

Converting Rows to Columns Icon
In my Access(2003)Database,I need to convert Row data to Columns.

For ex-E1

should be converted to E1 E2 E3 as Column headers.

Can anyone help me with some solution for this Conversion of Row data to COlumns.

Hiding row data from a report

Hiding row data from a report Icon
I have a sub-report that shows data from two tables joined together. As there are multiple results shown on my report, is it possible to filter out the ones I don't want too see?

For example I have 7 rows showing on the sub-report which is what I need, but I want to add a button next to each row of data and an onClick event to allow me to hide the row from view andallow me to still see the other 6 rows? I don't want to delete this row, just hide it, there will be times where I need to hide multiple rows at the same time.

Moving and adding rows of data

Moving and adding rows of data Icon
I have a form with a dozen rows of data in the center of the form. Each row has seven columns: a label and four to six text boxes that display data from a table. I want to insert new rows with similar data at the top of these columns, moving the existing rows down.

How do I insert the desired rows without moving EVERYTHING on the formdown?

Line by Line Transactional Data Queries

Line by Line Transactional Data Queries Icon
I am working with line by line transactional data. For example, for a single order, I have multiple rows (identified by an orderID column) with several columns of additional information. Specifically for my tasks, I will be dealing with the discount amount (number) and discount/promo code (text) columns. Thus, I have 3 objectives:

1. If there exists a discount amount in a row with a null or blank value in the adjacent discount/promo code column, insert a text value "Other Discount" to all rows within that same order (orderid).

In other words, an order was placed with an unidentified discount code (text) provided.

1. Distribute a discount code (text) used in a purchase to rows of the same order (with the same orderid). Currently, an order with 3 purchased items only includes the discount code on one of the rows.

I need this text value replicated in each of the 3 line items or rows. (this is similar to my first task above)

2. Distribute the discount amount (number), which also only included in only 1 row for a multi-item rode, evenly across all ordered items so that when I aggregate all orders by OrderId, I will have a "net order revenue" number.

For example, if a person orders 3 different items at $25 each, receives a $15 discount, I need each row to be reduced to $20.


HELP! Table SQL Icon
I have one table with 5 columns (one is empty) and 12 rows, I have another table with 4 columns and 12 rows. I want to update the first table, the empty column, with the 3 columns from the second table.

Both tables have a record that a different Serial by row.

How to get Top 25 rows by each group

How to get Top 25 rows by each group Icon
I have a table in Access 2007 with almost 200,000 rows with multiple columns for which I need to find the top 25 rows for each group.

The important columns are STOREID (store ID), CUSTID (Customer ID), and QTY (Order amount). I want to select 25 rows per store that reflect the top 25 customers in descending order by their order amount values.

SELECT TOP 25. only gives me the top 25 rows regardless of store so I'm missing something.

How to transfer a 2d array into a table

How to transfer a 2d array into a table Icon
I have a 2d array, with 10 columns, and somewhere between 200 and 10.000 rows. I was hoping there was a simple(and efficient) way to transfer the entire array into a table containing over a million rows, and growing.

I allready know how to transfer it by looping through the array, adding 1 row at a time. However, this is not that effective, and I believe that one of the problems stems from the fact that the main table has its indexes updated each time a row is added.

I could probably work around this by first moving the array into a temporary table, and then use SQL to add all the rows at once.