I'm currently working on an Access database, and I would like to make a query that can fuse multiple rows into one, but keeping all the entries separated in differents columns.

The table i'm making my query on is called "Monthly", is used for monthly reports of activity and is made this way:

I would like to know if there is a way to transform it into this:

I first thought of using GROUP BY, but it's clearly not adapted.

The objective would be to format it on a SQL request, not to modify the structure/ how the data is stored.

Thanks in advance for anyone who answers.

CodePudding user response：

If it doesn't already exist, add an autonumber field to table.

Consider:

Query1:

SELECT ID, project, [date] AS Data, "D" AS Cat FROM ProjectData
UNION SELECT ID, project, turnover, "T" FROM ProjectData
UNION SELECT ID, project, margin, "M" FROM ProjectData;

Query2:

TRANSFORM First(Query1.Data) AS FirstOfData
SELECT Query1.project
FROM Query1
GROUP BY Query1.project
PIVOT DCount("*","ProjectData","Project='" & [Project] & "' AND ID<" & [ID]) 1 & [Cat];

Presume this will be a multi-year db so probably need some filter criteria to limit selection to one year.

Similar example Pivot Query in MS Access

For more about CROSSTAB queries, review http://allenbrowne.com/ser-67.html

Or emulate CROSSTAB (example for 2 months):

SELECT ProjectData.project, 
Max(IIf(Month([date])=1,[Date],Null)) AS Jan, Max(IIf(Month([date])=1,[Margin],Null)) AS JanMargin, Max(IIf(Month([date])=1,[turnover],Null)) AS JanTurnover, 
Max(IIf(Month([date])=2,[Date],Null)) AS Feb, Max(IIf(Month([date])=2,[Margin],Null)) AS FebMargin, Max(IIf(Month([date])=2,[turnover],Null)) AS FebTurnover
FROM ProjectData
GROUP BY ProjectData.project;

Should not use reserved words as names. Date is a reserved word.