Thanks for your interest.
Here is the query SQL:
TRANSFORM Sum(q_GenCondSurvey_v1.Replace_Cost) AS [The Value]
SELECT q_GenCondSurvey_v1.NetRplYear, Sum(q_GenCondSurvey_v1.Replace_Cost) AS [Total Of Replace_Cost]
FROM q_GenCondSurvey_v1
GROUP BY q_GenCondSurvey_v1.NetRplYear
PIVOT q_GenCondSurvey_v1.Building;
Here is the result (abbreviated a little) (I haven't managed to line up the columns properly in this post!):
NetRplYear Total All Garages Truck_Repairs Units1to8
0 £0.00 £0.00 £0.00 £0.00
2003 £2,500.00 £1,000.00 £1,500.00
2004 £10,000.00 £10,000.00
2005 £2,000.00 £2,000.00
2007 £10,000.00 £10,000.00
2008 £0.00 £0.00 £0.00 £0.00
2009 £0.00 £0.00 £0.00
2013 £0.00 £0.00
2018 £0.00 £0.00 £0.00
2019 £0.00 £0.00
2023 £0.00 £0.00
Despite what I said originally, I now see that some of the zeroes are in fact shown (sorry for misleading you!!). However, there are still some years missing (e.g. 2006). Also, only some of the zeroes are shown!
This crosstab query is indeed based on another query, which is this:
SELECT DISTINCTROW Gen_Cond_Survey.Surv_Ref, Gen_Cond_Survey.Surv_Date, Gen_Cond_Survey.Surv_Time, Gen_Cond_Survey.Building, Gen_Cond_Survey.Floor, Gen_Cond_Survey.Room_No, Gen_Cond_Survey.Room_Name, Gen_Cond_Survey.Locn_Type, Gen_Cond_Survey.Eqpt_Name, Gen_Cond_Survey.Asset_No, Gen_Cond_Survey.Eqpt_Category, Gen_Cond_Survey.Condition, Gen_Cond_Survey.Priority, Gen_Cond_Survey.Year_Made, Gen_Cond_Survey.Age, Gen_Cond_Survey.Force_Repl_Yr, Gen_Cond_Survey.Photo_Ref, Gen_Cond_Survey.Replace_Note, Gen_Cond_Survey.Replace_Cost, Gen_Cond_Survey.HSE, Gen_Cond_Survey.Function, Gen_Cond_Survey.Reliability, Eqpt_Categories.Typ_Life, Survey_Condition.[Condition Description], Survey_Priority.[Priority Description], IIf(NZ(Gen_Cond_Survey.Year_Made)<>0 And Eqpt_Categories.Typ_Life<>0,(Gen_Cond_Survey.Year_Made+Eqpt_Categories.Typ_Life),IIf(NZ(Gen_Cond_Survey.Age)<>0,(Year(Gen_Cond_Survey.Surv_Date)-Gen_Cond_Survey.Age+Eqpt_Categories.Typ_Life),0)) AS RplYear, IIf(NZ(Gen_Cond_Survey.Force_Repl_Yr)<>0,Gen_Cond_Survey.Force_Repl_Yr,NZ(RplYear)) AS NetRplYear
FROM Survey_Priority RIGHT JOIN (Survey_Condition RIGHT JOIN (Eqpt_Categories RIGHT JOIN Gen_Cond_Survey ON Eqpt_Categories.Category = Gen_Cond_Survey.Eqpt_Category) ON Survey_Condition.[Condition Code] = Gen_Cond_Survey.Condition) ON Survey_Priority.[Priority Code] = Gen_Cond_Survey.Priority;
This last query is based on a table. The calculated fields may be where my problem arises.
I'll go back and look at the underlying table to see that the zeroes, nulls etc are consistent. Meanwhile, I'd be grateful if you could indentify any obvious shortcomings in my queries!
Thanks,
Brian