The Code is as follows:
The structure of the landundertake table is as follows:
The structure of the table appraiser is as follows:
Access code:
Copy codeThe Code is as follows:
TRANSFORM First (Landundertake. valuerId) AS valuerId First
SELECT Appraiser. quarterId, Landundertake. landCode
FROM Landundertake inner join Appraiser ON (Landundertake. valuerId = Appraiser. valuerID) AND (Landundertake. quarterId = Appraiser. quarterId)
Group by Appraiser. quarterId, Landundertake. landCode
PIVOT Landundertake. valuerGrade;
SQL server code:
Copy codeThe Code is as follows:
Select dbo. appraiser. quarterID, dbo. landUndertake. landcode, case dbo. landUndertake. appraiserGrade when 'appraiserid1' then dbo. landundertake. appraiserID else null END as appraiserID, case dbo. landUndertake. appraiserGrade when 'appraiserid2' then dbo. landundertake. appraiserID else null END as appraiserID1
From dbo. LandUndertake inner join
Dbo. Appraiser ON dbo. LandUndertake. quarterID = dbo. appraiser. quarterID and dbo. landundertake. appraiserID = DBO. Appraiser. appraiserID
Group by dbo. appraiser. quarterID, dbo. landUndertake. landcode, dbo. landundertake. appraiserGrade, dbo. landundertake. appraiserID
The result of the cross tabulation query is as follows: