hi
can any one help me to find the solution on my following problem
i am creating one Crostab query like done in ms Access. but there is one problem.
i have to use. FIRST() Aggregate function like SUM() AVG()
you can find help for that function from here..
http://www.w3schools.com/sql/sql_functions.asp
it says the FIRST Function is not supported in SQL Server 2000..
i want the same functionality as First() give. (First Row's Specified value of Group).
here i am giving you my sample query.
Code:
SELECT qryRisk_Matrix.SystemName, qryRisk_Matrix.SAPFunctionalLocation, qryRisk_Matrix.SystemID, qryRisk_Matrix.BusinessName,
qryRisk_Matrix.RegionName, qryRisk_Matrix.SchemeName, qryRisk_Matrix.CMSID, qryCapProjCount.CountOfProjectID,
qrySwppProjCount.SwppProjCount,
'W-WQ' =FIRST(CASE RiskEventRef WHEN 'W-WQ' THEN qryRisk_Matrix.Risk0 END),
'W-NH' = FIRST(CASE RiskEventRef WHEN 'W-NH' THEN qryRisk_Matrix.Risk0 END) FROM qrySwppProjCount INNER JOIN
(qryCapProjCount INNER JOIN
qryRisk_Matrix ON qryCapProjCount.SystemID = qryRisk_Matrix.SystemID) ON qrySwppProjCount.SystemID = qryRisk_Matrix.SystemID
WHERE (((qryRisk_Matrix.BusinessName) = 'Regulated Water'))
GROUP BY qryRisk_Matrix.SystemName, qryRisk_Matrix.SAPFunctionalLocation, qryRisk_Matrix.SystemID, qryRisk_Matrix.BusinessName,
qryRisk_Matrix.RegionName, qryRisk_Matrix.SchemeName, qryRisk_Matrix.CMSID, qryCapProjCount.CountOfProjectID,
qrySwppProjCount.SwppProjCount
Mitul N Koradia
Problem With FIRST () Agregate function
Linear Mode
