Need help with a query

[EDIx12]

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

 

[JoeAtWork]

Downloaded your database, this SQL statement gave the results you want (note I use one of your existing queries):

SELECT Sum(WorkDetail.Cost) AS Costs, Parts.ProductCat
FROM Parts INNER JOIN WorkDetail ON Parts.PartNo = WorkDetail.PartNo
WHERE (((WorkDetail.WorkOrderNo) In (SELECT WorkOrderNo FROM [Work Orders > $100])))
GROUP BY Parts.ProductCat;

 

[EDIx12]

Excellent, and thanks. I used the query as you have it except I just put the subquery as the code from the other query.

SELECT Parts.ProductCat, Sum(WorkDetail.Cost) AS SumOfCost
FROM Parts INNER JOIN WorkDetail ON Parts.PartNo = WorkDetail.PartNo
WHERE (((WorkDetail.WorkOrderNo) In (SELECT WorkDetail.WorkOrderNo FROM WorkDetail GROUP BY WorkDetail.WorkOrderNo HAVING (((Sum([WorkDetail.Cost]))>100)))))
GROUP BY Parts.ProductCat;