Finding the lastest date for an Inspecetion

[Luis Palacios]

I am trying to query the last/latest investigation for a facility using a date field. Each facility will have different types of investigations, I only want the last investigation performed. For the example below I only want to see ABC Day Care 11/5/07.

FACILITY
ABC Day Care

INVESTIGATIONS
1/1/06
4/18/07
11/5/07

[Rabbit]

Depends on where you're trying to use it. Either a Max() aggregate query or a DMax() function call.

[Luis Palacios]

I don't understand what you are asking. Will you explain further? Here is the SQL that I am working on.

SELECT CCL_EMPLOYEE.NBR_REGION, CCL_EMPLOYEE.NBR_UNIT AS Unit, ([CCL_EMPLOYEE.NM_LAST] & ", " & [CCL_EMPLOYEE.NM_FIRST]) AS Employee, CCL_FACILITY.NBR_FCLTY AS [Facility #], CCL_FACILITY.NM_FCLTY AS [Name of Facility], CCL_FACILITY.CD_FCLTY_TYPE AS [Op Type], CCL_INSPECTION.DT_BEGIN_INSPCTN AS [Date Began], CCL_INSPECTION.DT_END_INSPCTN AS [Date End], CCL_INSPECTION.CD_INSPCTN_TYPE AS [Ins Type]
FROM ((((CCL_FACILITY INNER JOIN CCL_INSPECTION ON CCL_FACILITY.ID_FCLTY = CCL_INSPECTION.ID_FCLTY) INNER JOIN CCL_CODE_TABLE_ROW ON CCL_FACILITY.CD_FCLTY_TYPE = CCL_CODE_TABLE_ROW.CD_CODE) INNER JOIN CCL_CODE_TABLE_ROW AS CCL_CODE_TABLE_ROW_1 ON CCL_INSPECTION.CD_INSPCTN_TYPE = CCL_CODE_TABLE_ROW_1.CD_CODE) INNER JOIN CCL_FACILITY_ADDRESS ON CCL_FACILITY.ID_FCLTY = CCL_FACILITY_ADDRESS.ID_FCLTY) INNER JOIN CCL_EMPLOYEE ON CCL_INSPECTION.ID_EMPLYEE = CCL_EMPLOYEE.ID_EMPLYEE
WHERE (((CCL_FACILITY.NBR_FCLTY)=510923 Or (CCL_FACILITY.NBR_FCLTY)=556373 Or (CCL_FACILITY.NBR_FCLTY)=860960 Or (CCL_FACILITY.NBR_FCLTY)=876183 Or (CCL_FACILITY.NBR_FCLTY)=558679 Or (CCL_FACILITY.NBR_FCLTY)=310307 Or (CCL_FACILITY.NBR_FCLTY)=401626 Or (CCL_FACILITY.NBR_FCLTY)=414127 Or (CCL_FACILITY.NBR_FCLTY)=500833 Or (CCL_FACILITY.NBR_FCLTY)=865154 Or (CCL_FACILITY.NBR_FCLTY)=853481 Or (CCL_FACILITY.NBR_FCLTY)=537436 Or (CCL_FACILITY.NBR_FCLTY)=540032 Or (CCL_FACILITY.NBR_FCLTY)=531810 Or (CCL_FACILITY.NBR_FCLTY)=502793 Or (CCL_FACILITY.NBR_FCLTY)=517369 Or (CCL_FACILITY.NBR_FCLTY)=501967 Or (CCL_FACILITY.NBR_FCLTY)=509294 Or (CCL_FACILITY.NBR_FCLTY)=867435 Or (CCL_FACILITY.NBR_FCLTY)=816647 Or (CCL_FACILITY.NBR_FCLTY)=558935 Or (CCL_FACILITY.NBR_FCLTY)=547134 Or (CCL_FACILITY.NBR_FCLTY)=543233 Or (CCL_FACILITY.NBR_FCLTY)=532226 Or (CCL_FACILITY.NBR_FCLTY)=516844 Or (CCL_FACILITY.NBR_FCLTY)=523808 Or (CCL_FACILITY.NBR_FCLTY)=516853 Or (CCL_FACILITY.NBR_FCLTY)=523211 Or (CCL_FACILITY.NBR_FCLTY)=868838 Or (CCL_FACILITY.NBR_FCLTY)=505838 Or (CCL_FACILITY.NBR_FCLTY)=559653 Or (CCL_FACILITY.NBR_FCLTY)=540624 Or (CCL_FACILITY.NBR_FCLTY)=875179 Or (CCL_FACILITY.NBR_FCLTY)=545156 Or (CCL_FACILITY.NBR_FCLTY)=548913 Or (CCL_FACILITY.NBR_FCLTY)=813895 Or (CCL_FACILITY.NBR_FCLTY)=553482 Or (CCL_FACILITY.NBR_FCLTY)=552186 Or (CCL_FACILITY.NBR_FCLTY)=547770 Or (CCL_FACILITY.NBR_FCLTY)=554517 Or (CCL_FACILITY.NBR_FCLTY)=550565 Or (CCL_FACILITY.NBR_FCLTY)=525720 Or (CCL_FACILITY.NBR_FCLTY)=547557 Or (CCL_FACILITY.NBR_FCLTY)=815756 Or (CCL_FACILITY.NBR_FCLTY)=811682 Or (CCL_FACILITY.NBR_FCLTY)=551505 Or (CCL_FACILITY.NBR_FCLTY)=819473 Or (CCL_FACILITY.NBR_FCLTY)=838151 Or (CCL_FACILITY.NBR_FCLTY)=557335 Or (CCL_FACILITY.NBR_FCLTY)=554964 Or (CCL_FACILITY.NBR_FCLTY)=866218 Or (CCL_FACILITY.NBR_FCLTY)=834332 Or (CCL_FACILITY.NBR_FCLTY)=869778) AND ((CCL_FACILITY.CD_PRGRM_TYPE)="DC" Or (CCL_FACILITY.CD_PRGRM_TYPE) Is Null) AND ((CCL_CODE_TABLE_ROW.ID_CODE_TABLE)=72) AND ((CCL_CODE_TABLE_ROW_1.ID_CODE_TABLE)=45) AND ((CCL_FACILITY_ADDRESS.CD_ADDR_TYPE)="L"))
ORDER BY CCL_EMPLOYEE.NBR_REGION, ([CCL_EMPLOYEE.NM_LAST] & ", " & [CCL_EMPLOYEE.NM_FIRST]);
 

Thanks for all your help.

[Rabbit]

Please use code tags. Well, now I know where you're trying to get the Max. So you're going to have to use an aggregate query. View > Totals. That will give you the option of setting up an aggregate query.