# 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 & "" & Sex2 AS Sex, Sum(Number) AS [Total Number]
FROM TableName
GROUP BY Sex1 & "" & 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 < Sex2 THEN Sex1 ELSE Sex2 END
& CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END AS Sex,
Sum(Number) AS [Total Number]
FROM TableName
GROUP BY CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END,
CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END ;
``````

with `IIf()` function:

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

You can use a UNION query to force the Sex1/Sex2 pairs to be in the same order

``````    SELECT
Nz(Sex1, "") & Nz(Sex2, "") AS Sex,
Number
FROM TableName
WHERE Nz(Sex1, "") >= Nz(Sex2, "")
UNION ALL
SELECT
Nz(Sex2, "") & Nz(Sex1, "") AS Sex,
Number
FROM TableName
WHERE Nz(Sex1, "") < Nz(Sex2, "")
``````

producing

``````Sex  Number
---  ------
FF        3
MF        1
FF        9
MM        3
F         5
M         8
MF        5
MF        2
``````

and then use that as a subquery to produce the totals

``````SELECT
Sex,
SUM(Number) AS [Total Number]
FROM
(
SELECT
Nz(Sex1, "") & Nz(Sex2, "") AS Sex,
Number
FROM TableName
WHERE Nz(Sex1, "") >= Nz(Sex2, "")
UNION ALL
SELECT
Nz(Sex2, "") & Nz(Sex1, "") AS Sex,
Number
FROM TableName
WHERE Nz(Sex1, "") < Nz(Sex2, "")
)
GROUP BY Sex
``````

producing

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

You can try using CONCAT() function in MSSQL to get 2 columns in one.

SELECT CONCAT(`Sex1`,`Sex2`) AS Sex, Sum(`Number`) AS `Total_Number` FROM `db`.`test` GROUP BY concat(`Sex1`,`Sex2`);

• Does Access have a `CONCAT()` function? Even if it did, the `group by` you have would not solve the issue asked. MF would be grouped separately from FM. – ypercubeᵀᴹ Jul 18 '16 at 12:57