I am new to Access and am trying to create a query that can be used to calculate PPM defective. My database contains one table that contains the defective part information (part number, quantity defective, and date that defect was recorded) and another table where ship quantities are entered (part number, quantity shipped, and date shipped). I really want to be able to enter a start date and end date and have a query pull ship quantities by part number for this date range from table one, defective quantities by part number for this date range from table two and then perform a calculation of defective quantity / ship quantity * 1000000.

What I have so far seems to work but for single month queries only. If I query from 01/01/09 to 03/31/09 for instance, the query returns 3 sets of each part number (one total for each month queried). I can not seem to get a total of part number x shipped for the 3 month time frame along with a total of part number x defective, etc.



Any suggestions on where to look or suggestions on how to work this out is greatly appreciated!