Inner Join Or Sub Query?

I hope someone out there can help me, I'm not an SQL experianced person, I only know enough to make problems. Anyway I believe my problem below is probably simple to someone that knows SQL very good. I have a link to a sample Access database if anyone is interested in helping me solve this.

The Sample Database (http://shadesoffire.net/files/db1.mdb)

Have a problem and can't quite seem to get it solved. I have simplified the whole thing to make it easier to understand.

I want a report that shows me the Product Category and Sum from work orders that were > $100.

I'm not quite sure how to do this, I've tried several methods of Joining and Sub queries but nothing really works. All I get in my results is product categories > 100. See I would expect the product category to be on the report even it it were $55 if it came from a work order containing total costs > 100.

I have manually supplied the results that I want, any help would be greatly appreciated. My real problem deals with larger results and numbers but these are the basics.

Linked is an Access database with one of my queries that show all work orders > 100. I would expect all those product categories in my summary report.

Redesign of the database is not an option.

These work orders are all > 100, the total on all reports should be $511


WorkOrderNo Total Cost
00001 $120.00
00003 $268.00
00005 $123.00



These product categories are associated with the above work orders and this is the result I am looking for.


Prod Cat Costs
12 $176
19 $89
22 $233
91 $13


The above results would come from this:


WorkOrderNo LineNo PartNo Cost Cat
00001 1 ABC $55.00 22
00001 2 XYZ $65.00 12
00003 1 ABC $89.00 22
00003 2 XYZ $77.00 12
00003 3 123 $89.00 19
00003 4 456 $13.00 91
00005 1 ABC $89.00 22
00005 2 XYZ $34.00 12