A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Eliminating Dates based on a measure



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 09:11 PM posted to microsoft.public.access
Chuck W[_2_]
external usenet poster
 
Posts: 98
Default Eliminating Dates based on a measure

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
  #2  
Old May 27th, 2010, 11:06 PM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Eliminating Dates based on a measure

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.