Results 1 to 6 of 6
  1. #1
    kkbharat is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34

    Designing Followup system.

    Hi all,

    I am new to Access 2010 and am trying to build a followup system where I need to store customers data and data related to followups done by our sales people. I have designed two table in one I maintain customers details like Name, mobile number, email address etc. and in second table I maintain data related to followup like the status of followup, date of next action and next action to be taken etc.

    I have connected these two tables in one-to-many relationship where there are multiple records related to one customer. I want to build a system where in all the customers whose next action date is less than current date and based on this do the followup i.e. all the customers whose followup date is after current dated they should not get selected.

    Can anyone suggest the line of step I will need to take in order to achieve this. I hope I made some sense here.

    Awaiting early response.

    Thanks & regards



    Bharat

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How do you define FollowUpDate? When, where and how do you populate this field? That is what triggers a FollowUp in your business?

    Consider Customer -->CustFollowUp
    where CustFollowUp has fields such as

    FollowUPID PK
    CustomerID FK
    FollowUPDate
    FollowUpComment


    In general:
    If FollowUps start after a Sale or Order, then you would create a Followup based on that Activity.

    -find the Max(FollowUPDate) for a Customer
    -if today's date is >= that Max(FollowUpDate) for that Customer, then do a FollowUP
    -if today's date is < that Max(FollowUpDate) for that Customer, then no FollowUP is required

    If FollowUps occur on some defined frequency (3 months/90 days), then adjust to compare Max(FollowUPDate) + 90days to Today

    Good luck.

  3. #3
    kkbharat is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Quote Originally Posted by orange View Post
    How do you define FollowUpDate? When, where and how do you populate this field? That is what triggers a FollowUp in your business?

    Consider Customer -->CustFollowUp
    where CustFollowUp has fields such as

    FollowUPID PK
    CustomerID FK
    FollowUPDate
    FollowUpComment


    In general:
    If FollowUps start after a Sale or Order, then you would create a Followup based on that Activity.

    -find the Max(FollowUPDate) for a Customer
    -if today's date is >= that Max(FollowUpDate) for that Customer, then do a FollowUP
    -if today's date is < that Max(FollowUpDate) for that Customer, then no FollowUP is required

    If FollowUps occur on some defined frequency (3 months/90 days), then adjust to compare Max(FollowUPDate) + 90days to Today

    Good luck.
    Orange, thanks you very much for your response.

    I shall try to explain what I did up to now:

    I created 2 tables One tblCustomer with fields like CustomerID(PK), First Name, Second Name, Mobile No, EmailID and second table called tblFollowup with CustomerID(FK) FollowupDate, NextAction, Comment. This is to monitoring the followup one is suppose to make after the first visit of the customer till either the sale is made or is lost.

    Currently I am making data entries through a main and subform (for many side of the relationship) and the data entry part I have taken care of. Its just that I am trying to do this based on the followup date.

    The system I am trying to workout is where the sale person should get a list of customers whose followup date is, lets say, today (and in some case if the followup was missed by him then the followup date before today). When the followup call is made and if something needs to be done further then the sales person makes an entry specifying what further action needs to be taken and when is the next followup date is and then this date would become available in the followup list when it falls due on the current date whenever it might be.

    I have attached the screenshot for your reference. Hope this helps.

    Thanks once again,

    Bharat

    Click image for larger version. 

Name:	Table design for reference.jpg 
Views:	23 
Size:	135.3 KB 
ID:	30895

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Regarding date format review http://allenbrowne.com/ser-36.html

    Apply filter criteria. Use intrinsic tools or write code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a comment.....

    You should be using queries and forms/reports, not working directly with the tables.
    Access tables are NOT Excel spreadsheets!

  6. #6
    kkbharat is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Thanks everybody for your response.

    Yes basically I am trying to build a query so that I get a list of customers whose followup date falls due on current date which I am unable to workout. So as a solutions I sought your suggestions as to how to go about. And by the way it is not about date format its was just for your information.

    Any suggestions please.

    Thanks in advance,

    Bharat

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

Similar Threads

  1. Followup Table
    By aparnawangu in forum Access
    Replies: 1
    Last Post: 04-19-2016, 05:27 AM
  2. designing a query
    By saikat_datta in forum Queries
    Replies: 6
    Last Post: 12-27-2015, 11:01 PM
  3. Designing textbox
    By PoorCadaver in forum Access
    Replies: 3
    Last Post: 10-17-2011, 05:58 AM
  4. Help designing a table
    By webfactoryuk in forum Database Design
    Replies: 2
    Last Post: 04-13-2011, 08:39 AM
  5. Replies: 2
    Last Post: 04-12-2011, 10:14 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