Divide Each Value By Sum of Column

[Evagrius]

Hello,

I thought this would be simple. I am trying to divide each unique value in a column by the total column; so sales by total sales . . what am I doing wrong, please?

[sales]/sum([sales])

 

[plog]

You can't mix levels in a query. You can't show individual values and use aggregate values in the same query. You would have to do it in a subquery.

Create a sub query to get your totals, then use whatever the datasources the individual records are in and link to that subquery with the totals.

 

[AlexN]

Hello,

I thought this would be simple. I am trying to divide each unique value in a column by the total column; so sales by total sales . . what am I doing wrong, please?

[sales]/sum([sales])

• Give your Sum([Sales]) a name (alias) like TotalSales:Sum([Sales]).
  
• Save your query and create another one based on this, where columns are not calculated any more, they are plain numbers. There you can perform your division.
  
• If you really want to calculate sales percentage of total you should use:

  ([TotalSales]-[(Sales])/[Sales]

  and format it as percentage.
  
• Stop thinking like you're using Excel.

edit:
Beaten by plog

 

[Evagrius]

Thank You, much - I followed your direction.

You're right AlexN - I really am thinking Excel. I've only used Excel for the past few years and I am having a tough time separating the two . . will keep trying.

 

[namliam]

Rule number 1 for making the transition from excel to access, a spreadsheet is totaly different from any database. When working in access, forget all you think you know about excel and start from scratch.