Access 2007 OVER clause workaround

[MrSmith]

I am attempting to add an additional field to a table. The field will be a percentage of 100% in that TotalPrice/Sum([TotalPrice]). I don't believe the OVER clause works in Access, so was curious if someone had a suggestion in terms of a workaround. My SQL is below, it's receiving a "missing operator error" currently. I tried to create a new query that would return only SUM([TotalPrice]) and then use that query as my denominator but returned no results. Any suggestions would be heavily appreciated. Thanks!

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]

[Dal Jeanis]

You'll need a subquery in that spot. I'm not familiar with Over() - since there are no parameters, I assume that's going to give you the total from the whole query. So, include the same query again with an alias, and alias the first query just to reduce the typing, and you get something like this -

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

If Over() was doing some secret handling of grouping, like only giving you the Sum for each [Customer Number], then you'd have to include all the grouping fields into the Q2 subquery and specify the JOIN criteria.

That would look somewhat like this one, but it's total aircode....

SELECT 
Q1.[Part Number],
Q1.[Customer Name],
Q1.[Customer Number],
Q1.[SumOfQuantity],
Q1.[TotalCost],
Q1.[TotalPrice],
Q1.[Gross Margin],
Q1.[TotalPrice] / Q2.SumTotalPrice AS WeightFactor
FROM [2-C: Avg Prices by Customer Number Query] AS Q1
INNER JOIN
  (SELECT Q2.[Customer Number], SUM(Q2.[TotalPrice]) As SumTotalPrice
  FROM [2-C: Avg Prices by Customer Number Query] AS Q2
  GROUP BY Q2.[Customer Number])
ON Q2.[Customer Number] = Q1.[Customer Number]
);

This thread had some correlated subquery SQL that was more solid: https://www.accessforums.net/access/...ess-35607.html

[MrSmith]

Excellent thanks for your help!

[Dal Jeanis]

You're welcome.

It looks like ... " SUM([TotalPrice]) OVER([Customer Number]) AS SumTotalPrice " ... serves the same function in Oracle and SQL Server as that entire semi-correlated subquery in my second example does in Access/Jet SQL. (Okay, it's technically not a subquery at all, but I came *that* close to coding it as one, before I thought of a better way.)

I'd love to see that clause implemented in Access. I don't know how they'd represent it Visually, but it would simplify the joins/SQL immensely for "partitions" that people need all the time.