You need first get a distinct set of all possible combinations of user+game, using a cross join:
select distinct u.user, g.game
from game g
cross join user u
;
This will give below result:
| USER | game |
|-------|----------------|
| Chris | dungen |
| Chris | naruto |
| Chris | winning eleven |
| Gale | dungen |
| Gale | naruto |
| Gale | winning eleven |
| Donna | dungen |
| Donna | naruto |
| Donna | winning eleven |
Then you can join this resultset (using outer join) to the user table in this way:
SELECT *
FROM (
select distinct u.user, g.game
from game g
cross join user u
) x
LEFT JOIN user u ON x.user = u.user and x.game = u.game
;
The above query gives you the following resultset:
| user | game | USER | game | score |
|-------|----------------|--------|----------------|--------|
| Chris | naruto | Chris | naruto | 10 |
| Gale | dungen | Gale | dungen | 9 |
| Donna | winning eleven | Donna | winning eleven | 8 |
| Chris | dungen | (null) | (null) | (null) |
| Chris | winning eleven | (null) | (null) | (null) |
| Gale | naruto | (null) | (null) | (null) |
| Gale | winning eleven | (null) | (null) | (null) |
| Donna | dungen | (null) | (null) | (null) |
| Donna | naruto | (null) | (null) | (null) |
Next you can use the above resultset to generate a report using simple SUM and GROUB BY.
But first you need to replace all nulls with 0, because a sum of nulls gives null value.
In this way:
SELECT x.user, x.game,
sum( case when score is not null then score else 0 end ) as score
FROM (
select distinct u.user, g.game
from game g
cross join user u
) x
LEFT JOIN user u ON x.user = u.user and x.game = u.game
GROUP BY x.user, x.game
order by x.user, x.game
;
This query produces the following:
| user | game | score |
|-------|----------------|-------|
| Chris | dungen | 0 |
| Chris | naruto | 10 |
| Chris | winning eleven | 0 |
| Donna | dungen | 0 |
| Donna | naruto | 0 |
| Donna | winning eleven | 8 |
| Gale | dungen | 9 |
| Gale | naruto | 0 |
| Gale | winning eleven | 0 |
You can find a full runnable example here: http://sqlfiddle.com/#!9/657cdf/12
I am using MySql dialect of SQL in these examples, because it is available on SqlFiddle
but I belive that converting this to MS-Access shouldn't be a problem.
=== EDIT - how to convert this query into MS-Access dialect ===
I've tested this query on MS-Access 2010
There are only two things that are not supported by MS-Access:
MS Access doesn't support CROSS JOIN keyword.
It can be converted in this way: How to use cross join in access?
using "old" syntax with comma betwen tables:
SELECT DISTINCT u.user, g.game
FROM game g, user u
MS-Access doesn't support Case expressions
:
CASE WHEN condition THEN value1 ELSE value2 END
But you can use IIF function instead:
IIF( condition, value1, value2 )
more on this here: Does MS Access support "CASE WHEN" clause if connect with ODBC?
The whole query converted to MS-Access dialect:
SELECT x.user, x.game,
sum( IIF( u.score IS NOT NULL, u.score, 0)) as score
FROM (
SELECT DISTINCT u.user, g.game
FROM game g, user u
) x
LEFT JOIN user u ON x.user = u.user and x.game = u.game
GROUP BY x.user, x.game
ORDER BY x.user, x.game
And a result of the query (in MS-Access):
