MS ACCESS Group Query

Question

This one really has me scratching my head. It's sort of like a GROUP_CONCAT, but different. I'm pretty sure there is no way to do this with SQL only. I have a query that does a flip-table on a normalized table. The result looks like this:

|_Category_|_FieldA_|_FieldB_|_FieldC_|
|----------|--------|--------|--------|
|   CAT1   |    A   |        |        | 
|----------|--------|--------|--------|
|   CAT1   |        |    B   |        | 
|----------|--------|--------|--------|
|   CAT1   |        |        |    C   | 
|----------|--------|--------|--------|
|   CAT1   |   D    |        |        | 
|----------|--------|--------|--------|
|   CAT1   |        |        |    E   |   
|----------|--------|--------|--------|
|   CAT1   |    F   |        |        | 
|----------|--------|--------|--------|

My challenge is to compress it into as few rows as possible, but only have one value per cell.

|_Category_|_FieldA_|_FieldB_|_FieldC_|
|----------|--------|--------|--------|
|   CAT1   |    A   |    B   |    C   | 
|----------|--------|--------|--------|
|   CAT1   |    D   |        |    E   | 
|----------|--------|--------|--------|
|   CAT1   |    F   |        |        | 
|----------|--------|--------|--------|

Any Ideas?

Thanks in advance.

Mark

If you would like to normalize it, i'd suggest to redesign it. Have you any chance to do that? — Maciej Los, Jan 20 '15 at 23:10
The link below should give you your answer: http://stackoverflow.com/questions/5517233/ms-access-query-concatenating-rows-through-a-query — Dane I, Jan 21 '15 at 02:11
The database is normalized. The actual table contains a field for the Category, a field that designates a division, and a third field for the value. When it's flipped, each division becomes a column. Each row in the query caries a value for a division. The client wants the data "compressed" - the first item for division 1, the first item for division 2, and the first item for division 3 appear on the first fow for that category. You can see this pattern in the example of the results I need. — user1956557, Jan 21 '15 at 02:21
The example provided is the equivelant of the Group_Concat function in MySQL. It's close, but not quite what I need. Group_Concat creates a delimited list and only one row for the grouping field. What I need is a row for each item in the column that has the most items in the category, in the sample it's FieldA — user1956557, Jan 21 '15 at 02:29
You could use it for each column, create a table for each of your 3 columns, then do a simple join query with the new tables, no? — Dane I, Jan 21 '15 at 02:35

score 3 · Accepted Answer · answered Jan 21 '15 at 14:21

As i mentioned in my comment to the question, normalized table should look like:

|_Category_|_F_Name_|_F_Val__|
|----------|--------|--------|
|   CAT1   | FieldA |    A   |
|----------|--------|--------|
|   CAT1   | FieldB |    B   |
|----------|--------|--------|
|   CAT1   | FieldC |    C   |
|----------|--------|--------|
|   CAT1   | FieldB |    D   |
|----------|--------|--------|
|   CAT1   | FieldC |    E   |
|----------|--------|--------|
|   CAT1   | FieldA |    F   |
|----------|--------|--------|

How to achieve that?

SELECT A.Category, "FieldA" AS FieldName, A.FieldA AS FieldValue
FROM TableA AS A
WHERE NOT A.FieldA IS NULL
UNION ALL
SELECT A.Category, "FieldB", A.FieldB
FROM TableA AS A
WHERE NOT A.FieldB IS NULL
UNION ALL
SELECT A.Category, "FieldC", A.FieldC
FROM TableA AS A
WHERE NOT A.FieldC IS NULL;

To export the data into new table, use query:

SELECT B.* INTO TableB
FROM (
   --above query
)  AS B;

Do not forget to add autonumber field (as primary key) to TableB to be able to identify each record.

As per my understanding, you want to pivot data. It's not so simple, becasue we need to simulate

ROW_NUMBER() OVER(PARTITION BY FieldName, ORDER BY ID)

which is not supported in MS Access. How to workaround it?

SELECT B.ID, B.Category, B.FieldName, B.FieldValue,
       (SELECT COUNT(A.FieldName)
        FROM TableB AS A
        WHERE A.FieldName=B.FieldName AND A.ID >=B.ID
        GROUP BY A.FieldName ) AS TRank
FROM TableB AS B;

It should produce below record set:

ID  Category    FieldName   FieldValue  TRank
1   CAT1        FieldA      A           3
2   CAT1        FieldA      D           2
3   CAT1        FieldA      F           1
4   CAT1        FieldB      B           1
5   CAT1        FieldC      C           2
6   CAT1        FieldC      E           1

But... you can't use above query as a source of pivot data, because of "The Microsoft Access database engine does not recognize as a valid field name or expression. (Error 3070)" error message. So, finally, you should export these data into another table (let's say TableC).

SELECT C.* INSERT INTO TableC
FROM TableB AS C

Now, you can pivot data:

TRANSFORM First(A.FieldValue) AS FirstOfFieldValue
SELECT A.Category, A.TRank
FROM TableC AS A
GROUP BY A.Category, A.TRank
PIVOT A.FieldName;

Result:

Category    TRank   FieldA  FieldB  FieldC
CAT1        1       F       B       E
CAT1        2       D       C
CAT1        3       A

Cheers,
Maciej

You're very welcome ;) If my answer was helpfull, please accept it. — Maciej Los, Jan 21 '15 at 21:51

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

I had the same problem. Take a look at: Microsoft Access condense multiple lines in a table

or get the cloud version here https://www.apponfly.com/en/application/microsoft-access-2013

Works pretty good

edited May 23 '17 at 12:28

Community

1
1

answered Jan 21 '15 at 14:12

NataliaGarber

1

This is the equivelant of the MySQL Group_Concat function, which is close to what I need, but not quite it, since it concatenates all the rows in a group into a single row with delimited data. – user1956557 Jan 21 '15 at 16:12

MS ACCESS Group Query

2 Answers2