Query - need unpainted fire hydrants from subform

[jschlapi]

I have a Master form that uses a subform for data entry. It feeds my main table perfectly, so I am able to create a report showing each fire hydrant and every date of service.

My problem is that I cannot create a query to show any hydrant that has NEVER been painted. My code is:

SELECT Maintenance.[Hydrant No], Hydrants.Location, Maintenance.[Paint OK], Maintenance.[Needs Paint], Maintenance.Touchup, Maintenance.Repainted, Maintenance.[Last Paint Date], Maintenance.Crew


FROM Hydrants INNER JOIN Maintenance ON Hydrants.[Hydrant No] = Maintenance.[Hydrant No]
WHERE (((Maintenance.[Last Paint Date]) Is Null))
ORDER BY Maintenance.[Hydrant No];

My result for this query is every line item that does not have a date will be displayed - if it was painted this year, for instance, I end up with 2 lines for 1 hydrant and the one without a paint date shows as unpainted, however, since it was painted this year, I do not want it showing in the query.

I hope this makes sense - any ideas? Thank you.

[June7]

This requires each record to test for a value in all other records in same table. Can be tricky. Use subquery or DLookup.

Try:

WHERE (((Maintenance.[Last Paint Date]) Is Null) AND NOT HydrantNo IN (SELECT HydrantNo FROM Maintenance WHERE Not [Last Paint Date] Is Null))
ORDER BY Maintenance.[Hydrant No];

That should return hydrants without a paint date only if there are no records for the hydrant with a paint date.

[Dal Jeanis]

If table Hydrants includes a list of all hydrants, regardless of whether they have maintenance records, then do a LEFT JOIN like this -

SELECT 
  TH.[Hydrant No], 
  TH.Location, 
  NZ(TM.[Paint OK],""), 
  NZ(TM.[Needs Paint],""), 
  NZ(TM.Touchup,""), 
  NZ(TM.Repainted,""), 
  NZ(TM.[Last Paint Date],"NEVER"), 
  NZ(TM.Crew,"")
FROM 
  Hydrants AS TH
  LEFT JOIN 
  Maintenance AS TM
  ON TH.[Hydrant No] = TM.[Hydrant No]
WHERE 
  (((TM.[Last Paint Date]) Is Null))
ORDER BY 
  TM.[Hydrant No];

Something just seems odd to me about the design assumptions here.
[Paint Ok] and [Needs Paint] seem like two Boolean variables, and seem mutually exclusive.

Likewise, [Touchup] and [Repainted] seem like past maintenance actions, so they might be dates of that activity, or they might be mutually explucive Boolean variables to describe what happened at the [Last Paint Date].

The problem is that, if the first two are inspection results, then there needs to be an inspection date somewhere, and the only place to put it is in the [Last Paint Date] field.

If all fields those represent things that happened at different times, then [Crew] would just be the last people to touch the record...

On the other hand, if each hydrant can have multiple maintenance records, then only one of the four fields would be filled true, the others false.

I'm thinking that what you have here is an unnormalized form, that probably should look like

tblMaintActions
PK           AutoNumber Key
HydrantNo    FK to Hydrant
MaintDate    Date
MaintType    Text   (Inspection or Painting - could also be a code)
MaintResults Text   (Paint OK or Needs paint for Inspections, Touchup or Repainted for Painting  - could also be a code)
MaintNotes   Text   (a place for notes)

[jschlapi]

This worked perfectly!! Thank you so much for your help.
===============================================

This requires each record to test for a value in all other records in same table. Can be tricky. Use subquery or DLookup.

Try:

WHERE (((Maintenance.[Last Paint Date]) Is Null) AND NOT HydrantNo IN (SELECT HydrantNo FROM Maintenance WHERE Not [Last Paint Date] Is Null))
ORDER BY Maintenance.[Hydrant No];

That should return hydrants without a paint date only if there are no records for the hydrant with a paint date.