SQL table join + count help

[j420exe]

I have 3 tables: ARTISTS, ART and PAINTINGS
ARTISTS has columns (data):
userid (adam, betty, gil, jack, larry, steve, tyler)
full_name (firstname lastname)

ART has columns:
art_id (auto number created when new art_name added via application)
art_name (name of piece of art entered)
designed_by (populated with userid)

PAINTINGS has columns:
paint_id (auto number created when new painting_name added via application)
painting_name (name of painting entered)
painted_by (populated with userid)

I need a sql statement for a report that will return a list of the userids, counts(art_id), and count(paint_id) .

Some userids will exist in both tables some will not. If not then count = 0.

Would like result set format to look like this:

artist ART_CNT PAINT_CNT
adam 1 5
betty 3 0
gil 4 4
jack 6 0
larry 2 2
steve 0 9
tyler 3 7

I can get the userids and counts from each table but don't know how to join or merge the sql to get the result set format of 3 columns from one query. Is this possible???

Here are the seperate select statements:

SELECT designed_by, Count(art_id)
FROM ART
GROUP BY designed_by

SELECT painted_by, Count(paint_id)
FROM PAINTINGS
GROUP BY painted_by

Thanks for your help in advance.

[Delerna]

Try this

SELECT userid as artist,ART_CNT,PAINT_CNT
FROM ARTISTS 
LEFT JOIN
(   SELECT designed_by, Count(art_id) as ART_CNT
    FROM ART
    GROUP BY designed_by
) b on a.userid = b.designed_by
LEFT JOIN
(    SELECT painted_by, Count(paint_id) as PAINT_CNT
     FROM PAINTINGS
     GROUP BY painted_by
) c on a.userid = c.painted_by
 

LEFT JOIN is allowed in SQL server but I don't think it will work in access
I think it should be either LEFT OUTER JOIN or LEFT INNER JOIN
Can't remember which as I havn't used access in a while

[j420exe]

Thanks for the fast reply. What do the a, b, c reference?

b on a.userid = b.designed_by

When executing it, an error comes back as invalid identifier "b"

[Delerna]

the a b and c are alias's for the table and subqueries
I did that just to make my typing easier in the post

the alias shuld be recognizable by access. Check your query carefully
it seems you may have left the alias off

[Delerna]

No, sorry I left the alias off the table

this bit
SELECT userid as artist,ART_CNT, PAINT_CNT
FROM ARTISTS

should be
SELECT userid as artist,ART_CNT, PAINT_CNT
FROM ARTISTS a