Multiply values in two tables based on filtered condtion in both tables

[Santhosh]

I have two tables: (a) Calendar dates (b) Monthly Expenses budgeted




Calendar_dates table field are
(a) Day (Calendar day for 4 months)
(b) Daily rates %


Monthly expenses budgeted table fields are


(1) Business line1
(2) Business Category
(3) Business Grooup
(4) Month1 expense budgeted amount
(5) Month2 expense budgeted amount
(6) Month3 expeense budgeted amount
(7) Month4 expnense budgeted amount


i need to multiply daily rates% for a particular day with the corresponding month expense budgeted amount in Monthly expense table. The output should be in following format:


(1) Calendar day (from days table)
(2) Business Line1
(3) Business group
(4) (Calendar_Dates.Daily rates%) * corresponding month expense budgeted amount




I am able to multiply all the daily rates for a single month expense budgeted amount in a simple query. If someone could help me to multiply based on the corresponding month criteria it would be very helpful

[June7]

Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

Exactly what data is in Day field - a full date?

Which months would be Month1 - Jan, May, Sep

Day is a reserved word and should not use reserved words as names for anything. Also advise not to use space nor punctuation/special characters in naming convention.

[Santhosh]

Sample data attached. Output worksheet is the result that i am expecting in MS-access query

[Santhosh]

I am looking at a daily calculation sample data is attached

[June7]

Monthy_Expense_Budget is not normalized structure. Your example output has wrong year for Jan and uses Jan date for Feb. Desired outcome can be accomplished with Cartesian query.

SELECT Calendar_Date.Date, Monthly_Expense_Budget.BusinessLine1, Monthly_Expense_Budget.BusCategory, Monthly_Expense_Budget.BusGroup,

Calendar_Date.WeightPct,

Format([Date],"yyyymmm") AS YrMon,
[WeightPct]*Switch([YrMon]="2019Dec",[Dec19BudgetAmt],[YrMon]="2020Jan",[Jan20BudgetAmt],[YrMon]="2020Feb",[Feb20BudgetAmt],[YrMon]="2020Mar",[Mar20BudgetAmt]) AS Result
FROM Monthly_Expense_Budget, Calendar_Date;

You will have to modify the query every time the months change.