I constructed a query which checks a list of locations against two other tables in order to find out if the location has a record of a specific type of maintenance, and if so, when that maintenance was most recently performed. The output table contains the location ID and the most recent maintenance date. I manually compared the dates in the output table and the database to confirm that the query runs correctly. Everything seems fine, except that the first cell in the output table, which should contain the first location ID, is blank. I would like to know if this is a result of my make table query (see the SQL statement below), or if there could be a setting I need to change in the query property sheet. This seems like an exceedingly simple problem, so I feel kind of silly asking the question, but I would appreciate the help.
SELECT IIf([ERP_WCI].[MPFLAG]="Y" And [ERP_WMI].[MTYPE]="MPELV",[July_13_GWE].[LOC ID]) AS LOCID, Max(ERP_WMI.LOGDATE) AS MaxOfLOGDATE INTO [Most Recent WMI MPELV log date for wells with MPFLAG=Y]
FROM (July_13_GWE INNER JOIN ERP_WMI ON July_13_GWE.[LOC ID] = ERP_WMI.LOCID) INNER JOIN ERP_WCI ON July_13_GWE.[LOC ID] = ERP_WCI.LOCID
GROUP BY IIf([ERP_WCI].[MPFLAG]="Y" And [ERP_WMI].[MTYPE]="MPELV",[July_13_GWE].[LOC ID]);
Output table:
Location ID Max of Log Date 10/18/2011 U5-1084 3/5/2010 U5-1089 3/5/2010 U5-147 3/5/2010