How to sum at levels in a query

[Sck]

I have two tables in my database, one contains the fields I need to total by and the other has the amounts to total. Easy enough to begin with but the trick is the key between the tables are not exact.



Example:
SumByTable Key
1.0
1.1
1.1.1
1.1.2
1.2
1.2.1
2.0
2.1
2.2


ActualDataTable Key
1.1.1.1 $1.00
1.1.1.2 $1.00
1.1.1.3 $1.00
1.1.2.1 $2.00
1.1.2.2 $2.00
1.2.1.1 $3.00
2.1 $5.00
2.2 $5.00

I need to be able to pull the data summed at every level so my result will look like this:
1.0 $10.00
1.1 $7.00
1.1.1 $3.00
1.1.2 $4.00
1.2 $3.00
1.2.1 $3.00
2.0 $10.00
2.1 $5.00
2.2 $5.00

I have been trying to use the left function to trim the key in the actual data table but I have not been able to get it to work. If it is at the x.0 level then anything starting with the x needs to be summed.

Any help is greatly appreciated! ps sorry about the spacing on the data above, it kept moving the $ left when I posted.

[weekend00]

1 change x.0 to x
Qry1: select iif(right(key,2)=".0",left(key,len(key)-2),key) from sumByTable

2 sum by group
select a.kkey,sum([data]) as summ from qry1 as a inner join actualData as on a.kkey=left(b.key,len(a.kkey) group by a.kkey

[Sck]

got it!! thanks