Prev: Create Linked Mailbox for All Users in External Domain
Next: Version Updates
From: Chuck W on 27 May 2010 16:11
Hello,
I receive a file from our IT Department each month called tblHBI that has
the following fields:
Month (i.e. 5/1/10, 6/1/10), Measure (Falls. Infection Control, MRTs) and
TotalCount. There are seven measures. The file has the sum of counts by
measure for the past two years. The file runs through December 2010 for each
measure. The file has a zero as a value for all future months where there is
no date (i.e. Falls = 0 for 7/1/10). There may also be zero values
legitimately where there were no occurances of something for given month.
The measures all have a set reporting deadlines that vary. For instance,
Infections are always one month behind, Falls are always two months behind,
and MRTs are always three months behind.

What I want to do is the delete or filter out records for reporting date
months that don't exist yet. For my June report, I want to delete or not
show all Falls records After April. For Infections I want to delete or not
show all records after May.

I created a table called tblEndDates that has CurrentMonth, Measure and
EndDate. It has 133 records and show the following:

CurrentMonth Measure EndDate
6/1/10 Infections 5/1/10
6/1/10 Falls 4/1/10
6/1/10 MRTs 3/1/10

Is there a way to use the follow table to filter out the records that I
don't want?

Thanks,

Chuck
From: KARL DEWEY on 27 May 2010 18:06
You are on the right track but ran too far.
Build tblMeasOffSet like this --
Measure OffSet
Infections 1
Falls 2
MRTs 3

In your query use this --
FROM tblHBI
WHERE tblHBI.Measure = tblMeasOffSet.Measure AND Format(tblHBI.Month,
"yyyymm") <= Format(DateAdd("m", -tblMeasOffSet.OffSet, Date()), "yyyymm")


--
Build a little, test a little.


"Chuck W" wrote:

> Hello,
> I receive a file from our IT Department each month called tblHBI that has
> the following fields:
> Month (i.e. 5/1/10, 6/1/10), Measure (Falls. Infection Control, MRTs) and
> TotalCount. There are seven measures. The file has the sum of counts by
> measure for the past two years. The file runs through December 2010 for each
> measure. The file has a zero as a value for all future months where there is
> no date (i.e. Falls = 0 for 7/1/10). There may also be zero values
> legitimately where there were no occurances of something for given month.
> The measures all have a set reporting deadlines that vary. For instance,
> Infections are always one month behind, Falls are always two months behind,
> and MRTs are always three months behind.
>
> What I want to do is the delete or filter out records for reporting date
> months that don't exist yet. For my June report, I want to delete or not
> show all Falls records After April. For Infections I want to delete or not
> show all records after May.
>
> I created a table called tblEndDates that has CurrentMonth, Measure and
> EndDate. It has 133 records and show the following:
>
> CurrentMonth Measure EndDate
> 6/1/10 Infections 5/1/10
> 6/1/10 Falls 4/1/10
> 6/1/10 MRTs 3/1/10
>
> Is there a way to use the follow table to filter out the records that I
> don't want?
>
> Thanks,
>
> Chuck
 | 
Pages: 1
Prev: Create Linked Mailbox for All Users in External Domain
Next: Version Updates