Access Query to multiply values against percentages for a set of data

[Ryan5896]

Hello,



I am trying to create a simple database that will output a 12 month spread of department costs using multiplication.

I am needing the query to have 12 expression columns for Jan-Dec that calculates and spreads Jan-Dec costs within the table named "tblForecast_Dept_Allocation", but it needs to spread those costs unique to business unit, department number, and account number.

For example: Business Unit 42701 has 49 unique departments listed with a percentage noted in the "tblForecast_Dept_Allocation" table that sums to 100%. I need to create a query that will multiply the costs in Jan-Dec stored within table "tblForecast_Dept_Allocation" so that end result is 12 months of costs but re-spread at the 49 unique departments per each account listed. Same result happening for the other two business units listed.

I took an attempt at creating the query using expression but can not seem to figure it out. I have attached my work so far. Any help or pointers is much appreciated!

Thank you,
Ryan

[June7]

Strongly advise not to use space in naming convention.

You are omitting the "tbl" prefix of table names in the query.

"Expression" needs to be "Sum" or "First". However, GROUP BY in this query serves no purpose. There will be 3128 records with or without aggregation because the GROUP BY fields combined are unique for each record.

[Ryan5896]

Thank you!!