Sum Query based on other queries

[echorley]

I have three different queries that find the sum of columns in three different tables. How can I then create another query that adds those columns together?

I tried to build an expression that looked like thie in the field portion of the query builder

Expr1: [qryFQDefScoreUs]![SumOfFQScoreUs]+[qryFQOffScoreUs]![SumOfFQScoreUs]+[qryFQSpecTeamUs]![SumOfFQScoreUs]

Then the total line was set to Expression.

This does not return a value.

Thanks!

 

[IgorB]

When you create each query just include some field which describes your summary.
Then in final query merge 3 queries, use this field as joint field and select each summary; select in the same final query virtual filed=Sum1 + Sum2 + Sum3.

 

[echorley]

I am baffled

without an example, your explanation does not help. I do not know what

"just include some field which describes your summary"

means to do.

I have figured out what the problem is though. If any one of the individual summaries do not have any values that are greater than zero, then the query returnes an empty value. If all three columns add up to a number greater than zero, then my query that adds the sum of the three sum queries together works.

I am not sure why the query does not return a value of zero.

 

[Jon K]

Try using the Nz() function to convert any null values to zeroes:-

Expr1: Nz([qryFQDefScoreUs]![SumOfFQScoreUs])+Nz([qryFQOffScoreUs]![SumOfFQScoreUs])+Nz([qryFQSpecTeamUs]![SumOfFQScoreUs])

 

[echorley]

I just stumbled onto the Nz expression to convert nulls to zeros. This is certainly what I need to do, but


Expr1: Nz([qryFQDefScoreUs]![SumOfFQScoreUs])+Nz([qryFQOffScoreUs]![SumOfFQScoreUs])+Nz([qryFQSpecTeamUs]![SumOfFQScoreUs])

still returns a null value.

When I do my original queries that finds the sum of each individual column, can I apply the Nz function there?