Link to home
Start Free TrialLog in
Avatar of jrflanagan
jrflanagan

asked on

Relative dates in Access query

I am trying to create a query that will return the revenue generated by each of our sales people at specified intervals (weekly) after they started in the role. (Revenue is tied to the Week Ending Date. The Week always ends on a saturday, if the person starts on a monday, their first week ending date is that first saturday)

Since everyone started on a different date, how can i create a query that will look at revenue during a sales period that is relative to their start date.....1 week after they started, 2 weeks after they started, etc.


I would like it to look something like this:

               Salesperson 1            Salesperson 2        Salesperson 3
Week 1           $100                           $150                          $0
Week 2           $150                           $ 200                         $25
Week 3           $175                           $175                          $25

Ideally,the "Week x" column would go until there is no additional data, but I could cap it at a specified number if necessary.

I've got a crosstab query that list all Week Ending dates, but need to somehow change the Week Ending date to a relative "Week X" .

TRANSFORM Sum([SalesData].Revenue) AS SumOfRevenue
SELECT [SalesData].[Week Ending]
FROM [SalesData]
GROUP BY [SalesData].[Week Ending]
PIVOT [SalesData].[Act Mgr];




Any ideas?

Thanks
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

At the root level you have to calculate the Sum of all of their sales in a query,...then pull out the weeks.

I am sure an expert here can whip up something based on just what you posted here.

As for me, I would need a basic sample of this database (with sample data), and a clear example of the exact output you want, based on that sample data.

JeffCoachman
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jrflanagan
jrflanagan

ASKER

I had to tweak it a little bit, but that did the trick. You turned what I thought would be a complicated solution into something very simple.

Thank you!