Access: How to Combine Data from Two Rows in a Query I have a base dataset formatted like the one below.

Sex1    | Sex2    | Number|  
F       |         | 5     |   
M       |         | 8     |
M       | F       | 5     |   
F       | F       | 3     |
M       | F       | 2     |   
F       | M       | 1     |
F       | F       | 9     |   
M       | M       | 3     |


I'd like to use SQL to create a query that produces totals from the price by Gender composition like so:

Sex | Total Number
F   |    5
M   |    8
MF  |    8 
FF  |    12
MM  |    3


However, I'm struggling to find a way to pull the FM and MF categories into the same row. The code I have that leaves them distinct is of the form

SELECT Sex1 &amp; "" &amp; Sex2 AS Sex, Sum(Number) AS [Total Number]
FROM TableName
GROUP BY Sex1 &amp; "" &amp; Sex2


Is there a simple way to combine the rows?
You could use a CASE expression or the IIf() function to first order the two values and then group by them:

with CASE expression:

SELECT CASE WHEN Sex1 &lt; Sex2 THEN Sex1 ELSE Sex2 END 
       &amp; CASE WHEN Sex1 &lt; Sex2 THEN Sex2 ELSE Sex1 END AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY CASE WHEN Sex1 &lt; Sex2 THEN Sex1 ELSE Sex2 END,
         CASE WHEN Sex1 &lt; Sex2 THEN Sex2 ELSE Sex1 END ;


with IIf() function:

SELECT IIf(Sex1 &lt; Sex2, Sex1, Sex2) &amp; IIf(Sex1 &lt; Sex2, Sex2, Sex1) AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY IIf(Sex1 &lt; Sex2, Sex1, Sex2),
         IIf(Sex1 &lt; Sex2, Sex2, Sex1) ;