Summarizing a given table in this way,

[jamal numan]

Summarizing a given table in this way,



Please, have a look on the attached screenshot. How can I derive the indicated table from the existing one?

The data is attached


Thank you

Best

Jamal

[rpeare]

SELECT "Male Size Total" AS Label, Sum(Family.MaleSize) AS SumOfMaleSize
FROM Family
GROUP BY "Male Size Total"
UNION ALL
SELECT "Female Size Total" AS Label, Sum(Family.FemaleSize) AS SumOfFemaleSize
FROM Family
GROUP BY "Female Size Total";

[jamal numan]

SELECT "Male Size Total" AS Label, Sum(Family.MaleSize) AS SumOfMaleSize
FROM Family
GROUP BY "Male Size Total"
UNION ALL
SELECT "Female Size Total" AS Label, Sum(Family.FemaleSize) AS SumOfFemaleSize
FROM Family
GROUP BY "Female Size Total";

Thank you very much rpeare for the help. This is fantastic.

Just in case if I wanted to stick the percentage sign with the numbers then how could I do this (please, have a look on the attached screenshots)?


For other types of queries it is simple, just once can choose the format from the property sheet pane (attached).


Best

Jamal

[rpeare]

Each part of the union query would have to have the percentage column in it before you did the union OR you create a query that is based on the union query and add the calculation to that.

Personally I'd do the former rather than the latter if the result of your union query is the picture on the right (minus the percent column).

Without seeing your union query statement I'd be guessing though.

[jamal numan]

Each part of the union query would have to have the percentage column in it before you did the union OR you create a query that is based on the union query and add the calculation to that.

Personally I'd do the former rather than the latter if the result of your union query is the picture on the right (minus the percent column).

Without seeing your union query statement I'd be guessing though.

Very much appreciated rpeare for the prompt answer,

I’m attaching a screenshot of what I wanted to do and the data itself. I'm thankful for your help.

Best

Jamal

[rpeare]

I don't understand, the example I gave you was using union queries, which I thought was giving you what you wanted, but your screen shot shows no union queries, nor does it correspond to query you showed in post #3, it looks almost identical to the first post in terms of contents and layout.

[jamal numan]

I don't understand, the example I gave you was using union queries, which I thought was giving you what you wanted, but your screen shot shows no union queries, nor does it correspond to query you showed in post #3, it looks almost identical to the first post in terms of contents and layout.

Sorry for the confusion. My bad.

You are right. I just started it again from scratch to see how the union could be built to preserve the percentage (%) format. I tried it and found out that the unions doesn’t keep it in the output query table

Then which union syntax will keep the percentage sign?