Weekly reminder query

[nejenkins]

i have a series of clients that need work doing. each one has house work on at a different frequency. some weekly, some 2 weekly, some 6 weekly etc. in additional these clients have garden work on at a different frequency

I would like to be able to add these clients to a database, say the work required (usually more than one form of work) and the frequency for each work form. Eg. john Smith, garden, every 2 weeks starting 1st April until 1st October and then John smith, house, every 4 weeks starting 1st June until 1st December.

The work would only ever be weekly. There are about 6 types of work I provide.



I would then like to be able to run a query every week which would then list the name and the work required that particular week. Basically a complicated calendar reminder for multiple, different jobs for multiple clients.

Any ideas how to do this?

[alansidman]

crossposted: https://www.mrexcel.com/forum/micros...ml#post5368314

[kd2017]

Hopefully a pro will step in and guide you shortly.

That said I'm guessing the most straight forward solution would be some vba to loop and create a reminder record in a reminder table for each occurance based on the requirements above. What's your vba experience?

Also guessing, the slickest way to do it might be to create a calendar table so that you can calculate the dates quickly on the fly with a fancy query... But I'm not sure how to do it that way.

Interesting question, I'll play around with it later. Google "database calendar table" returns some blog posts on the subject.

[ssanfu]

Interesting problem....

I have a few questions:
- How many jobs do you have per day per work type?
- What is a "week"? Mon - Fri? Mon - Sat?
- Does the client get to pick the DOW?
- Is there a fixed duration specified per work type?
-Could the duration for a work type vary between clients?
- Does the client get to pick the start time?
- What are the job types?
- What are the frequencies?

[ArviLaanemets]

I see 2 possible solutions:
1. Windows Scheduled Task on some server available 24/7 runs every 1/2/6/etc. week, opens the database, and runs appropriate queries for given schedule.
2. You migrate the front-end of your DB to SQL Server, and define there Job's, which will run every 1/2/6/etc. week, and call procedures which run appropriate queries.

You can't use any database events only, because they will run only when someone opens the database. You can never guarantee, that your database is opened in every day some of queries must run.

[nejenkins]

The week the unit of time. I do not need the granularity of which day it is.
Each client may have 0-6 jobs scheduled for the week depending on which jobs they are signed up for and the scheduling
There are six categories of work eg house, garden, windows, car.

I want a worker to be able to open the database and run a query (or similar) - the database will ‘known’ what week the current day is in, and give a list of the clients and jobs scheduled for that particular week. I Don’t think I need anything to run when the database is closed. I don’t need the database to ‘push’ notifications of work to the worker

Thanks for interests

[accesstos]

The week the unit of time. I do not need the granularity of which day it is.
Each client may have 0-6 jobs scheduled for the week depending on which jobs they are signed up for and the scheduling
There are six categories of work eg house, garden, windows, car.

I want a worker to be able to open the database and run a query (or similar) - the database will ‘known’ what week the current day is in, and give a list of the clients and jobs scheduled for that particular week. I Don’t think I need anything to run when the database is closed. I don’t need the database to ‘push’ notifications of work to the worker

Thanks for interests

So, the source of your information could be a query like this:

SELECT * FROM tblSchedule WHERE DatePart("WW",schDate)= DatePart("WW",Date());

You can use it as recordsource of a form or report.

Replace the tblSchedule and the schDate with the actual object names of the table of scheduled jobs.

Cheers,
John

[kd2017]

So, the source of your information could be a query like this:

SELECT * FROM tblSchedule WHERE DatePart("WW",schDate)= DatePart("WW",Date());

You can use it as recordsource of a form or report.

Replace the tblSchedule and the schDate with the actual object names of the table of scheduled jobs.

Cheers,
John

I think the problem is how to query/calculate reminder dates given the parameters [StartDate], [EndDate], and [RecurringFrequency].

The approach that I'm thinking would be to use a temporary table (in the front end) and some VBA to repopulate the table with the reminders every time the user clicks a button asking for this week's reminders.

@nejenkins,
FYI you can calculate the first day and last day of the *current* week using the Date() and Weekday() functions like so:

'Weeks are Sunday to Saturday
first_day_of_week = Date() - Weekday(Date()) + 1
last_day_of_week = Date() - Weekday(Date()) + 7
'OR last_day_of_week = first_day + 6

This assumes your first day of the week is Sunday. Otherwise you can add a parameter to the Weekday() function to change the first day of the week like so:

'Weeks are Monday to Sunday
first_day_of_week = Date() - Weekday(Date(), vbMonday) + 1
last_day_of_week = Date() - Weekday(Date(), vbMonday) + 7

You can also use these Date() and Weekday() functions in a query's criteria, but if your end up using a temporary table in the manner I mentioned above it wouldn't be necessary because the table would only contain data for the current week anyway.

[kd2017]

I've created and attached an example database.

*** I'm an amateur! I messed with this for fun. I'm not a pro and make no guarantees. I don't know if this is the best approach. THIS IS UNTESTED ***

When it opens a default form should pop up, there is a yellow button "view this week's schedule" at the top left of the form that will run VBA and populate the temporary table I've been talking about and then display the results in a query.

Some further changes I would make to this would be to split the database file into a back end and front end, and move the t_reminders table to the front end. In the database options I would set the db to "compact on close".

I would love to hear from the gurus their opinion on this solution.

[ssanfu]

Here is my design to think about.


My idea: there would be a form for the table "tblWorRequested", select client, work type, frequency and start/end dates. Click a button and code would calculate the weeks for the work - the week number would be stored in table "tblDatesScheduled", along with a client FK and work type FK.
Report would be based on "tblDatesScheduled", filtered by week number.