By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

How do i transfer different rows into (a) columns heads and (b) rows using SQL?

P: 5
How do i transfer rows into columns using SQL?

Please help me to solve this situation using MS ACCESS Query

I have a table that stores records like

Table x

ID WeekEnd %
1 10/12/07 70
1 10/13/07 80
1 10/14/07 40
1 10/15/07 45
2 10/12/07 20
2 10/13/07 30
2 10/15/07 30

I want to display the above Table in the following format using query.

ID 10/12/07 10/13/07 10/14/07 10/15/07
1 70 80 40 45
2 20 30 NA 30

1). Want to display the WeekEnd as column head and display values accordingly
2) Fill NA for empty fields

Note: Please provide me better solution other than creating a table.


Thanks,
Karthik
Sep 7 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes!

You need to use a crosstab query for this, where you make the date the column heading and your current percentages the value to be pivoted (using an aggregate function such as sum on them for the purposes of the crosstab query).

Your question would, however, appear to be a homework assignment. Our guidance notes and FAQ on posting such questions make it clear that we cannot provide you with complete solutions for homework questions - you need to do the work yourself.

Investigate the use of a crosstab query to do what you need, and if you have specific issues we can help you with that would not break our guidelines please let us know.

-Stewart
Sep 10 '08 #2

P: 5
Thanks for the reply.

Now, i have designed the query as expected and i want this to publish in report for printing and some additional calculation.

How can i do that.

Regars,
Karthikeyan S


Hi, and Welcome to Bytes!

You need to use a crosstab query for this, where you make the date the column heading and your current percentages the value to be pivoted (using an aggregate function such as sum on them for the purposes of the crosstab query).

Your question would, however, appear to be a homework assignment. Our guidance notes and FAQ on posting such questions make it clear that we cannot provide you with complete solutions for homework questions - you need to do the work yourself.

Investigate the use of a crosstab query to do what you need, and if you have specific issues we can help you with that would not break our guidelines please let us know.

-Stewart
Sep 10 '08 #3

Expert Mod 2.5K+
P: 2,545
Sorry, but I simply cannot guide you through such a task; please use the Access report wizards and base your report on the query you have developed. You will need to modify the automatically-created report to fit the fields more correctly for your page. Only you can do this - we simply can't assist you with such a task.

-Stewart
Sep 10 '08 #4

Expert Mod 2.5K+
P: 2,545
HI again. I did not know that this thread was a duplicate of another thread you started. As this is against site rules I would ask you please not to double-post your threads - it wastes the time of the volunteer expert contributors to this site. I will close this thread to avoid wasting the time of other contributors.

MODERATOR
Sep 10 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.