MS Acess Cross Tab Query - Row to Column Transpose
0
Using cross tab query in MS Access SQL
how to transform table like this
===========================
|IND |LBL |TICKETS|
===========================
|1 |AM |101 |
|2 |AM |1 |
|1 |HR |102 |
|1 |TV |100 |
|2 |TV |2 |
===========================
to this
=======================
|LBL |IND1 |IND2|
=======================
|AM |101 |1 |
|HR |102 |0 |
|TV |100 |2 |
=======================
this is the query that i tried.
I think I have made some mistake in the crosstab query, if you could point out, it would be helpful
TRANSFORM First(TBL1.TICKETS) AS TCK
SELECT TBL1.LBL, First(TBL1.TICKETS) AS TCK1
from
(
SELECT IIF(A.AM= '[AM]' OR A.AM='[AM2]',1,2) AS IND
, 'AM' AS LBL
, COUNT(A.AM) AS TICKETS
FROM shree_main AS A GROUP BY IIF(A.AM='[AM]' OR A.AM='[AM2]',1,2), 'AM'
union all
SELECT IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1 ,2) AS IND, 'HR' AS LBL,
count([A].Newpri) AS TICKETS FROM shree_main AS A
GROUP BY IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1,2), 'HR'
union all
SELECT IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2) AS IND, 'TV' AS LBL, COUNT(A.TV) AS TICKETS FROM shree_main AS A GROUP BY IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2), 'TV'
) AS TBL1
GROUP BY TBL1.LBL, TBL1.LBL, TBL1.LBL, TBL1.LBL
PIVOT TBL1.LBL;
and this is the result of the query
LBL TCK1 AM HR TV
AM 1 101
HR 102 102
TV 2 100
ms-access-2010 crosstab
asked Nov 11 at 18:05
user2959433
247
add a comment |
0
Using cross tab query in MS Access SQL
how to transform table like this
===========================
|IND |LBL |TICKETS|
===========================
|1 |AM |101 |
|2 |AM |1 |
|1 |HR |102 |
|1 |TV |100 |
|2 |TV |2 |
===========================
to this
=======================
|LBL |IND1 |IND2|
=======================
|AM |101 |1 |
|HR |102 |0 |
|TV |100 |2 |
=======================
this is the query that i tried.
I think I have made some mistake in the crosstab query, if you could point out, it would be helpful
TRANSFORM First(TBL1.TICKETS) AS TCK
SELECT TBL1.LBL, First(TBL1.TICKETS) AS TCK1
from
(
SELECT IIF(A.AM= '[AM]' OR A.AM='[AM2]',1,2) AS IND
, 'AM' AS LBL
, COUNT(A.AM) AS TICKETS
FROM shree_main AS A GROUP BY IIF(A.AM='[AM]' OR A.AM='[AM2]',1,2), 'AM'
union all
SELECT IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1 ,2) AS IND, 'HR' AS LBL,
count([A].Newpri) AS TICKETS FROM shree_main AS A
GROUP BY IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1,2), 'HR'
union all
SELECT IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2) AS IND, 'TV' AS LBL, COUNT(A.TV) AS TICKETS FROM shree_main AS A GROUP BY IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2), 'TV'
) AS TBL1
GROUP BY TBL1.LBL, TBL1.LBL, TBL1.LBL, TBL1.LBL
PIVOT TBL1.LBL;
and this is the result of the query
LBL TCK1 AM HR TV
AM 1 101
HR 102 102
TV 2 100
ms-access-2010 crosstab
asked Nov 11 at 18:05
user2959433
247
add a comment |
0
0
0
Using cross tab query in MS Access SQL
how to transform table like this
===========================
|IND |LBL |TICKETS|
===========================
|1 |AM |101 |
|2 |AM |1 |
|1 |HR |102 |
|1 |TV |100 |
|2 |TV |2 |
===========================
to this
=======================
|LBL |IND1 |IND2|
=======================
|AM |101 |1 |
|HR |102 |0 |
|TV |100 |2 |
=======================
this is the query that i tried.
I think I have made some mistake in the crosstab query, if you could point out, it would be helpful
TRANSFORM First(TBL1.TICKETS) AS TCK
SELECT TBL1.LBL, First(TBL1.TICKETS) AS TCK1
from
(
SELECT IIF(A.AM= '[AM]' OR A.AM='[AM2]',1,2) AS IND
, 'AM' AS LBL
, COUNT(A.AM) AS TICKETS
FROM shree_main AS A GROUP BY IIF(A.AM='[AM]' OR A.AM='[AM2]',1,2), 'AM'
union all
SELECT IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1 ,2) AS IND, 'HR' AS LBL,
count([A].Newpri) AS TICKETS FROM shree_main AS A
GROUP BY IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1,2), 'HR'
union all
SELECT IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2) AS IND, 'TV' AS LBL, COUNT(A.TV) AS TICKETS FROM shree_main AS A GROUP BY IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2), 'TV'
) AS TBL1
GROUP BY TBL1.LBL, TBL1.LBL, TBL1.LBL, TBL1.LBL
PIVOT TBL1.LBL;
and this is the result of the query
LBL TCK1 AM HR TV
AM 1 101
HR 102 102
TV 2 100
ms-access-2010 crosstab
asked Nov 11 at 18:05
user2959433
247
Using cross tab query in MS Access SQL
how to transform table like this
===========================
|IND |LBL |TICKETS|
===========================
|1 |AM |101 |
|2 |AM |1 |
|1 |HR |102 |
|1 |TV |100 |
|2 |TV |2 |
===========================
to this
=======================
|LBL |IND1 |IND2|
=======================
|AM |101 |1 |
|HR |102 |0 |
|TV |100 |2 |
=======================
this is the query that i tried.
I think I have made some mistake in the crosstab query, if you could point out, it would be helpful
TRANSFORM First(TBL1.TICKETS) AS TCK
SELECT TBL1.LBL, First(TBL1.TICKETS) AS TCK1
from
(
SELECT IIF(A.AM= '[AM]' OR A.AM='[AM2]',1,2) AS IND
, 'AM' AS LBL
, COUNT(A.AM) AS TICKETS
FROM shree_main AS A GROUP BY IIF(A.AM='[AM]' OR A.AM='[AM2]',1,2), 'AM'
union all
SELECT IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1 ,2) AS IND, 'HR' AS LBL,
count([A].Newpri) AS TICKETS FROM shree_main AS A
GROUP BY IIF(A.Newpri= '[HR]' OR A.Newpri='[NML]' OR A.Newpri='[N2H]',1,2), 'HR'
union all
SELECT IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2) AS IND, 'TV' AS LBL, COUNT(A.TV) AS TICKETS FROM shree_main AS A GROUP BY IIF(A.TV= '[SC]' OR A.TV='[TV]' OR A.TV='[TV][SC]' OR A.TV='[VE]',1 ,2), 'TV'
) AS TBL1
GROUP BY TBL1.LBL, TBL1.LBL, TBL1.LBL, TBL1.LBL
PIVOT TBL1.LBL;
and this is the result of the query
LBL TCK1 AM HR TV
AM 1 101
HR 102 102
TV 2 100
ms-access-2010 crosstab
ms-access-2010 crosstab
asked Nov 11 at 18:05
user2959433
247
asked Nov 11 at 18:05
user2959433
247
edited Nov 11 at 18:19
asked Nov 11 at 18:05
user2959433
247
asked Nov 11 at 18:05
user2959433
247
asked Nov 11 at 18:05
user2959433
247
247
add a comment |
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53251651%2fms-acess-cross-tab-query-row-to-column-transpose%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53251651%2fms-acess-cross-tab-query-row-to-column-transpose%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
