database - Grouping and adding values of grouped data. Problem with writing a query

Question

I have a csv file, I made a table from it in Access:

pastebin.com/pSVXsg5r

in the new table I would like to create four groups and assign them values.

• Group 1 EXP
• Group 2 KOM
• Group 3 PCK
• Group 4 TRP(new group name):HRL, L_F, LEA, PTP, SCH, TPE, UWA, VER.

I would like to display vdata1 vdata2 and HOURS for each user divided into groups. (sum of vdata1, sum of vdata2, sum of hours for each group EXP, KOM, PCK, TRP)

Here is my Database in Access, please hints or study materials

http://www.mediafire.com/file/dcska071cg4p2wh/Table.rar/file

标签： databasems-access

Answer 1

Calculate the group identifier and group on that field in aggregate query.

SELECT USERID, IIf([GRUPA] Not In ("EXP","KOM","PCK"),"TRP",[GRUPA]) AS GrpID, Sum(VDAT1) AS SumVDAT1, Sum(VDAT2) AS SumVDAT2, Sum(HOURS) AS SumHOURS
FROM Table
GROUP BY USERID, IIf([GRUPA] Not In ("EXP","KOM","PCK"),"TRP",[GRUPA]);

Or add another record to Groups:

TRP TRP

Then join tables:

SELECT USERID, LGTYP, Sum(VDAT1) AS SumVDAT1, Sum(VDAT2) AS SumVDAT2, Sum(HOURS) AS SumHOURS
FROM Groups INNER JOIN Table ON Groups.GRUPA = Table.GRUPA
GROUP BY Table.USERID, Groups.LGTYP;