Sum sickdays based on form entries

[holgi123]

Hi there,

any help on this would be very much appreciated.

I have a report for performance where I need to calculate networkingdays. I was able to get grossworkingsdays and holidays per user (grouping). The report is generated after I enter a timeframe on a form, i.e. 01/01/2006 until 31/01/2006. The sickdays are stored in a tblsick as this:

Name Jan Feb March April ....
A 1 1 2 5
B 0 2 4 1
Via DLookup the sickdays get correctly caclulated as long as the two dates I enter on the form are within the same month, as I format my entries so they match the tblsick column name and DLookup only looks for one column. So, for person A (B) the report correctly shows 1 (0) sickday if I entered on the form the range 01/01/2006 until 31/01/2006.

When I enter on the form a range like 01/01/2006 until 31/03/2006 I still get 1 sickday and not 2 (1+1). I understand this is because of the DLookup function.

My question is, what is the best way to overcome this and get all sickdays within the entered range? I thought about changing the tblsick structure to display one column per team member and enter each sickday individually (by row), something like this:

PersonA PersonB
03/01/2006 02/02/2006
04/02/2006 03/02/2006
.......... ..........

I would think this will somehow work but before going down that path and changing everything, is there a better and easier way. Also, entering sickdays day by day might be too much, rather than entering number of days for a total month?

Any help I would appreciate very much.

Many thanks for any feedback and assistance.

Cheers

 

[lynchg]

You should change your structure to this:

Two columns: Name, SickDate

Your data would look like this:

Name SickDate
John Doe 1/1/06
Jane Smith 2/4/06
Bob Jones 1/17/06

Then:

Select Name, Count(SickDate)
From tblSick
Where Sickdate Between DateRangeStart And DateRangeEnd
Group By Name

 

[holgi123]

Hi Lynchg,

thank you for your suggestion. I got so far in another way. However, the problem is, that I need the results of these queries on a report, which is structured by Name. So I have a report which shows performance by Sales Rep and I need to display orders by net working days, something like

Sales Rep A
Orders Grossdays Sickdays NetDays Orders per Day
30 20 5 15 2

Sales Rep B
Orders Grossdays Sickdays NetDays Orders per Day
15 20 5 15 1


I get all values besides sickdays. The report is triggered from a form, which specifies the daterange. With DLookup it only works, if the daterange is max. 1 months, because it stops after finding the first match.

This is where I'm stuck. I tried entering an SQL statement in control source on report, does not work. Yesterday I got so far in getting the sickday results in hidden fields on my form and than in the report I referenced the form control, but it only worked for the first sales rep in my report. I assumed it had to deal with the fact, that the value for [Name] in my report changes depending on the sales rep.

This is, where I'm lost at the moment.

Thanks