Merging multiple rows into one row with several columns
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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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
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.
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.
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
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.
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?
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
I have an Excel template that has a series of blank rows
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
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