Sum the values between start and end date

[instereo911 via AccessMonster.com]

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

 

[KARL DEWEY]

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?

 

[instereo911 via AccessMonster.com]

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.

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

 

[KARL DEWEY]

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.

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.

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

--



.

 

[Dale Fye]

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

 

[John Spencer]

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