I'm building a db to analyze sales and sales vs budget. Two complications:
1. Budget numbers for the Fiscal Year are generated per Period but analyzed by week. Each of 12 periods has 4-5 weeks. The period budget amount needs to be divided by the number of weeks in the given period.
2. The data needs to be analyzed by 2 different sets of Fiscal Years that do not match up. I have a tblFY that lists Date(key) FY A, Period A, Week A, FY B, Period B, Week B.
This works well for sales, which are entered in tblSales: UnitID, SaleDate, Sales
The budget is done for FY A. I'm not sure how to organize a tblBudget, since the info is UnitID, Period#, Budget.
Since the 2 fiscal years don't line up, it would seem I would need to break down the Period Budget into a daily amount to line up with the dates in FY B then summarize by Period B.
I could do this in Excel with an Index/Match formula and a couple of tables - can anyone explain how this would work in Access?