Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Location
    USA
    Posts
    2

    Question Unanswered: Expert Challenge: Complex Access 2010 Nested IIF argument - Can you HELP!

    I hope I can articulate this in a manner that makes sense...

    A sales person has 100s of customer relationships they must manage, in managing this relationship, we have identified 18 key activities to help strengthen the relationship. Each customer is in a 36 month contract, the sales person is required to complete 18 Activities within the 36 month term (these activities occur at different times within the 36 month term, please see attached). What I would like to deliver is a dashboard that provides a pivot and chart summary of when future activities are coming due for their customers.

    Attached is the sample data from my table, it includes the customer_number "123456", ActivityName "Introduction", Due_Date "06/01/2014". I need help developing an MS Access 2010 db that will provide future projections (dateadd based on the due date and when the activity must occur) on the following.

    Introduction letters must go out 30 days after due date (but they only occur once in the 36 month life cycle)
    Kickoff email should go out 30 days after due date (but they only occur once in the 36 month life cycle)
    True ups will occur once every quarter
    etc...


    Sales Person X has 2 introduction letters due in the month of August 2014, for the following customers.

    Sales Manager Xs team has 45 introduction letters due:
    10 due in July 2014,
    10 due in August 2014,
    25 due in September 2014, for the following team members

    (There would be a pivot table with slicers for the the Sales managers and Sales Person names)

    I would also like to include a chart that illustrates by activity how many are coming due (there would be a slicer for month and year)

    I hope this makes sense.

    Table Name Activity_Data

    Customer_Number ActivityName Due

    I think a datediff would allow me to do a future projection to include an all up count of what is coming due in 30, 60, 90, 120, 150, 180 days.....etc

    Click image for larger version. 

Name:	accessexample.JPG 
Views:	2 
Size:	60.0 KB 
ID:	15583

    Click image for larger version. 

Name:	accessexample2.JPG 
Views:	2 
Size:	127.6 KB 
ID:	15584

    What would be really cool, if I could create a form that will allow link to their email and send out the intro email and mark that activity complete. but this is a nice to have.... if there is anyone that skilled let me know.


    That is a lot, I hope it makes sense....

  2. #2
    Join Date
    Jun 2014
    Location
    USA
    Posts
    2

    Question Expert Challenge: Complex Access 2010 Nested IIF argument - Can you HELP!

    I hope I can articulate this in a manner that makes sense...

    A sales person has 100s of customer relationships they must manage, in managing this relationship, we have identified 18 key activities to help strengthen the relationship. Each customer is in a 36 month contract, the sales person is required to complete 18 Activities within the 36 month term (these activities occur at different times within the 36 month term, please see attached). What I would like to deliver is a dashboard that provides a pivot and chart summary of when future activities are coming due for their customers.

    Attached is the sample data from my table, it includes the customer_number "123456", ActivityName "Introduction", Due_Date "06/01/2014". I need help developing an MS Access 2010 db that will provide future projections (dateadd based on the due date and when the activity must occur) on the following.

    Introduction letters must go out 30 days after due date (but they only occur once in the 36 month life cycle)
    Kickoff email should go out 30 days after due date (but they only occur once in the 36 month life cycle)
    True ups will occur once every quarter
    etc...


    Sales Person X has 2 introduction letters due in the month of August 2014, for the following customers.

    Sales Manager Xs team has 45 introduction letters due:
    10 due in July 2014,
    10 due in August 2014,
    25 due in September 2014, for the following team members

    (There would be a pivot table with slicers for the the Sales managers and Sales Person names)

    I would also like to include a chart that illustrates by activity how many are coming due (there would be a slicer for month and year)

    I hope this makes sense.

    Table Name Activity_Data

    Customer_Number ActivityName Due

    I think a datediff would allow me to do a future projection to include an all up count of what is coming due in 30, 60, 90, 120, 150, 180 days.....etc

    Click image for larger version. 

Name:	accessexample.JPG 
Views:	1 
Size:	60.0 KB 
ID:	15585

    Click image for larger version. 

Name:	accessexample2.JPG 
Views:	0 
Size:	127.6 KB 
ID:	15586

    What would be really cool, if I could create a form that will allow link to their email and send out the intro email and mark that activity complete. but this is a nice to have.... if there is anyone that skilled let me know.


    That is a lot, I hope it makes sense....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •