14 Feb 12 15:39
Thanks for taking time to review my stumbling block.
I am attempting to return a "First Pass Yield" query. Let me define this requirement:
1.) For a given month and year chosen in a FPY form (frmFPY), I'd like Access to only return those units that were tested for the first time in the given month (cboMonth) and year (txtYear). Another catch: The unit may have been tested multiple times in the chosen month/year (failures and retesting). I only want to focus on the very first time.
2.) I'd like to return an associated Pass/Fail status for each of these unique tests. This is just another field in the table. If there's info in RoiTitle, it's considered a FAIL, otherwise = PASS. I will then report this and the % pass out via Powerpoint.
I first thought I would create one table that only included the units run on a given month. Here's that code. It seems to work well (notice I had to convert the form info to actual dates with CDate).
SELECT Tbl_ATP_Test_data.SN, Tbl_ATP_Test_data.RoiDate, Tbl_ATP_Test_data.RoiID, Tbl_ATP_Test_data.RoiClass, Tbl_ATP_Test_data.RoiTitle
WHERE (((Year([RoiDate]))=Val([Forms]![frmFPY]![txtYear])) AND ((Month([RoiDate]))=Month(CDate([Forms]![frmFPY]![cboMonth] & "/01/01"))));
Then make another query that showed all older historical data that matches the above query.
SELECT DISTINCTROW Tbl_ATP_Test_data.RoiDate, Tbl_ATP_Test_data.RoiID, Tbl_ATP_Test_data.RoiClass, Tbl_ATP_Test_data.RoiTitle
FROM Tbl_ATP_Test_data INNER JOIN [Table_ATP_Test_Data_Query-FPY] ON Tbl_ATP_Test_data.SN = [Table_ATP_Test_Data_Query-FPY].SN
I kind of got stopped there. Not sure how to finish, or if this is even the right way to go. The hope was I could pull out the serial numbers from the first query that was not in the second one, but not sure how to do that.