Hi, I have a crosstab query that

[briand2]

Hi,

I have a crosstab query that returns planned expenditure against individual buildings over a number of years. The result is something like this (the figures are not real!):

Year Bldg1 Bldg2 Bldg3
2003 £100 £200 £500
2004 £1000 £100 £25
2006 £25 £2000 £100
2007 £10000 £100 £350

You will notice that there is a "gap" between years 2004 and 2006, because no expenditure was found for any building for year 2005. However, I'd really like to see a row for 2005, with zero under each building?

Is this possible; can I do it by varying something in the crosstab query?

Any help would be most appreciated.

Brian

 

[stickers]

Is the crosstab getting its numbers straight from a table or are there other queries involved? I've had a similar thing - what I did was to make sure that there were zeros in the query before the crosstab - then the crosstab ought to turn out OK. Try posting the SQL - it might help others help you.

 

[Spellman]

You definitely need to post your query here. I would imagine that you need to amend your join properties. If you post the query as it stands we can have a look and amend it as necessary.

Mark

The key to immortality is to make a big impression in this life!!

 

[briand2]

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