Sorry, I at least don't fully understand you're problem. Could you give us a little information on the tables/fields involved? An example of what you're after, and what you're actually getting and the gap between the two might also go a long ways to helping us help you out.
ok let me try to explain... I have set up some data around patients who have future appointments and how long they are currently waiting. So i created the above field to place them into a week so we know how many patients are expected that week. But i still need to show a week where there are no patients waiting so i have consistant weeks being calculated. If there are no patients waiting say Week Commencing 12th April then its not shown.??
this is my SQL to the query i am running if that helps? i put in bold where i am stuck...
SELECT IIf([OP] In ("W401","W411","421"),"Knee",IIf([OP] In ("W371","W381","W391"),"Hip","Other")) AS HipOrKnee, Int([RTT]/7) AS RTTwks, IIf([RTTwks]<3,"0-3w",IIf([RTTwks]<6,"3-6w",IIf([RTTwks]<9,"6-9w",IIf([RTTwks]<12,"9-12",IIf([RTTwks]<=15,"12-15",IIf([RTTwks]>15,"over15w",False)))))) AS Bands, DatePart("ww",[TCI]) AS [Week no], IIf([TCI] Is Null,Null,([TCI]-Weekday([TCI])+2)) AS [W/C TCI date], KG_FinalEnglishPTLQry.Cons, KG_FinalEnglishPTLQry.PathwayLocator, KG_FinalEnglishPTLQry.[W Length], KG_FinalEnglishPTLQry.[W Date], KG_FinalEnglishPTLQry.TCI, KG_FinalEnglishPTLQry.DateAddedToWaitlist, KG_FinalEnglishPTLQry.RTT, KG_FinalEnglishPTLQry.CountOfField14, KG_FinalEnglishPTLQry.[Number of Outpatient Cancellations or DNA's], KG_FinalEnglishPTLQry.PCG, KG_FinalEnglishPTLQry.UnitNo, KG_FinalEnglishPTLQry.RefDate, KG_FinalEnglishPTLQry.MaxOfAttendanceDate, KG_FinalEnglishPTLQry.MaxOfLastDNAOrPatientCancell edDate, KG_FinalEnglishPTLQry.RefSource, KG_FinalEnglishPTLQry.RefSourceName, KG_FinalEnglishPTLQry.RefSourceInitiator, KG_FinalEnglishPTLQry.Specialty, KG_FinalEnglishPTLQry.SpecialtyCode, KG_FinalEnglishPTLQry.PatientName, KG_FinalEnglishPTLQry.Status, KG_FinalEnglishPTLQry.OP, KG_FinalEnglishPTLQry.[OP Name], KG_FinalEnglishPTLQry.RefSubscriptID
WHERE (((KG_FinalEnglishPTLQry.Cons)="BANJ" Or (KG_FinalEnglishPTLQry.Cons)="BANR" Or (KG_FinalEnglishPTLQry.Cons)="RAOJ"));