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

Question about creating columns from data in rows

P: n/a
Sol
If I have data in the following format in a database

LE Amount BS Class
1 10 Asset
1 15 Liability
1 20 Asset
1 12 Liability
2 45 Asset
2 74 Asset
2 12 Liability
I want to extract the sum of each LE by BS Class, but I want the BS
class in two seperate columns.

So
LE Assets Liablities
1 30 27
2 119 12
What SQL code can I use to do that.

NB I have run two seperate queries to return Assets & liabilities, but
if one LE has no assets the two tables get out of sequence. I really
need it to say 0 assets vs the liabilities.

Oct 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Sol,

If your data is in the format you describe then you can use a crossTab
query to get the results you desire. Just click on New Query and select
Crosstab from the list. A wizard will come up to guide you through the
query creation process. Select LE for Row Heading, select BS Class for
Column Heading, and select Sum for Amount. You may want to uncheck the
"Include Sum Total" in the lower left corner of the dialog that contains
the list with the Sum function.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 5 '07 #2

P: n/a
Use a crosstab query (Crosstab on Query menu, in query design.)

LE is your Row Heading
BS Class is your Column Heading.
Amount is your Value, and it looks like you want Sum in the Total row.

Once you get the basics of crosstab queries down, here's some more involved
techniques:
http://allenbrowne.com/ser-67.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sol" <so***********@citi.comwrote in message
news:11**********************@r29g2000hsg.googlegr oups.com...
If I have data in the following format in a database

LE Amount BS Class
1 10 Asset
1 15 Liability
1 20 Asset
1 12 Liability
2 45 Asset
2 74 Asset
2 12 Liability
I want to extract the sum of each LE by BS Class, but I want the BS
class in two seperate columns.

So
LE Assets Liablities
1 30 27
2 119 12
What SQL code can I use to do that.

NB I have run two seperate queries to return Assets & liabilities, but
if one LE has no assets the two tables get out of sequence. I really
need it to say 0 assets vs the liabilities.
Oct 6 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.