Query Database for Summary 1

[shelby55]

Hi

SkipVought offered me brilliant advice last year on a project I was working on. The new fiscal year is here and I have to update the template for use with 2010/11 but want to add something else. Hopefully someone (Skip?) can help.

My worksheet takes dates from a data entry page and copies to them to another page to calculates days each month between the cases:
SELECT `'09_10$'`.F5 AS 'referral', `'09_10$'`.F6 AS 'assessment'
FROM `'09_10$'` `'09_10$'`
WHERE (trim(`'09_10$'`.F5) Is Not Null)

Then to summarize the total days per month I used:
=SUMPRODUCT((dts>=D$1)*(dts<E$1)*(dts>=IF($A2="",TODAY(),$A2)+1)*(dts<=IF($B2="",TODAY(),$B2)))

The above is for the April column only but just showing what I used.

Then a summary for the total days is:
SELECT Sum(`ReferAssess$`.`Total Days`) AS 'Total Days', Sum(`ReferAssess$`.F4) AS 'Apr-09' <etc for full year of months>
FROM `ReferAssess$` `ReferAssess$`

Now I want to create a summary for patients that don't have an assessment date per month. So if a patient had a referral date of March 10 but didn't have an assessment date until July 1 then they would show as a person awaiting assessment in March, April, May and June.

How do I do this? Thanks.

 

[SkipVought]

You were, among other things, using an ABSOLUTE row and column reference...

=IF(AND(TEXT($A2,"yyyymm")=TEXT($B2,"yyyymm"),TEXT($A2,"yyyymm")=TEXT(C$1,"yyyymm")),1,IF(TEXT($A2,"yyyymm")<=TEXT(C$1,"yyyymm"),1,0)-IF($B2="",0,IF(TEXT($B2,"yyyymm")<=TEXT(C$1,"yyyymm"),1,0)))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}