0

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]
Kara
  • 6,115
  • 16
  • 50
  • 57
Zachary Smith
  • 59
  • 2
  • 4
  • 14

1 Answers1

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;
Luke Wage
  • 693
  • 4
  • 13