Convert multiple rows to columns?

Hey

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


Post your answer or comment

comments powered by Disqus
Is there a to convert multiple reports to one PDF with VBA? I have a large amount of pdfs to produce around 4000. One of my other large problems I can not download any tools to help with this. I am using access 2010 and I have Adobe PDF Writer installed.

Rows to columns;
I have a table with three fields
ID,Desc,Code
1,aaa,code1
2,bbb,code2
3. aaa,code3
4, bbb,code4

I need a query creating these two columns:
Desc,Code
aaa,code1;code3
bbb,code2;code4

how can I do this?

I'm filling in for someone who has a strange love for mail merge and now I need to covert employee history from a row to a column for the mail merge.

I currently have a list of employees and every change in position is listed as a new row. Since all employees are not the same some employees have more rows than others. What I need to do is move the history rows into columns. So what I'll end up with is one single row per employees with their history going to the right. Some employees will have more columns than others. I tried doing a crosstab but that will only let me pick one column from the original table.

I have three columns that I need repeated over and over to reflect their work history.

this is what im working with
Name or ID will be the key
Job start date
Job title
department

currently every one of theese fields is in a column but every entry has its own row.

I need a single row for every employee and the columns to repeat to the right to reflect every move.

Can someone help me I'm stuck

I doesnt matter if the output is in something other than access

Hi everbody,

As a rookie to Access I browsed many forums in search for an answer to my problem. I've got a relationship based ageda/calender for multiple people (100+). The information of those people is ordened in one table and their appointments in another. The appointments are already ordened on Peson-Id and the appointment (which consist of the "dd-mm-yyyy':' uu:mm to uu:mm 'on' [location]", i.e. "01-01-2011: 09:00 to 10:00 on London square").

Table one:
Person-Id | Name | Address | Postal code | City | Phone number
1 | Hank | Street 1 | xxxxx | City 1 | 0000-00000
2 | Thomas | Street 2 | xxxxx | City 2 | 0000-00001
3 | Mary | Straat 3 | xxxxx | City 3 | 0000-00002
4 | Clara | Straat 4 | xxxxx | City 4 | 0000-00003

Table two:
Person-Id | Appointment
1 | "01-05-2010: 08:00 to 11:00 on London square"
1 | "01-08-2010: 09:00 to 10:00 on London square"
1 | "01-01-2011: 09:00 to 10:00 on London square"
3 | "01-06-2010: 09:00 to 10:00 on London square"
3 | "01-07-2010: 09:00 to 10:00 on London square"

From these two tables I am trying to created a query that shows the person-id, name, phonenumber and there next 5 appointments. I did not put all the data I've got in the the tables above, but you can imagen that every person in the database has more then 5 appointments.

Desired output:
Person-Id | Phone number | Appointment_1 | Appointment_2 | Appointment_3 | Appointment_4 | Appointment_5
1 | 0000-00000 | "01-05-2010: 08:00 to 11:00 on London square" | "01-08-2010: 09:00 to 10:00 on London square" | "01-01-2011: 09:00 to 10:00 on London square" | "" | ""
3 | 0000_00002 | "01-06-2010: 09:00 to 10:00 on London square" | "01-07-2010: 09:00 to 10:00 on London square" | "" | "" | ""

I already used a query to get only the desired data from the first table, now I am looking for a way to order the second table. Which means I need to switch the rows to columns ordened by Person-Id.

If that works, an inner join to combine the two sets of data fixes the rest.

Can someone help me with this problem?

Thanks in advance!

Thank you!

I have an unbound report where I am converting rows to colums. Records retrieved from the query are: Symbol,Year,Field1..Field24.
The report needs to print:
Symbol, Year1, Year2, Year3 , Year4
Field1
Field2
In the detail format section, I use VBA code to fill the unbound fields using a for,next loop to read through the record set and suppress printing until detail section is ready. In other words all records for the group are printed in one detail section. This works when the underlying query is run against a specific symbol. When all records in the table are selected, the report will print the last set of records only. Is there any way to force to detail section to print after each group of records is read and all unbound fields for that group are populated.
Help very much appreciated!

Is there a way to convert a row to a forum view as you can if Access?

I have a crystal report like this:

PersonID, Lastname, Site
12, Abbott, A
12, Abbott, B
12, Abbott, F
34, Smith, C
50, Olson, B
50, Olson, E

Basically a person can be in one or more site. (usually one or at most 4 sites.
We totally have 9 sites.)
Now I would like to pull the report this way- basically pull their sites from row to column, one person has one row.
PersonID, Lastname, Site1, site2, Site3, site4
12, Abbott, A, B, F
34, Smith C
50, Olson, B,E


Thanks

I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

I am currently using Access 2010.

Any help please?

Thanks

Kevin

My database has 5 joined tables and I need to add multiple records at one time rather than individually. All I can find on Google is how to add multiple rows to one table, but I need to be able to add multiple records to multiple tables while maintaining data integrity.

I have the table with all of the the records i need to add. Can I somehow tell my db to add the selected fields from "tblNew" to their corresponding fields in tables 1,2,3,4?

I have been trying to use the SQL view in a query to append multiple rows to a table. I have had no luck. Any suggestions?

Thank you.

I can use a simple query that shows survey responses like so:


	Code:
	SubID QstnBrief Rspns
--------------------
1     Gender  Male
1     Color   Red
1     Color   Blue
1     Color   Orange
--------------------
2     Gender  Female
2     Color   Blue
--------------------
etc.

Gender = What is you gender?
Color = What is favorite color(s)? (select all that apply)

Data on subjects is stored in tblSubjects.
Data on questions (QstnID, QstnBrief, etc.) is stored in tblQuestions. This table includes a variable to indicate of the question is a 'select all that apply' question.
Data on responses is stored in tblResponses.

I would like to convert these data to this structure:


	Code:
	SubID Gender Color_Red Color_Blue Color_Orange
1     Male   Red       Blue       Orange
2     Female           Blue       
etc.

In other words, the 'select all that apply questions" (like Color) need to return a separate variable for each response (using QstnBrief & "_" & Rspns).

If I didn't have 'select all that apply' questions, I could just use a simply crosstab to convert the data:


	Code:
	TRANSFORM First(tblResponses.Rspns) AS FirstOfRspns
SELECT tblSubjects.SubID AS SubID
FROM tblSubjects INNER JOIN (tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID = tblResponses.QstnID) ON
tblSubjects.SubID = tblResponses.RspnsID
GROUP BY tblSubjects.SubID
PIVOT tblQuestions.QstnBrief;

But this doesn't handle the select all that apply questions the way I need. Suggestions?

I am trying to make the data from multiple row to column. I tried using union but maybe I did not do it right and not get what I need. I also tried the crosstab. It gave me all the groupname on the header so i have scroll to the right for 100 columns where it did not align next to the InvName. It Here is the table data. The group# only have 3 groups. It has many different invName and the Group name about 100.

Group# InvName Value GroupName 1234 BS11 10.5 aabb 1234 BS11 10.5 ccdd 1234 BS4 5.5 hhee 1234 BS11 10.5 ooppp 1235 BC77 3.2 jjii

And this is what I want it to be: GroupName1 GroupName2 GroupName3 does not matter it can be the same I just want it to seperate each groupname in the same row where the InvName is the same.

Group# InvName Value GroupName1 GroupName2 GroupName3 1234 BS11 10.5 aabb ccdd ooppp 1234 BS4 5.5 ccdd 1235 BC77 3.2 jjii nncc
Just make all the same InvName and put the groupName in horizontal. Is this be done by using ms access? I have access 2007.

Thank you very much in advance to read this post. You advice are appreciated.

Hello 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
777 1/1/2010 73 95 68 ............ 95
777 1/2/2010 59 87 91 ........... 102
.........

888 1/1/2010 211 222 189 ........... 235
.........

999 1/1/2010 153 133 155 ........... 143
........

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

unitID Date Hour KWh
777 1/1/2010 1 73
777 1/1/2010 2 95
777 1/1/2010 3 68
........
777 1/1/2010 24 95
777 1/2/2010 1 59
777 1/2/2010 2 87
......


888 1/1/2010 1 211
888 1/1/2010 2 222
.......



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 ?

Regards,

WUJU

Hello y'all....brand new here. I searched through the forums but couldn't find anything on this...

I have a query that looks like this when exported to excel (note there are many more rows, this is just one)

VENDOR INVOICE FEES POOL PAYOUT
ARL01 07/04/11-07/31/11ARL01 36496.98 351000.07 -339307.76

This is what I want the export to Excel to look like

VENDOR INVOICE AMOUNT LINE DESCRIPTION
ARL01 07/04/11-07/31/11ARL01 36496.98 Fees
ARL01 07/04/11-07/31/11ARL01 351000.07 Pool
ARL01 07/04/11-07/31/11ARL01 -339307.76 Payout

So the same information is there, but each invoice item now has its own row (as well as another record that equals the column heading *this is optional and not as important)

How can I accomplish this? If you need more information, I'll do my best!....Thanks

A CrossTab Query needs minimum 3 fields [Column Header, Row Header & 3rd field is used for calculations: sum, add etc...]

Can a CrossTab Query have multiple Rows & Columns that use the same field to calculate?

All help is appreciated....

Regards,

Hi to everybody,
I need another help,

I created a query with several field, when the query run, the result is a nuber of row for the same "criteria",
e.g.

ID, Field1, Field2,...
1 , x , y,...
1, z , m,...
1, n , h,...

my question is:
Concatenate Column Values from Multiple Rows into a Single Column ?
e.g.
ID, Field2,
1 , x, z, n,
or
ID, Field1
1 , y,m,h

Thank in advance

Good evening:
I have an Access query that lists customer subscriptions to our products for
the current month and the 7 preceeding months (One column for each month). A customer can subscribe to more than one product,
therefore the subscriptions are listed multiple times each month. (one record for each
subscription).
I need to combine the records for each customer, for their current
product(s).

Example:

ABC COMPANY__________SUBSCRIPTION 1
ABC COMPANY__________SUBSCRIPTION 2
ABC COMPANY__________SUBSCRIPTION 3

How do I combine the subscriptions into one column instead or multiple rows?
(see below) Possibly, but not necessarily, comma delimited.

Like this...
ABC COMPANY_________SUBSCRIPTION 1, SUBSCRIPTION 2, SUBSCRIPTION 3

Sorry in advance as I am totally self-taught and need very basic explanations if you can help!
Can I present the attached table in Excel in a database report? If so, how would I design the report/ query?
I have included the basic table structure that I have at the moment (it is a very large database that has worked fine over many years, but now I'm stuck!)
The "performance" field in the Performance table is a "number" field and cannot have y/n entries - I guess I can get around this by having 1 or 0 as the entries instead? What I am having trouble with is that I have multiple rows and columns, as the service wants to see each Site separately with the blocks for that Site listed below, by date, but with all performance indicator results under the date column. My other reports only have one column heading whereas this apparently needs several??!!

Hi,

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.

Can anyone please help??

Greetings,

I have searched and searched for the best way to do this and I am stumped so here goes. I will try to be as detailed as I can with my explaination.

I have a text file with data I want to import into access.
here is what it looks like.

00100378435
NOTE: 01/13/05 07:27 Cat: Entered by: JOSH
JOSH 730 CSR/JOSH TALKED W/FSM 1013. FSM ADVISES THAT
SOFTWARE ISSUE THAT DD CANNOT CORRECT IN THE FIELD.
ADVISE CUST THAT NEW SOFTWARE IS DUE OUT SOON.
JTM/DD

00100378436
NOTE: 01/13/05 08:30 Cat: Entered by: JOSH
830 CSR/JOSH TALKED W/FSM 1013. FSM ADVISES THAT
SOFTWARE ISSUE IS OUT.
JTM/DD

Each 11 character number field represents an account and the notes go with that account.

When I import it into access i can get it into 2 columns.

The first column has the numbers and the second column has the notes but each line of notes creates a seperate row instead of all the notes in 1 row.

So I end up with Column 1 having in the first row the number then several empty rows of cells (depending on how many lines of text there are) and in Column 2 I get multiple rows each with one line of text.

I do have an index file that lists the account number with the start and end row of the text file that is such as this:

Start,End,Account
1,33,00100378435
34,61,00100388570
62,67,00100409643
68,69,00100425683
70,94,00100434326

I thought I could use the GetRows command to pull the rows into one field but I havent been able to figure out how to do this.

Just to let you know the text file of information has over 4 million lines of text in it so I cant just edit the file. Each line has a carriage return as well as spaces.

I am not a stranger to Access but I am not by any means an expert.

Any help on how to accomplish this would be most helpful.

Thank you in advance.

Hello All..!!
I hate to say it, but "I'm new to VBA". I've always used Excel and loved it, but my method was always the method of many many mouse clicks.
Lately i've been working with sheets with more than 1500 rows, that refer to more than 300 worksheets.
I'm finding lots of useful information on the internet in forums here and there. My problem is i'm not able to join the various things I’ve learned.
What I need to do is copy multiple rows based on a numerical value from a single column.

Example:
Quantity A B C ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
to this…
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
2 dataC1R1 dataC2R1 dataC3R1 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
1 dataC1R2 dataC2R2 dataC3R2 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….
3 dataC1R3 dataC2R3 dataC3R3 ….

The code I’ve been trying to work with inserts blank rows. Where I need it to copy the previous row based on the value in the first column.
Sub AddMultitipleRows2()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
' Based on the value in column b, where Quantity is in the
' example above.
For iRow = LastRow To FirstRow Step -1
With .Cells(iRow, "b")
If IsNumeric(.Value) Then
If .Value > 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
If .Value = 1 Then
.Offset(1, 0).Resize(.Value).EntireRow.Insert
End If
End If
End With
Next iRow
End With
End Sub
thank you to anyone that could take the time to help.
Dogbone...

I got a very simple access database of assets that’s automatically generated by a third party software into an access database table.
The data I get ends up int the 'wrong order' and I need to change the rows and columns.
This is how it looks:
Asset# - Item - ItemValue2
Asset1 - Name - PC1
Asset1 - Serial - 123ABC
Asset2 - Name - PC2
Asset2 - Serial - 456DEF

And this is how I want it to look:
Asset# - Name - Serial
Asset1 - PC1 --- 123ABC
Asset2 - PC2 --- 456DEF

I tried to search for it but didn’t get any good results out of it. Can someone help me with a simple way to fix this?

Hi All,

I have been self-teaching access for about 2 weeks now, but am a bit stuck now on how to make things work!

Basically I am making a staff database, recording personal staff details, and multiple companies they have previously had involvement with. It feels like I need multiple rows per record but I know obviously this is not the way to go or else I end up with many records for one staff member. Also, for each company they have worked with I need to record 4 pieces of information (tickbox or yes/no is fine).

Like this:

Last_Name Company Recruitment Travel Finance Education

Bloggs_________EDF Yes Yes No No
______________Oracle No Yes Yes No
Williams________XYZ Yes No No No
______________Aston No No Yes Yes


I have tried creating extra tables and one-to-many relationships with junction tables etc, but with no joy.

Also self teaching has meant I do not know coding, I hope this is possible without this!

I would very much appreciate your help, many thanks in advance!

I need to convert the following dataset from:

ClmSys Code
1111 V1
1111 V2
1111 V3
2222 V2
2222 H4
2222 H4
2222 P8
3333 JV
4444 AA
4444 V3
4444 V2
4444 TT
....

TO
ClmSys Cod1 Cod2 Cod3 Cod4 ...
1111 V1 V2 V3
2222 V2 H4 H4 P8
3333 JV
4444 AA V3 V2 TT

Please help me for that. Any idea like SQL or any way I can do that? Thanks.
Albert


Not finding an answer? Try a Google search.