Adding numbers (grrrrr!)

[TechieJr]

I am trying to find a way of adding sums of numbers together.

We have to keep track of where our clients obtain funding from. There are 7 different pots they can choose from. For reporting purposes, we need to merge the values of what was chosen from pot1, pot2, and pot3. I have created queries which sum the values taken from each pot, but have not been able to add these sums together.

I've spent way too much time on this and am just about ready to go back to pen and paper. Can anyone shed some light on this?

Thanks,
TechieJr.

 

[GingerR]

so your query results look like this? three columns, one for each pot's sum?

Pot1Sum Pot2Sum Pot3Sum
10988 7886 7886

?

if so, just write another query, using the existing query as it's source, and do Pot1Sum+Pot2Sum+Pot3Sum.

if i'm way off track, please provide more details.

thanks--g

 

[Steve101]

Suggest you provide an example of a query which will not work.

If you're trying to add the values of different fields in the same record, and its not working, then it might be that some of the values contain NULLS. Anything added to NULL returns a result of NULL.

To get round this, use the NZ function to convert nulls to zero; eg. instead of A+B+C use NZ(A,0)+NZ(B,0)+NZ(C,)



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

sorry, last bit should read ....+NZ(C,0)

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TechieJr]

Hi all,

Here's a few more details about the issue. The query returns seven records, each with two columns - the name of the pot and the sum of money taken from that pot.

Fund Sum
financial inst. 10000
federal 15000
provincial 5000
municipal 2500
private 10000
owner 12500
other 7500

I need to be able to add the federal, provincial, and municipal sums and call the "Government". I also need to add the private and other together and call them "Other"

The SQL code from my query is as follows:

SELECT t_Client_ProjDetails.EquityGroup, Sum(t_Leverages.Amount) AS SumOfAmount, t_Leverages.Source
FROM t_Client_ProjDetails INNER JOIN t_Leverages ON t_Client_ProjDetails.ClientID = t_Leverages.ClientID
GROUP BY t_Client_ProjDetails.EquityGroup, t_Leverages.Source
HAVING (((t_Client_ProjDetails.EquityGroup)="youth") AND ((t_Leverages.Source)="Financial Institution" Or (t_Leverages.Source)="Federal" Or (t_Leverages.Source)="Provincial" Or (t_Leverages.Source)="Municipal" Or (t_Leverages.Source)="Other" Or (t_Leverages.Source)="Private" Or (t_Leverages.Source)="Owner"));

Thanks for the reminder about null values, Steve. I have had to battle that in my attepmts to get this working, although it is not in the above code.

Let me know if I can add anything further details to the discussion.

Thanks for your input,
TechieJr.

 

[GingerR]

i suggest that instead of hardcoding into your sql that you create a new table, with fields like

FUND ReportGroup
federal GOVT
provincial GOVT
municipal GOVT
private OTHER
other OTHER

then use this table in your query, joining on the FUND field, make it a TOTALS query and GROUP ON the 'ReportGroup' field (or whatever you want to call it). This way, some day when some other funding source exists, you don't have to go thru your entire database changing every query and form and report to accomodate how it is to be grouped/titled on your reports.