list equipment by date without record

  • Thread starter Thread starter Jason F
  • Start date Start date
J

Jason F

I have a Report that has two table in the query builder, "tblEquipment" and
"tblClean". Both tables have an "ID" field that is an autonumber. The linked
field is "IntEquipNo". Each peice of equipment has multipal cleaning
records. I need a report that will list every "IntEquipNo" that does not
have a cleaning record after "sdCleanDateCriteria". The clean date criteria
is enter in a dialog box when the form is opened. This is a short Date
format. The "tblClean" has a short date formated field "scDate". How do i
do this?

Thanks in advance for any help
 
Try this substituting your form name for YourFormName --
SELECT tblEquipment.*, tblClean.*
FROM tblEquipment LEFT JOIN tblClean ON tblEquipment.IntEquipNo =
tblClean.IntEquipNo
WHERE tblClean.scDate > [Forms]![YourFormName]![sdCleanDateCriteria];
 
make a query using the necessary fields from both tables....be sure it works ok

then in the appropriate field from tblClean, put in the parameter: Is Null

that will force the query to return records for which there is no data in
this field....
(assuming the field is indeed blank) i.e. no cleans

once the query works ok - then make the report sourced on this query
 
Two query solution.

First query - find all records in tblClean after the cutoff date

Now use that query in a second query to find the Unmatched records (use
the Unmatched query wizard) or use the first query as a subquery in the
where clause.

Subquery solution
SELECT *
FROM tblEquipment
WHERE intEquipNo NOT IN
(SELECT IntEquipNo
FROM tblClean
WHERE intEquipNo is not null
AND scDate > Forms!NameOfForm!sdCleanDateCriteria)

OR Unmatched query solution

SELECT tblEquipment.*
FROM tblEquipment LEFT JOIN
(SELECT IntEquipNo
FROM tblClean
WHERE scDate > Forms!NameOfForm!sdCleanDateCriteria) as Temp
ON tblEquipment.IntEquipNo = Temp.IntEquipNo
WHERE Temp.IntEquipNo is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top