Sum the values between start and end date
in [Reports]
|
Prev: how do i format the group totals in report to show thousands etc?
Next: using iif and like in control Source of report
From: John Spencer on 18 Dec 2009 09:41 No Join solution. SELECT ActualStartDate_BAInput , ActualEndDate_BAInput , Sum(W.Day.[Day]) as CountOfDays FROM Table_POR_Final As P , Table_Workdays as W WHERE W.[Day]>=ActualStartDate_BAInput AND W.[Day]<=ActualEndDate_BAInput GROUP BY ActualStartDate_BAInput , ActualEndDate_BAInput You can also do this with a NON-equi join SELECT ActualStartDate_BAInput , ActualEndDate_BAInput , Sum(W.Day.[Day]) as CountOfDays FROM Table_Workdays as W INNER JOIN Table_POR_Final As P ON W.[Day]>=ActualStartDate_BAInput and W.[Day]<=ActualEndDate_BAInput GROUP BY ActualStartDate_BAInput , ActualEndDate_BAInput I'm a bit dyslexic with the greater than/less than operators and often get the comparison backwards. If this does not work try switching the operators. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County instereo911 via AccessMonster.com wrote: > Hi everyone, > > I have two tables Table_POR_Final and Table_Workdays > > On Table_POR_Final there are the following fields > ActualStartDate_BAInput > ActualEndDate_BAInput > > and on Table_Workdays are two fields "Day" and "Workday". On this table it > shows each day (day) and if it is considered a workday (1 or 0 on Workday) > > So what I want to do is say something like this > > Sum the Workday values on Table_Workdays between ActualStartDate_BAInput! > Table_Por_Final and ActualEndDate_BAInput!Table_POR_Final > > > So example > > ActualStartDate_BAInput 01/04/2010 > ActualEndDate_BAInput 01/08/2010 > > Workdays (sum of workdays) = 5 > > So the query would run and sum the numbers in between 01/04 and 01/08 > (1+1+1+1+1) and = 5 > > > Is this possible... Am i approaching it wrong (seems like I am) > > > Thanks all > |