Query with null values

[Psyclone]

I haven't used Access in about 5 years and am having trouble with queries. I've tried reading tutorials, but they weren't much help for what I'm trying to do.

I have several tables for a sporting event. I want to calculate the total scores from each event (table) in the query. Here's what my tables look like...



TOURNAMENT 1 (Table 1)

PlayerName........Round 1.........Round 2.........Round 3.........Total
John................27..............26............ ..28..............81
Paul................32..............28............ ..24..............84
George..............29..............27............ ..30..............86
Pete................36..............35............ ..33.............104

----------------------------------------------------------

TOURNAMENT 2 (Table 2)

PlayerName........Round 1.........Round 2.........Round 3.........Total
Paul.................25..............27..............24.............76
John.................29..............28..............26.............83
George...............26..............30..............28.............84
Ringo................27..............30..............29.............86

-----------------------------------------------------------------------------

I'm trying to set up my query to sum the totals from each event. Right now, I just have an expression (Total 1 + Total 2) that gives me the totals for each event and it looks like this when I execute it...

QUERY

PlayerName........Event 1.........Event 2
Paul.................81..............83
John.................84..............76
George...............86..............84
Ringo................................86
Pete................104

-------------------------------------------------------------------------------

The player totals are showing even when they don't play in all events. When I add another field to the query with the expression "Grand Total: Event 1 + Event 2", it only adds the events if a player played in all of them and ends up looking like this when I execute it...

QUERY

PlayerName.......Grand Total
Paul.................164
John.................160
George...............170


----------------------------------------------------------

It isn't totaling the players who didn't play in every event. How do I get it to sum all the totals if someone is missing from a table?

Is the problem that it's trying to sum a Null value from the query fields?

[HiTechCoach]

It would help to know all the table and all the fields in each table.

Do you have a players table?

[Psyclone]

I got it. As soon as I found it, I remember using it before. I just had to use Nz(expr) to deal with the values.

Sorry, I honestly spent the last day or two searching (not continuously) for this answer and refamiliarizing myself with Access. Of course I found the answer about an hour after I posted this.

How do I set this thread as solved?

And yes, I have a players table that I was using too.

[HiTechCoach]

Thanks for the update.

Great job on getting it figured out.