• dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 121

Topic: Need help with database design for recurring checklists

Hi friends,


It has been a while since i have been active here but i have been lurking the forum. I sincerely hope everyone is well.


I have been thinking about making a little mvdb utility program to help me organize my daily office work, because it would really help me bring a bit of structure, but i am having troubles understanding (or planning) what the best way would be to set up my tables. Basically i need to execute recurring tasks for different clients.


I was wondering if anyone could guide me in the right direction or give me pointers.


The situation:
As of now i am using a simple excel file at the office with a checklist for different clients. There are routine checks i need to do for different clients. Depending on the client, and the kind of checks, the timing intervals are as follows: yearly/quarterly/monthly. So these are the recurring tasks.


Basically i have a table in excel, the table titles are the checks that need to be done, the first column is the client's name, the rest of the columns in the row are the dates or yes/no booleans if the check has been done. At the end of the check interval, lets say it is the monthly checklist, i will copy the excel tab to a new tab manually and restart the different checks. It would be ok to do this manually in mvdb.


What i would like to achieve:
I have made a quick sketch in excel to give an impression of what i would like to achieve. The "tablegrid" corresponds with my current "checklist" that i am using in excel. So i am looking to build a tablegrid like below:


https://i.ibb.co/TTmntLt/tablegrid.png


Where i could basically choose from a dropdown/list in the main form which checks i need to do.
For example for the year 2021 and the quarter 2, or the month 5, and so on.


However, i dont seem to be able to grasp how i need to setup my tables, i know this is wrong but am i going in the right direction with this or not? Look below:


https://i.ibb.co/pv667MW/tables.png


Yes, i am not good at this.


I would need these 3 functionalities:
- setup a standard checklist which can be reused
- a standard checklist can have different timing intervals, for example yearly/quarterly/monthly > this depends on the client
- a client can have many different checklists


Many thanks for taking the time to look at this.

Kind regards,

2 Reply by derek (edited by derek 2021-06-26 23:21:54)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,834

Re: Need help with database design for recurring checklists

Hi Kristof,
Nice to see you're still around.
I was having a think about how you might structure your database to give you what you need.  As always, there are lots of different ways and the attached is just a suggestion that might give you some ideas.
In order to get maximum flexibility, I use a checklist 'type' (month, quarter, year) when I create an empty checklist (form1 / form1a).
On Form2, if the selected checklist type is 'month' then a list of clients that have monthly checks is displayed.  If the checklist type is 'quarter' then a different list of clients with quarterly checks is displayed etc etc
To fill the selected checklist you can either
1.  Double click on any of the clients to add it into that particular period's checklist.
2.  Click on the 'COPY ALL' button at the bottom of the client list to add all clients for the selected 'type'..
Once you have added clients to the selected checklist,  double click on a row in the checklist to edit the details. 
The way it's structured, there are different checks (and a different number of checks) depending on whether it's by month, by quarter or by year - it may not be how you work but it's just to show that the way it's structured gives a lot of flexibility.
Finally, on Form1 as well as the checklist details, there is summary information (how many clients have been added, how many checks of certain types have been completed etc - it's basically an overview of the current 'status' of each checklist.
It probably sounds more complicated that it is.  Just give it a try and hopefully it might be of use.
Regards,
Derek.

Post's attachments

Attachment icon dbk1.zip 569.54 kb, 266 downloads since 2021-06-26 

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 121

Re: Need help with database design for recurring checklists

Hello Derek! Very nice to hear you again!


As always, thank you very very much for taking the time to make this elaborate example!! This is very helpful, it is almost exactly what i wanted to achieve! As you mentioned it is structured in a way which would have not been my first idea but i think it is really clever how it all fits together and the possibities the way of doing things like this creates. Thanks again for all your help!!!


PS: love the animation! big_smile