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

Sponsored Links:

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
3. aaa,code3
4, bbb,code4

I need a query creating these two columns:

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

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


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?



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:

	SubID QstnBrief Rspns
1     Gender  Male
1     Color   Red
1     Color   Blue
1     Color   Orange
2     Gender  Female
2     Color   Blue

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:

	SubID Gender Color_Red Color_Blue Color_Orange
1     Male   Red       Blue       Orange
2     Female           Blue       

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:

	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 ?



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)

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

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


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",

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 ?
ID, Field2,
1 , x, z, n,
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
I need to combine the records for each customer, for their current



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

Like this...

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


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