Planning tasks

[xburgler]

Hi,

I'm developing a database to schedule some task with different frequencies in weeks (every week, every 2 weeks, every 12 weeks, etc). I need to see the tasks that I have to do for the week 34 / 2002 for example.

Does anyone know the best way to do it or does anyone know a good example for this.

Tanks in advance
Jorge

 

[ghubbell]

Hi Jorge!
I have a wonderful example for you. This will take some work on your part though....

Frequency Table:
FreqID Frequency Freq Unit DayEquiv
1 1 Week(s) 7
2 2 Week(s) 14
3 1 Month(s) 30
FreqID is an AutoNumber, Primary key.

tblItem Table: (Items) Single view form.
ItemID IDescription Obsolete (T/F)
1 Car 0
2 Cow 0
3 Kitchen Floor 0
ItemID is an AutoNumber, Primary Key.

tblops Table: (Operations)
OpID Description
1 Wash
2 Milk
3 Change Oil
OpID is an AutoNumber, Primary Key.

tblSched Table: (Schedule)
ItemID FreqID OpID SchID
1 1 1 1
2 1 2 2
1 3 3 3
3 1 1 4
ScheduleID is an autonumber. ItemID/FreqID/OpID are the primary key.

tbllog Table: (Log Table)
SchID Date By Note
1 2001-04-01 Gord Bla Bla Bla
2 2001-04-07 Gord More Bla
3 2001-04-02 Jorge Something other than Bla
Foreign Key SchID from Schedule Table. Allow Duplicates in all.

From these tables build 5 forms, each based on their respective tables. Try to set up combo boxes as the field lookups in each table first before you build the forms...Saves lots of work!

The "Reminder" form: Single form view.
This takes 2 queries to drive it: The first:

SELECT tblsched.ItemID, tblsched.FreqID, tblsched.OpID, tbllog.SchID, Last(tbllog.Date) AS LastOfDate
FROM tblsched LEFT JOIN tbllog ON tblsched.SchID = tbllog.SchID
GROUP BY tblsched.ItemID, tblsched.FreqID, tblsched.ItemID, tbllog.SchID
ORDER BY tbllog.SchID;

Saved as QryScheduleA

The second:

SELECT tblitem.IDescription, tblops.Description, [LastofDate]+[DayEquiv] AS OverDue, QryScheduleA.ItemID, QryScheduleA.SchID
FROM ((QryScheduleA LEFT JOIN tblfreq ON QryScheduleA.FreqID = tblfreq.FreqID) LEFT JOIN tblitem ON QryScheduleA.ItemID = tblitem.ItemID) LEFT JOIN tblops ON QryScheduleA.MaintenID = tblnops.OpID
WHERE ((([LastofDate]+[DayEquiv])<=Now()+2 Or ([LastofDate]+[DayEquiv]) Is Null) AND ((tblitem.Obsolete)=0))
ORDER BY QryScheduleA.ItemID, [LastofDate]+[DayEquiv];

Base your &quot;Reminder&quot; form off of the second query.
You don't need to show SchedID or ItemID. Show IDescription, Description and &quot;Overdue&quot;
Add 1 label &quot;LblOverdue&quot; besides the available fields: Caption: Overdue!

In the forms On current Event:

On Error goto FCErr
If IsNull(Me.Overdue) Or Me.Overdue < now Then
If IsNull(Me.Overdue) Then
Me.lblOverdue.Visible = True
Me.Overdue.Visible = False
End If
If Me.Overdue < now Then
Me.Overdue.Visible = True
Me.Overdue.ForeColor = vbRed
Me.LblOverdue.Visible = True
End If
Else
Me.lblOverdue.Visible = False
Me.Overdue.Visible = True
Me.Overdue.ForeColor = vbBlack
End If
FCexit:
Exit Sub

FCErr:
Resume FCexit

All this should be pretty close to working. I didn't just dream this up...Well, I did but a long time ago and have modified it a little for your needs. Connect your relationships, load in some data (maybe even with something that must be overdue by the time you would be finished.) Open your &quot;Reminder&quot; form and see if it works. I have all forms as subforms on the item form, and as you move items, the others can follow. It is not as tough as it looks and can probably be up and running in a half a day. Enjoy! Gord
ghubbell@total.net

 

[xburgler]

Thanks ghubbell.