Query that joins other queries is not calculating

[Guest]

This is what I have:

Table1 contains two fields for Team names and players last names. There are
3 different fields for last names (LN1, LN2 and LN3). Not all of them are
filled (a team only has two members).

Table2 contains the records.

Date LN Points
5/31/2007 Smith 50
5/31/2007 Williams 60

and so on.

Query1 retrieves LN1 (table1) and the points for each date
Query2 retrieves LN2 (table1) and the points for each date
Query3 retrieves LN3 (table1) and the points for each date

TtlQuery is supposed to sum the points for LN1, LN2, LN3 for each team, but
it doesn't calculate the total points for a team when the team has only 2
players.

Could someone please help me figure out how to do this correctly?

Thank you.

 

[John W. Vinson]

This is what I have:

Table1 contains two fields for Team names and players last names. There are
3 different fields for last names (LN1, LN2 and LN3). Not all of them are
filled (a team only has two members).

Table2 contains the records.

Date LN Points
5/31/2007 Smith 50
5/31/2007 Williams 60

and so on.

Query1 retrieves LN1 (table1) and the points for each date
Query2 retrieves LN2 (table1) and the points for each date
Query3 retrieves LN3 (table1) and the points for each date

TtlQuery is supposed to sum the points for LN1, LN2, LN3 for each team, but
it doesn't calculate the total points for a team when the team has only 2
players.

Could someone please help me figure out how to do this correctly?

Thank you.

Use NZ() when you're summing the points. Any expression involving NULL returns
NULL!

You didn't post your expression, but it might be something like

TotalPoints: LN1.Points + LN2.Points + LN3.Points

Instead use

TotalPoints: NZ(LN1.Points) + NZ(LN2.Points) + NZ(LN3.Points)

NZ (Null To Zero) will convert NULL values to a numeric 0 which will add
correctly.

John W. Vinson [MVP]

 

[sergio.pringle]

Use NZ() when you're summing the points. Any expression involving NULL returns
NULL!

You didn't post your expression, but it might be something like

TotalPoints: LN1.Points + LN2.Points + LN3.Points

Instead use

TotalPoints: NZ(LN1.Points) + NZ(LN2.Points) + NZ(LN3.Points)

NZ (Null To Zero) will convert NULL values to a numeric 0 which will add
correctly.

John W. Vinson [MVP]

Thank you very much.