complex Query (working out totals)

Hi,

I would like help with a quite complex SQL statement.  It involves 2 tables.

The first table (table1) look like this:

table1

identifier      Req      Parent
ID-001      5      FM
ID-002      4      FM
ID-003      12      FM
ID-004      21      PL
ID-005      40      FM
ID-006      1      PL
ID-007      10      PL
ID-008      8      FM

The second table (table2) looks like this

identifier      QTY
ID-001      2
ID-001      4
ID-001      1
ID-002      30
ID-004      40
ID-002      1
ID-007      10
ID-008      8

I’m trying to produce a recordset that could populate a list control with the data that would result from following these steps:

1.      Select table1.identifiers where table1.Parent = FM.
2.      For each row in table2 search table.identifer for a matching record, then add up the table2.QTY values for resulting records found
3.      Zero should added where no values can be found

Given the example above it should produce the following results:

identifier      Req      qty
ID-001      5      7
ID-002      4      31
ID-003      12      0
ID-005      40      0
ID-008      8      8

Could this be done?