Question Calculation Accross Tables...ish

cwitton

New member
Local time
Today, 05:19
Joined
Feb 20, 2012
Messages
7
Hello...bit of a challenge.

I am building a database and need to perform what essentially is a simple percentage calculation, with a twist.

Background:
Two tables: One with the 'percentages' of which there is the offered percentage the company wants to buy of the total, the estimated acceptance, and the final percentage that is bought.
The second table has the exposures.

Usually our company would just multiply its percentage share by the total value of the stock. What I want to do is say that if there is a final percentage, to use that in the calculation accross the two tables. If there is no final percentage, to use the estimated acceptance. If no estimate then use the offered percentage.

Any clues. Did I mention this was not all on one talbe but accross two. If that makes any difference.

Thanks,


CW
 
I'm not convinced by the table structure, but I digress.

A simple NZ function should do what you want

ie

Code:
SELECT  tblStock.[StockValue] * NZ(tblpercentages.[FinalPercentage], NZ(tblpercentages.[estimatedPercentage], tblpercentages.[offeredPercentage])) as FinalOuput
FROM tblPercentages
INNER JOIN tblStock ON
etc etc

So if the Final percentage column is NULL use the estimated Percentage figure instead, if that's NULL, use the Offered Percentage.

A table structure that gives you columns with lots of NULL values in them tends to indicate an issue in the design.
 
Last edited:
Thanks for the response. The NZ function does not seem to like the three options - it can address using the estimated percentage if there is no final percentage...but not the offered percentage if there is only that.

Also...never used Inner Join before, please elaborate?

Many Thanks,

C
 
You can nest NZ as per the example above (note the second NZ to cater for the second column also potentially being NULL).

a JOIN does exactly what it says on the tin, it JOINS One or more tables together. I hope you'll forgive me, but I don't intend to rewrite the reams of information on fundamental SQL principles that's already available via a quick google search.

INNER JOIN tells the database to JOIN the two tables using the columns specified in the ON part of the of the JOIN and display results where there are matching values in both tables.
 
Cheers...got it to all work out fine. Appreciate your help on this.
 

Users who are viewing this thread

Back
Top Bottom