Topics Search

Merging multiple rows into one row with several columns

Merging multiple rows into one row with several columns
Views: 34
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:

More topics

 

Converting multiple columns into one row

Preview
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

Preview
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?

Preview
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?

Preview
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?

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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