Any experts out there: Complex Access 2010 Nested IIF argument - Can you HELP!

[NoJoke1]

This is my first post, I will try my best to reduce confusion with my question.

I have a table that includes data with 18 different activities for one customer and the activities are due at different times within a 36 month term.
I would like to create a query that will look at the [customer_number], "123456", the [activity_name], "introduction", [due_date], "06/01/2014" and add 30 days to the due_date for that customer.

Is there anyone out there in Microsoft Access Help Center land that can help me write an argument that will answer the above question. I will be ever so greatful.....

Please be advised, I am not sure if IIF statment is the best option. If there is a better way, please share your expertise.

I hope my question make sense, if not, ask and I will try to clarify.... I am not expert... beginner to intermediate level.

Thank you,
NoJoke

 

[plog]

I would like to create a query that will look at the [customer_number], "123456", the [activity_name], "introduction", [due_date], "06/01/2014" and add 30 days to the due_date for that customer.

This SQL will do just that:

SELECT customer_number, DateAdd("d", 30, due_date) AS ThirtyDaysAfterIntroduction
FROM YourTableNameHere
WHERE activity_name = 'introduction'

 

[gblack]

Assuming the items reference were just an example of the type of data... SQL code might be more like:

SELECT customer_number, activity_name, due_date, DateAdd("d", 30, due_date) AS ThirtyDaysAfterIntroduction
FROM YourTableNameHere

No?

 

[NoJoke1]

Now, that seems to be moving me in the right direction. Can I nest several criterias into the expression for the remaining 17 activities?

 

[plog]

Instead of walking us down the path one step at a time, how about you tell us where the path starts and the ultimate destination?

Provide 2 sets of data--A: some sample data from your table, and B: the final resulting data that you would be generated using A. Be sure to include table and field names. Post another image or use this format if typing it in:

TableNameHere
FieldName1, FieldName2, FieldName3
David, 32, 5/4/2008
Larry, 55, 6/9/2009
Sally, 12, 7/2/2010

 

[NoJoke1]

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




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.... :banghead:

 

[plog]

Your sample data is very lacking. You mention Sales Person X in your explanation, but don't have any field that designates Sales Person in your data; The same with Sales Manager; You provided pivot tables of what I think is resulting data but you didn't include the starting data so I could see how you got there; you included an image with tasks, but didn't explain what the cryptic values in the 'Timing' field meant or what the Month values were for.

B+ for you explanation, D- for your data. Try it again, no explanation this time, just post data. Give me data from your tables and then what data should result from your query based on that startind data.