Results 1 to 10 of 10

Weekly reminder query

  1. #1
    nejenkins is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2015
    Posts
    14

    Weekly reminder query

    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?

  2. #2
    alansidman's Avatar
    alansidman is offline Mountain Living Proponent
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,429

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    441
    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.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,436
    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?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Join Date
    Apr 2017
    Posts
    980
    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.

  6. #6
    nejenkins is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Jun 2015
    Posts
    14
    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

  7. #7
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    275
    Quote Originally Posted by nejenkins View Post
    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:
    Code:
    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

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    441
    Quote Originally Posted by accesstos View Post
    So, the source of your information could be a query like this:
    Code:
    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.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    441
    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.
    Attached Files Attached Files

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,436
    Here is my design to think about.
    Click image for larger version. 

Name:	Relationships1.png 
Views:	13 
Size:	85.7 KB 
ID:	40145

    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.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Weekly Metrics Query/Report
    By Rangerguy2000 in forum Queries
    Replies: 4
    Last Post: 12-05-2018, 10:54 AM
  2. Query calculating total weekly enquiry nos
    By aparnawangu in forum Queries
    Replies: 1
    Last Post: 01-04-2016, 11:26 PM
  3. Problem with a weekly query at month end
    By Gryphoune in forum Queries
    Replies: 8
    Last Post: 08-05-2014, 03:11 PM
  4. Weekly reporting comparisons in query
    By Schon731 in forum Queries
    Replies: 3
    Last Post: 10-21-2010, 06:59 PM
  5. Weekly calculation query?
    By katie_88 in forum Queries
    Replies: 1
    Last Post: 07-13-2010, 10:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums