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: instereo911 via AccessMonster.com on 17 Dec 2009 15:23 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1
From: KARL DEWEY on 17 Dec 2009 16:46 >>On this table it shows each day (day) and if it is considered a workday (1 or 0 on Workday) Does the 'day' field contain the name of the day like Monday? How are the two tables related? -- Build a little, test a little. "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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1 > > . >
From: instereo911 via AccessMonster.com on 17 Dec 2009 16:56 Hi Karl, I am not sure how to relate these tables. The day field contain a date. example 01/01/2010, and the workday contains 1 or 0 (depending if it is a workday that i am counting). so table example Day Workday 01/01/2010 0 01/02/2010 0 01/03/2010 1 etc 12/31/2010 0 The other table (Table_POR_Final) has two fields that i need to relate (ActualStartDate_BAInput and ActualEndDate_BAInput) which are both dates (example 01/01/2010, 01/02/2010 etc I hope that helps. KARL DEWEY wrote: >>>On this table it shows each day (day) and if it is considered a workday (1 >or 0 on Workday) >Does the 'day' field contain the name of the day like Monday? > >How are the two tables related? > >> Hi everyone, >> >[quoted text clipped - 25 lines] >> >> Thanks all -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1
From: KARL DEWEY on 17 Dec 2009 18:53 Try this - SELECT Sum([Workday]) AS My_Days_Worked FROM [Table_Workdays], Table_POR_Final WHERE [Day] Between [ActualStartDate_BAInput] AND [ActualEndDate_BAInput]; -- Build a little, test a little. "instereo911 via AccessMonster.com" wrote: > Hi Karl, > > > I am not sure how to relate these tables. The day field contain a date. > example 01/01/2010, and the workday contains 1 or 0 (depending if it is a > workday that i am counting). > > > so table example > Day Workday > 01/01/2010 0 > 01/02/2010 0 > 01/03/2010 1 > etc > 12/31/2010 0 > > The other table (Table_POR_Final) has two fields that i need to relate > (ActualStartDate_BAInput and > ActualEndDate_BAInput) which are both dates (example 01/01/2010, 01/02/2010 > etc > > > I hope that helps. > > > KARL DEWEY wrote: > >>>On this table it shows each day (day) and if it is considered a workday (1 > >or 0 on Workday) > >Does the 'day' field contain the name of the day like Monday? > > > >How are the two tables related? > > > >> Hi everyone, > >> > >[quoted text clipped - 25 lines] > >> > >> Thanks all > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1 > > . >
From: Dale Fye on 18 Dec 2009 09:04
I assume that Table_POR_Final contains some other fields as well, so I'll propose a solution that includes the PK (assumed to be ID) from that table. Select Table_POR_Final.ID, Sum([tbl_Workdays].[Workday]) as WorkdayCount FROM Table_POR_Final, Table_Workdays WHERE Table_Workdays.Day BETWEEN table_POR_Final.ActualStartDate_BAInput AND table_POR_Final.ActualEndDate_BAInput GROUP BY Table_POR_Final.ID ---- HTH Dale |