First Pass Yield

[bcooler]

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
FROM Tbl_ATP_Test_data
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
WHERE (((Tbl_ATP_Test_data.RoiDate)<=[Forms]![frmFPY]![txtdate]));

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.

 

[bcooler]

Figured it out.....

I had to make a query with just the serial number and date. Turns out you can only do it this way to get the info you need. Then, I turned on the query grouping feature and grabbed the "Min" choice. This gave me a listing of the earliest that each serial number was tested.

Then I created a new query with the first one above and the original table as sources. I then joined them both at the serial number field and the date field. Not sure if it's called an inner join or outer, but I said to only return the serial numbers from the query source and not the table source.

I then created a user form to enter the month and year of choice and used the textbox/combobbox names as additional criteria in the 2nd query.

Finally, I created a report for this data and made a button to open it as necessary.