Pivot Query cant get the correct result

[atzdgreat]

Hi all im trying to achieve the below result with my pivot query but it seems not correct.

TO ACHIEVE:

KPIScore	UserID	KPICode	July 2024	June 2024
$4.50	XXX000035	FRACRT04	$4.00	$5.00
$0.00	XXX000172	FSACRT01	$0.00	$0.00
$0.00	XXX000172	FSACRT07	$0.00	$0.00

CODE:

TRANSFORM SUM(Score) AS FinalScore
SELECT AVG(Score) AS KPIScore, UserID, KPICode FROM KPI GROUP BY UserID, KPICode, FORMAT(DueDate, 'MMMM yyyy')
PIVOT FORMAT(DueDate, 'MMMM yyyy')

RESULT:

KPIScore	UserID	KPICode	July 2024	June 2024
$4.00	XXX000035	FRACRT04	$4.00
$5.00	XXX000035	FRACRT04		$5.00
$0.00	XXX000172	FSACRT01	$0.00
$0.00	XXX000172	FSACRT01		$0.00
$0.00	XXX000172	FSACRT07	$0.00
$0.00	XXX000172	FSACRT07		$0.00

 

[plog]

Use nz() to force your nulls to 0 values:

MS Access: Nz Function

This MSAccess tutorial explains how to use the Access Nz function with syntax and examples. The Microsoft Access Nz function lets you return a value when a variant is null.

www.techonthenet.com