Access 2007, OVER clause workaround - missing operator error

Question

My query is designed to add an additional field named "WeightFactor" to the end of a table. My SQL code is below. When I attempt to run I receive the "missing operator error". I can see in previous questions that OVER() is a syntactically correct statement. The "WeightFactor" field should be a small percentage of 100% since I am taking rowprice/totalprice. When I remove the OVER clause the error goes away but the result is simply displaying 100% on each row, rather than the correct percentage of 100% on each row.

I've been warned that OVER is not supported in Access so was wondering if that was true and if so is there a workaround anyone knows about?

SELECT  [2-C: Avg Prices by Customer Number Query].[Part Number],
    [2-C: Avg Prices by Customer Number Query].[Customer Name],
    [2-C: Avg Prices by Customer Number Query].[Customer Number],
    [2-C: Avg Prices by Customer Number Query].SumOfQuantity,
    [2-C: Avg Prices by Customer Number Query].TotalCost,
    [2-C: Avg Prices by Customer Number Query].TotalPrice,
    [2-C: Avg Prices by Customer Number Query].[Gross Margin],
    [TotalPrice] / SUM([TotalPrice]) OVER() AS WeightFactor
FROM    [2-C: Avg Prices by Customer Number Query]

Answer 1

Why don't you create a second query to calculate the total of all items?

'TotalQuery
SELECT SUM([2-C: Avg Prices by Customer Number Query].TotalPrice) AS TotalPriceOver
FROM [2-C: Avg Prices by Customer Number Query];

Then you can use this one in your original query:

'OriginalQuery
SELECT [2-C: Avg Prices by Customer Number Query].TotalPrice,
[TotalPrice]/[TotalPriceOver] AS WeightFactor
FROM [2-C: Avg Prices by Customer Number Query], TotalQuery;