Apply curve to results of query

[Spoonbill]

I have projected total orders from marketing events associated with the first
week of the event. The actual orders come in over 26 weeks. Is there a
query type or function I can use to apply the curve to the results and "step"
them out over time?
Columns would be weeks in the year, rows are marketing events defined by the
week they start. I eventually need to total orders and revenue by the week
they occur with all the overlapping marketing events. Thanks for any advice.

 

[scubadiver]

"Columns would be weeks in the year". If you mean fields, you could create a
separate field for each week number but you are better off having a table
with a weekID and the week date (that is how relational databases work).

Each week can have many events
Each event can have many weeks

Two primary tables and a junction table (with the two foreign keys) in the
middle.


As far as your question goes what do you mean by "applying a curve"?

 

[Spoonbill]

I'm picturing a datasheet that is 4 fields from my query plus 78 fields of
weeks, with 52 weekly marketing events.

The curve is a calculation of what part of the total orders and revenue
occurs in the first, second, and so on through 26 weeks. It's weekly
percentages, and the same for all weekly events.

So, the datasheet needs to multiply the week 1 percentage by the total
planned revenue or orders, and start the distribution in the week field that
the event begins. I could do this in excel, but want to keep it in my Access
project. I thought of some kind of crosstab query. Or, a combination of
conditional logic and lookup. Thanks for your thoughts and interest.

 

[Dale Fye]

It sounds like you have a "function" which gives you expected orders over
time, based on the actual orders that come in during the first week of a
marketing event.

I would think that this function might also vary by event type, so I think I
would have a table (tbl_Marketing_Sales_Projections) that looks something
like:

Event_Type_ID, WeekNum, Multiplier

Where Event_Type_ID represents the type of marketing event, WeekNum is the
week number (1 to 26), and Factor is a single or double field that contains a
multiplication factor based on your function, and represents the amount of
total sales attributable to that event, that occurs in the specific week.

If you then have a table(tbl_Week_One_Sales) that contains the following
fields:
Event_ID - relates to the actual event
Event_Start_Date - (this would probably be in your Events table)
Event_Type_ID
WeekOneSales

You could create a query (qry_Marketing_Sales_Projections)
that looks something like:

SELECT Event_ID,
Week_Num,
DateAdd("ww", WeekNum, Event_Start_Date) as EventWeekDate,
WeekOneSales * Multiplier as EventWeeklySales
FROM tbl_Week_One_Sales WOS
INNER JOIN tbl_Marketing_Sales_Projections MSP
ON WOS.Event_Type_ID = MSP.Event_Type_ID

This query could then be used to generate a Crosstab query that uses the
EventID as a RowHeader, Format(EventWeekDate, "yyyy/mm/dd") as a Column
header, and EventWeeklySales as the Value.

HTH
Dale