write a query to retrieve data

[query]

I have three sheets in my workbook: Request sheet with request number
and date,Fault sheet with request number and faults count,and in the
third sheet I have month start date and end date. I want to retrieve
data such that : sum of all the faults of those requests which fall in
the month given in third sheet

Request Sheet
Request ID ActualEnd Date
1 1/22/2006
2 2/27/2006
3 1/22/2006
4 1/22/2006
5 1/22/2006

Fault Sheet
Request ID Total

1 6.75
2 3.75
3 7.50

third sheet
Jan-06 1/1/2006 1/31/2006 to get value

 

[Clivey_UK]

Hi,
There may be a better way of organizing your data, basically combinin
the first two sheets.
However, keeping to the format you already have, this is what I woul
suggest. This assumes that row 2 in Request and Fault is for ID #1, an
row 3 is ID #2 etc, and there are 100 rows of data. Also that the thir
sheet has the month in A1, the start of the month in B1 (Start Date)
and the end of the month in C1 (End Date).
Type the following in D1 and (importantly) press *Ctrl Shift Enter *t
accept the formula; this makes it an 'array' formula and you will se
{} around the formula; it won't work if you just press enter.
=SUM(IF(Request!B2:B100>=B1,IF(Request!B2:B100<=C1,Fault!B2:B100,0),0))

What the formula basically says is if the dates in Request are >= Star
Date AND dates in Request are <= End Date, then sum those same rows i
Fault sheet.

You may already be doing this, but to get Excel to calculate the Star
Date automatically where the month is shown in A1 of the third sheet
put this in B1:
=A1-DAY(A1)+1
This formula calcuates the first day of the month for any date. Yo
could use a simpler formula in your example, but this formula might b
useful in the future.
For Excel to calculate the end date in cell C1, simply use =B2-1 wher
it is taking one day off the first day of the next month. Or look a
the =EODATE formula.

Hope that helps. Let me know if it works.
Cliv