Month, DATE, Week

[Ranjit kurian]

i need a query to view the dates and report names
example:

There are Three columns(Frequency, DAY, REPORT)in a TABLE, now my query
should give me the date as shown below in QUERY, only if the report is due
today(example:Fri)

Explanation of a TABLE
Daily: my query should show the reports(a,b,c,d) every day, if i have
mentioned Daily in ‘DAY’ column.

Weekly: my query should show the reports(a,b,c,d) only, if the given
DAY(Mon,Tue etc…) match with todays day

Monthly: if the current months week is week1, week2, week3 or week4 the
query should count the number of week in month and if the given DAY(week1,
week2,etc…) matchs with current week of a current month then the report name
should be viewed .


TABLE:
Frequency DAY REPORT
Monthly Week1 a
Daily Daily b
Weekly Mon c
Monthly Week2 d

QUERY:
REPORT DATE
d 12/09/08
b 12/09/08

 

[Allen Browne]

Can I suggest an alternative structure here?

Try a table with fields like this:
- TheReport Text name of the report to run.
- FirstDue Date/Time the day it is first due.
- Freq Number
- PeriodID Text

For the PeriodID you will use a combo, with a list of valid entries that can
be used with DateAdd(), so "d", "ww", "m", "q", or "yyyy". In combination
with the Freq period, this tells you how often to run the report, e.g.:
Freq PeriodID Meaning
1 d every day
2 ww every other week
1 m each month.

Now you need a counting table, with a Number field that contains a record
for each number from 0 to (say) 4000 (roughly the number of days in the next
11 years.) If you name the table tblCount, with a Number field called
CountID, you can use this code to populate it instead of typing in the
numbers:
http://allenbrowne.com/ser-39.html

Now create a query using both tables. There should be no line joining the 2
tables in the upper pane of query design. This is called a Cartesian
Product, and gives you every possible combination of the two. Type an
expression like this into the Field row in query design:
DueDate: DateAdd(PeriodID, Freq * CountID, FirstDue)

This query gives you every time each report is due, for it first 4000
occurences. Add whatever criteria you wish under this field to show the
reports due today, this week, this month, this year, whatever.

This technique is really useful for dealing with any kind of recurring
events. There's a sample database in this link that takes the idea further,
explaining how to cancel or reschedule particular instance of recurring
events, even when they are open-ended (i.e. there the series does not have a
scheduled end date):
http://allenbrowne.com/AppRecur.html

 

[Ranjit kurian]

actually my requirement is a Task Tracker, my query should view me all the
reports pending for that particular day, i have daily, weekly, monthly,
quertly reports, only onces in a year all the reports name and
frequency(daily, weekly, monthly, quertly) will be entered in a table, the
dates will not be in same day(tue, wed....) for every month, so i can't give
the exact date for each reports,

actually i hv a query for daily and weekly but i have stucked up in monthly
and quertly, the query is shown below.

IIf(([Report List]!Frequency="Daily" And Format(Date(),"ddd")<>"sun" And
Format(Date(),"ddd")<>"sat"),Date(),IIf(([Report List]!Frequency="Weekly" And
[Report List]!DAY=Format(Date(),"ddd")),Date()

when i run the above query it give me the date if the report is pending for
that day, so in the same way i need monthly also if the monthly report is
pending on that day then it should view

 

[Allen Browne]

I'm not exactly sure how you are calculating this, but this expression
should give you a value from 1 to 5 for the week of the month of today,
assuming week 1 starts on whatever day of the month if falls:
(Day(Date()) - 1 \ 4) + 1

 

[Ranjit kurian]

Is it possible to write a query, when i mention week1 in column DAY, the
query should calculate from today's() date and if its 1stweek of the current
month it should throw today's date. same way if i type week2 in column DAY,
the query should calculate from today's() date and if its 2ndweek of the
current month it should throw today's date..

 

[Allen Browne]

Use an IIf() expression in conjunction with the expression posted.

 

[Ranjit kurian]

Hi
thanks for advise...
can you please guide me the below IIf() expression i used for monthly, i hv
stuck up in date ()

IIf(([Report List]![Frequency]="Monthly" And [Report
List]![Day]="Week1"),(Date()....[from todays date calculate whether we are in
1st week or 2nd week of current month]

 

[Allen Browne]

Ranjit, I don't really follow what you are doing, and it's not worth
spending time on this data structure as it is non-nomalized.

Much of this depends on how you calculate it. The 'first week of the month'
could be the first 7 days (regardless of what day of the week they start
on), or perhaps it's the first full wee, or the first week that has a
Thursday in it, or something else.

If 'first week' means the first 7 days, these expression will return 1 for
that week:
((Day(Date()) - 1) Mod 4) + 1
I don't know what field you have 'week1' or 'week2' in, but you will need to
use just 1 to match the expression above.

You also seem to want to match the days. Hopefully you are are using the
numbers 1 - 7, and not Sunday, Monday, etc. If so, you can get the day of
the week as:
WeekDay(Date())

Now somehow you are trying to combine those expression, but please
understand that if you week starts on a Friday (because the first of the
month is a Friday), then the first day of the week won't match the first day
of the month.

You are closer to your data, so you probably do know what you want. I can't
see it, don't understand it, don't believe it's worth the time to sort out
this non-normalized approach, and so cannot write your expressions for you.

Hopefully there's enough there for you to sort it out.