Results 1 to 5 of 5
  1. #1
    Pietleeu is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    3

    Scheduling/Tasks

    Good Day all,



    Some really interesting articles/posts in here sure learnt a lot by just scanning over some threads...

    I have a question regarding Tasks/Scheduling ... not too sure what the correct terminology is since im very new to access...

    I have a Solar Geyser/Heat Pump water heater business and part of my service is to provide a maintenance plan... My client base is building up to be very large now and keeping track of upcoming services is near impossible...

    Basically what I want to do is the following (and please advise if you would suggest an alternative way...

    I want to (well already have actually) import my client database into access.... I then want to add the clients Installation date and then I want to add a field that notifies me of a service date.. This is either annually or bi-annually... so I would like a way for acces to "add" a year or two years onto the installation date and then notify me, say a month in advance o this upcoming date..then obviously reschedule a year or two forward for the next annual or bi-annual service..

    Or am I aiming too high here.

    Kind Regards,

    Pietleeu

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    What you want to do is definitely possible. The first step in creating a database is to analyze what you want to do and what type of data you are going to capture. With that information, you design the table structure (tables & relationships between the tables); the table structure is the foundation for everything else, so it is well worth the time and effort to design it properly at the start.


    To help on the analysis part. Here are some questions that popped into my mind:

    You said that you provide multiple services (solar, geyser, water heater etc.). Can a customer buy many of these products/services from you over time & would each have a separate maintenance plan? Or would you bundle them together under 1 maintenance plan even though they may buy a water heater in 2013 and a heat pump system in 2015?

    Do you handle the maintenance plan as a separate item? In other words, let's say a customer buys a water heater from you. Do they have the option of not buying the maintenance plan for the water heater?

    Can the installation date of the water heater be different from the acceptance date of the maintenance plan? If so, which date would you use to plan for the next service call?

    Let's say a customer has had a service plan in place and the item has to be replaced at some point. I assume that you start the process over again with the new item (i.e. new maintenance plan). How do you currently obsolete the maintenance plan for the old unit? Do you need to make reference to the old unit/plan when establishing the new one?

    What are your longer range plans for the database?

    Are you going to track your employees & which plans are assigned to them?
    Do you have a need to capture the capabilities of each employee with respect to the units for which they have the expertise to fix/maintain?
    When you visit a customer to do a particular service, do you need to collect information on the other systems they have in hopes of selling them your services in the future for those other services? For example, let's say that you are at a customer to replace their heat pump. Do you make a note of the water heater they have and its approximate age/condition? That way you could follow up with them say in 6 months to a year to see if they are ready to replace that system.

  3. #3
    Pietleeu is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    3
    Good Day and thank you for the reply...

    I will answer as best I can...

    1 - Well basically I will have the following on the database... It will be just basic information and I would not at this stage require anything fancier than tracking the the next upcoming service date relative to the install date... In other words, I want to have the following.... customer name, system type, installation date, address, cust number, cust email, then an upcoming service date relative to the install date...

    My two different systems have two different service intervals, so i handle one client with two types of systems as two different "line items" ... The one will be one year intervals, the other two year intervals... that is the only two intervals i need...

    2-All our systems require a maintenance plan in order for the manufacturer to honor the warrantee, so the client has no choice...(the only difference is whether they pay us monthly or once off on the service date...

    3-We will only load the clients details once the system is bought, so as soon as we load the install date the countdown to the service starts... its one year or two years irrespective of whether they decide on the maintenance agreement on a monthly basis at a later stage(we will the work out a pro-rata rate for them... The fact is the Product Manual has to be stamped on the interval or the manufacturer wont honor the warrantee ...

    4- If the client needs it to be replaced, we will simply load it as a new client/item...so i guess if the old one is obsolete we will simply stop the maintenance countdown...

    5- Longer range plans.... Well for this particular one , I really only want to track the service dates.. We have an accounting programme that takes care of the rest.

    6- I do not need to track the employees , i have a "notes" field where i keep track of that..

    7- This is a very good point on the "other systems" ...like you mentioned with the water heater age and condition and we might incorporate it as an extra notification date.

    Thanx a million again... Im looking forward to your reply

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK, we'll keep it simple, but we still must follow the rules of normalization. First, we need a table to hold the basic customer information. This table will only hold the customer information, nothing about the systems they may buy is included in this table

    tblCustomers
    -pkCustomerID primary key, autonumber
    -CustomerNumber (you'll have to decide whether this field needs to be a text field or a numeric field)
    -txtFName (text field for the customer's first name --if an individual)
    -txtLName (text field for the customer's last name if an individual or you can use this field for the company name if the customer is a business/organization)
    -txtAddress
    -txtCity
    -txtState
    -txtPostalCode
    -txtPhoneNo
    -txtemail

    Now a table to hold the basic systems (i.e. water heater, heat pump etc.)

    tblSystems
    -pkSystemID primary key, autonumber
    -txtSystem
    -Frequency (a numeric field that provides the frequency at which the indicated system needs a service call--yearly (1), once every two years (2), 2x/year(0.5) etc.)

    For the frequency you will have to determine whether you want the frequency in number of days, number of weeks, months, years etc., but you will have to be consistent in which method you choose. For example, you may choose to express the frequency in terms of days 365 (yearly), 730 (every two years) etc. Alternatively, you could put the numerical value in the field Frequency and then the descriptor (annually, monthly) in a separate field in the table.


    Next we need a table to hold the systems that a customer buys

    tblCustomerSystems
    -pkCustSysID primary key, autonumber
    -fkCustomerID foreign key to tblCustomers
    -fkSystemID foreign key to tblSystems (identifies the system the specific customer buys)


    And finally a table to hold when events related to the customer/system occur (i.e. the installation and any subsequent service calls)


    tblServiceCalls
    -pkServiceCallID primary key, autonumber
    -fkCustSysID foreign key to tblCustomerSystems
    -dteService (date field for when the service was conducted)
    -txtService (a text field to describe the service; the initial installation would be a record in this table.

  5. #5
    Pietleeu is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    3
    Okay so I set up the tables and fields as mentioned above... but just a stupid beginner question... when you say for eg "-fkCustSysID foreign key to tblCustomerSystems" how do I make that connection ? I have done the relationship button but im not sure if its done correctly... How do i then check... and where to frome here?

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

Similar Threads

  1. Scheduling Presses
    By bbrazeau in forum Database Design
    Replies: 26
    Last Post: 05-09-2017, 02:38 PM
  2. Help designing a Scheduling database
    By DuWayne in forum Database Design
    Replies: 2
    Last Post: 12-31-2011, 09:30 AM
  3. scheduling appointments
    By mikejames in forum Programming
    Replies: 1
    Last Post: 09-30-2011, 04:45 PM
  4. Scheduling Problem
    By Marie in forum Queries
    Replies: 2
    Last Post: 01-19-2011, 09:10 PM
  5. scheduling db
    By JSHEL in forum Database Design
    Replies: 1
    Last Post: 12-12-2010, 09:28 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
  •  
Other Forums: Microsoft Office Forums