The database tracks sales vs. budget for a multi-unit business. The fiscal year is divided into Periods (12) and Weeks (4-5 per period, changes each year).
Sales are recorded by unit by date.
Budget numbers are generated at the beginning of the year by Unit and Period.
I would like users to be able to enter the budget numbers by unit and period and use a query to then divide the period total by the number of days/period.
Am having trouble finding a way to do this.
tblPeriod: Fiscal Year, Period, #Days per Period. (Key FY & Period?)
tblBudget: Unit, Period, Budget -no key?
Sales to budget comparisons are only done by week and period, not daily. However, it seems the budget numbers must be broken down by date to be able to summarize by week and period.
Is there a way to accomplish this without users dividing all the Period budget numbers by date and entering manually?