Grab the value in one record and apply it to a calculation in multiple records.Query?

[Turncloud]

In my database I have multiple company records, along with details of their annual spend. There is also a record called Grand Total which has a value for the spend for all of these customers (this is an import from a spreadsheet produced monthly).


I need to create a calculated field which is the spend for each company as a percentage of the grand total.

i.e. Concentration = spend / grand total *100

How can I reference this in a calculated field, given that 'grand total' is sort of a query, e.g x = 'total' where 'reference'="grand total" ..?

[BluffMeAllIn]

Hi Turncloud,

A little confused by your inquiry TBH.

You have indicated you have a database with multiple company records and details of their annual spending. However you indicate you also have a grand total which essentially would be a sum of the annual spending for all companies correct, however you have indicated the grand total is actually an import from a spreadsheet produced each month?????

Obv I am not sure how your database is designed but at basics it would have a company table, and spending details table. Is the spending details what you mean is being imported into the database each month from an excel spreadsheet? When you indicate you need to create a calculated field is this for an output report or you want to create a calculated field within the table?????

In general it's rarily common or best practice to store calculated fields when they can easily be calculated on the fly for report outputs. However if you are created for a report or within a table you should simply be able to reference the grant total field in the table doing your calculation?

Any details regarding my questions would go a long way in being able to assist with your issue.

Thanks,
Dave

[Dal Jeanis]

SELECT
   ... various fields ...,
   DLookup("[MyTotal]","[MyTable]","[MyReference] = 'Grand Total'") As MyGrandTotal,
   MyTotal,
   (MyTotal/MyGrandTotal) As MyConcentration
FROM
   MyTable;