How to add another field to this query.

Question

Hi, So this query will select all of the most recent inpsections from a table that have a valve marked as "INOPERABLE". There are two tables involved....a table of valves (WAT__CONTROLVALVE) and a table of inspections (INSPECTIONS__VALVE_INSPECTION). When I run the query I get the results I expect, but I am trying to figure out how to add some fields from the WAT_CONTROLVALVE table into the final query results. For example, I need the field names LIFECYCLESTATUS in the final output. Can anyone help? Thanks.

 select all_wr_table.*
 from works.INSPECTIONS_VALVE_INSPECTION as all_wr_table
 inner join
 (
     select max(most_recent_Table.INSP_DATE) as MaxOfINSP_DATE, most_recent_Table.FACILITYID
     from works.INSPECTIONS_VALVE_INSPECTION as most_recent_Table inner join works.WAT_CONTROLVALVE on most_recent_Table.FACILITYID = works.WAT_CONTROLVALVE.FACILITYID
     group by most_recent_Table.FACILITYID
 ) as most_recent_Table
 on 
 (all_wr_Table.FACILITYID=most_recent_Table.FACILITYID) and (all_wr_Table.INSP_DATE = most_recent_Table.MaxOfINSP_DATE)
 where(
 (
 ((all_wr_table.DATEWORK is null) and (all_wr_table.VALVE_INOPERABLE = 'True'))
 )
 )

Answer 1

OK, I eventually figured this out, with the help of MS Access query builder GUI.

SELECT all_wr_table.*, works.WAT_CONTROLVALVE.LIFECYCLESTATUS
FROM 
(
	works.INSPECTIONS_VALVE_INSPECTION AS all_wr_table 
	INNER JOIN 
	(
		select max(most_recent_Table.INSP_DATE) as MaxOfINSP_DATE, most_recent_Table.FACILITYID
		from works.INSPECTIONS_VALVE_INSPECTION as most_recent_Table inner join works.WAT_CONTROLVALVE on most_recent_Table.FACILITYID = works.WAT_CONTROLVALVE.FACILITYID
		group by most_recent_Table.FACILITYID
	)  AS most_recent_Table 
	ON 
	(all_wr_table.INSP_DATE = most_recent_Table.MaxOfINSP_DATE) AND (all_wr_table.FACILITYID = most_recent_Table.FACILITYID)
) 
INNER JOIN 
	works.WAT_CONTROLVALVE ON all_wr_table.FACILITYID = works.WAT_CONTROLVALVE.FACILITYID
	WHERE 
	(
		((all_wr_table.DATEWORK) Is Null) AND ((all_wr_table.VALVE_INOPERABLE)='True')
	);