CODEWITHSUNDEEP
ms-access
cona
cona

Reputation: 189

How to write a calculated field using Microsoft Access expression builder?

I'm trying to write a query that will calculate "Shares short" divided by "Shares floating". See my table below. I've tried the following expression, but I just get a big fat error.

Expr1: [Shares]![SharesType]="Shares short"/[Shares]![SharesType]="Shares floating"

I've also tried looking at Microsoft's expression tutorials (and the linked tutorials) but they're too general.

Table with Original Data

Upvotes: 0

Views: 184

Answers (2)

cona
cona

Reputation: 189

This worked.

SELECT Companies.CompanyName, Sum(IIf([SharesType]="Shares short",[Shares],Null))/Sum(IIf([SharesType]="Shares floating",[Shares],Null)) AS [Short as % of Float]
FROM Companies INNER JOIN Shares ON Companies.CompanyID = Shares.[Company ID]
GROUP BY Companies.CompanyName;

So my report now looks like this.

enter image description here

Upvotes: 0

June7
June7

Reputation: 21370

I find expression builder more confusing than helpful and don't use it.

If you are trying to do an aggregate query, click the Sigma icon on query designer tab and build in the design grid. Switch to SQL view to view statement which should be like:

SELECT CompanyID, Sum(IIf([SharesType]="s",[Shares],Null))/Sum(IIf([SharesType]="f",[Shares],Null)) AS Expr1 
FROM Shares 
GROUP BY CompanyID;

Upvotes: 1

Related Questions