Single lines in crosstab query 1

[NeilT123]

I have the following crosstab query

TRANSFORM Sum(Format([NValue]*[OMApplicationRate],0)) AS TotalNapplied
SELECT tblFieldDetails.FieldName, tblFieldDetails.FieldCode
FROM tblSLTRpt, ((tblSLTFarm INNER JOIN tblFieldDetails ON tblSLTFarm.FarmAccountNumber = tblFieldDetails.FarmAccountNumber) INNER JOIN tblCropping ON tblFieldDetails.FieldCode = tblCropping.FieldCode) INNER JOIN (tblOMValues INNER JOIN tblOMApplications ON tblOMValues.ManureNameIndex = tblOMApplications.ManureNameIndex) ON tblCropping.CroppingNumber = tblOMApplications.CroppingNumber
WHERE (((tblCropping.CroppingYear) Between [tblsltrpt].[CroppingYear] And ([tblsltrpt].[CroppingYear]-3)))
GROUP BY tblFieldDetails.FieldName, tblFieldDetails.FieldCode, tblCropping.CroppingYear
ORDER BY tblCropping.CroppingYear DESC , [tblCropping.CroppingYear]-[tblSLTRPT.CroppingYear] DESC 
PIVOT [tblCropping.CroppingYear]-[tblSLTRPT.CroppingYear] In ("0","-1","-2");

Which produces a table like this:

FieldName FieldCode 0 -1 -2
Bottom Hill -220481657 160
Laverstock 1280257460 160
Malthouse -302126641 160
Top Hill 878632798 160
Behind Barn -778875919 160
Ellas 1107879673 164
Front 1014579349 160
Front 2 1273740956 160
Malthouse -302126641 144
Point 1818545355 160

(apologies for alignment of above table)

But what I am trying to get is only one line per field so in the above the line for Malthouse would be

Malthouse -302126641 160 144

Can anyone suggest how I should amend my code?

Thanks in advance

Neil

 

[PHV]

What about this (typed, untested) ?

TRANSFORM Sum(Format([NValue]*[OMApplicationRate],0)) AS TotalNapplied
SELECT D.FieldName, D.FieldCode
FROM tblSLTRpt R
, ((tblSLTFarm F INNER JOIN tblFieldDetails D ON F.FarmAccountNumber = D.FarmAccountNumber) 
INNER JOIN tblCropping C ON D.FieldCode = C.FieldCode) 
INNER JOIN (tblOMValues V INNER JOIN tblOMApplications A ON V.ManureNameIndex = A.ManureNameIndex) ON C.CroppingNumber = A.CroppingNumber
WHERE C.CroppingYear Between R.CroppingYear And (R.CroppingYear-3)
GROUP BY D.FieldName, D.FieldCode
PIVOT (C.CroppingYear-R.CroppingYear) In ("0","-1","-2")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Hi PHV, thanks for the quick response and yes that works.

Is the use of the A B C etc an accepted practice or were you just doing that to save time in your response?

 

[PHV]

an accepted practice
A recommended one ! (aliasing)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886