Merging multiple rows into one row with several columns

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:

Related Topics

Converting multiple columns into one row
VBA scripters,

I need help on converting multiple columns into one row (actually two rows).
I have a Access table with 26 columns UnitID, Date, 1, 2, 3, 4,.,24.
(data in column "1" means KWh used in hour 1, and samething appplies to each hour until hour 24)

I want to convert this table to four columns (rows expands to 24 times larger since columns of hours changes to rows)

UnitID, Date, Hour, KWh_used

Table before converting is below

UnitID date 1 2 3 . 24

and, a table structure I want to see after conversion is below

unitID Date Hour KWh

Could you please help me using Visual Basic (in Access 2007) to convert multiple columns of KWh used in each hour into two columns, Hour and KWh used


System Requirements for Complex Large Queries
If I have 13 sheets linked from excel containing about 15,000-20,000 rows and 15ish columns. A 3 have about 35,000 rows, 1 has 85,00 rows and 12 columns, 10 columsn and 2 have 400,000 (2-3 columns) then I create multiple quries then from those quries make more quiries.

Can a 2.8ghz, i7 (not sandy) 4gb system handle this OR would adding more RAM help?

It's going really slow right now making the 1st query from the table with about 85,000 rows and 12 columns. Can't wait to combine that with the 20,000 row database with 15ish columns.


After finding duplicates, how to update row?
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.


Convert multiple rows to columns?
I have been beating my head against the wall trying to figure out a way to get this working. I have a total of 4 queries I am running to get various customer details from various tables.

Most of the data I need is in a couple of tables with just 1 row per customer and I usually only need to grab a couple columns out of it. Now the problem I have run into is the table that stores what appliances the customer has on the plan links to a contract table via a Contract_id and then each individual appliance has it's own Appliance_id attached to the record. What happens is when I run the query it will give me say 9 rows with the same Contract_id along with all the customer info but then each row represents a different appliance. What I need to be able to do is take those individual appliances in their rows and convert them into columns so when I run the query it just gives me 1 row per customer but has the different appliances as columns in that row. This way I can use the data to do a mail merge


Convert multiple rows to columns?
I have been beating my head against the wall trying to figure out a way to get this working. I have a total of 4 queries I am running to get various customer details from various tables.

Most of the data I need is in a couple of tables with just 1 row per customer and I usually only need to grab a couple columns out of it. Now the problem I have run into is the table that stores what appliances the customer has on the plan links to a contract table via a Contract_id and then each individual appliance has it's own Appliance_id attached to the record.

What happens is when I run the query it will give me say 9 rows with the same Contract_id along with the customer info but then each row represents a different appliance.

What I need to be able to do is take those individual appliances in their rows and convert them into columns so when I run the query it just gives me 1 row per customer but has the different appliances ascolumns in that row. This way I can use the data to do a mail merge.


How to detect user pasting multiple rows into a datasheet
I have a datasheet application that updates additional fields based on the input of one field using VBA code. Due to the design constraints, I use the following technique.

1. After the field is updated, I do a Refresh to save the row
2. Then I retrieve the other values I need using VBA code and save them.
3. Finally, I read the saved row, update the additional fields, and update the row.

This works fine as long as the operator enters the new row manually or even if he/she does a copy & paste of one row. However, if he/she copies and pastes multiple rows, then the Refresh doesn't insert the multiple rows and my step 3 above fails. Is there a way I can detect that multiple rows are being inserted so I can handle the event accordingly?


Two Remove those rows in a table which has already occured for a particular value
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


Cut multiple rows into new spreadsheet based on cell values
I'm new with macros and am trying to cut rows from sheet2 to a new sheet based on the values in column a. In Column A there are numbers (1-however many.sometimes 5 sometimes 80) and I want each number to be on its own tab (there are five columns of information for each row.not sure if that matters or not). Each number may have multiple rows associated with it so I would like to cut all those rows and paste them into a new sheet


Peforming Excel operations via Acces automation
Access 2007 issue, however could relate to earlier versions.

I have an Excel template that has a series of blank rows

Row1 headings
row 2 headings
rows 3 to 30 blank rows
row 34 formulas to sum values in columns =Sum(B4:B33)

Repeated about 5 times further down the worksheet

Access opens the workbook and uses CopyToRecordset to populate Excel from row 4 downwards. Will never be more than 30 rows.

But what I want to do is when the copy has taken place I want to remove blank rows when a row in column B is blank.

What I did was something like

For X = 4 to 250
If Range("B" & X).Value = "" Then
delete the row
End If Next

But could not get it to work


Colouring Rows & Columns
Does anybody know if you can colour rows or columns in Access?

I know you can colour every-other row, but this isn't what I need to do. I need to be able to colour certain rows or columns


Hiding row data from a report
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.


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

For ex-E1
E2
E3

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.


Moving and adding rows of data
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?


Update Query
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


Comnbining multiple rows into one row
We have a table that has school IDs, grades (1-12), subjects (reading, math) and scores (integers). There is one row in the table per subject/grade combination. For instance, reading grade 2 will be one row, reading grade 3 is another row, etc. There is a full set of subject/grade rows for each school, so there are 24 rows per school (12 reading, 12 math). We need to run a query that combines all the subject/grade/scores into one row per school, so the result row would have school_ID, reading_grade_1_score, math_grade_1_score, reading_grade_2_score, math_grade2_score, etc.

All the rows for one school have the same school_ID.

Can someone point me in the direction of how to accomplish this? Help will be greatly appreciated!


Multiple Record Entry Form - Test for any null fields
I have a form (DataEntry) and subform (DataEntrySubform) that allows the user to enter multiple rows of data to TableTemp, upon clicking Submit, VBA code is used to append TableAll with any rows in TableTemp, then delete * TableTemp.

My problem is this. I would like to test to make sure the user has filled out certain columns (some are optional, some are not). I don't want to set the requirement in TableTemp, because users need the freedom to enter data in multiple rows column by column.

I prepared an if statement like this

If IsNull(DLookup("[ColumnA]", "TableTemp")) Or IsNull(DLookup("ColumnB", "TableTemp")) THEN MsgBox "Whoooops!"

But this will not work if there are multiple rows, where perhaps one is completed satisfactorily while another row is not


Multiple Record Entry Form - Test for any null fields
I have a form (DataEntry) and subform (DataEntrySubform) that allows the user to enter multiple rows of data to TableTemp, upon clicking Submit, VBA code is used to append TableAll with any rows in TableTemp, then delete * TableTemp.

My problem is this. I would like to test to make sure the user has filled out certain columns (some are optional, some are not). I don't want to set the requirement in TableTemp, because users need the freedom to enter data in multiple rows column by column.

I prepared an if statement like this

If IsNull(DLookup("[ColumnA]", "TableTemp")) Or IsNull(DLookup("ColumnB", "TableTemp")) THEN MsgBox "Whops!"

But this will not work if there are multiple rows, where perhaps one is completed satisfactorily while another row is not.


merging different tables
I have 2 different tables in excel with a lot of information in them. I managed to link them to Access 2007 because I want them to be updated as I change them in excel. I want my final table in access.

My 2 tables have different column headings (some of them are the same) and different row headings (some may be the same). not all the cells have information in them, but it is a mixture of either numerical information or alphabetical. How do I complie both the tables into 1 big table?

So basically in one table I have colums 1,2,3,6 and rows a,b,c and in the second table I have colums 1,4,5 and rows d,e,b,f and I want to end up with a table with columns 1,2,3,4,5,6 and rows a,b,c,d,e,f. Not all of the data is filled in for all of the cells in the tables so 1d might be blank and I want it to show up blank in my big table. Any idea of how to go about doing this


Line by Line Transactional Data Queries
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.


from excel - multiple column to one
I am trying to convert an excel sheet to access. This is a sheet to track when a task needs to be done and when it was done.

The excel sheet has a column for each task and each date. So if task A needs to be done monthly there are 12 columns for that task.

The issue is not all tasks are needed for each record and from what I know of DB design multiple rows are better than multiple columns. I am looking at converting the data from

name, report Jan, report Feb, report mar, .

to
Name, type, date

with type being the type of task that has to be done.

Is there a way to quickly change the data from multiple columns to multiple rows.

PS: What I tried was several append equerries one for each task and for each month. I am hoping there is an easier way