Selecting records on date 1

[tamus121]

I have two tables, one holding records of locations the other various types of visits on various dates to these locations. Some locations have no visits yet and therefore no type of visit. Each location can have one or more visits on different dates and one or more type of visit for each date. e.g.

Location Type Date
1 B 02/03/08
3 A 07/05/08
3 B 07/05/08
2 B 03/03/09
4
1 C 06/06/09

The user will enter a type of visit and I want to list one record for each location (with its Type and Date) in a query. In priorty order :-
The lastest visit date matching the Type or
The latest date if there is no Type match or
If no Type just the location

I just can't get something that works so any help would be welcome
tamus

 

[maxhugen]

Create a grouped subquery based on visit_type table, where you return the `location_id`, plus the Max() (group function) of the `visit_date`, where `visit_type` = [whatever].

In the main query, left join `location` table to the above query.

Max Hugen
Australia

 

[tamus121]

Thanks Max

I have got my query to work. I couldn't manage the subquery route but used two queries. Pity I would have liked to have managed it using one query.
tamus

 

[PHV]

I would have liked to have managed it using one query
Why not posting your actual SQL codes ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tamus121]

ok I can. I have been working on a second one today and gave up on getting the subquery to work - I just couldn't get it. I have it also working but it would be useful to see how it is done

InspectionsTestQuery

SELECT Inspections.ApairyNo, Max(Inspections.[Inspection Date]) AS [MaxOfInspection Date]
FROM Inspections
WHERE (((Inspections.InspectionType)=[Forms]![InspectionDueReport]![CmbInspType]))
GROUP BY Inspections.ApairyNo;

InspectionGridRefTraceQuery

SELECT Inspections.InspectionType, Inspections.[Inspection Date], Inspections.DiseaseSurvey, From.ApiaryIdNo AS FromApiaryID, From.GridRefNo AS FromGridRef, To.ApiaryIdNo AS ToApiaryID, To.GridRefNo AS ToGridRef, GetGridDistance([from].[GridRefNo],[to].[GridRefNo]) AS Distance, IIf(([InspectionsTestQuery].[MaxofInspection Date])>([Inspections].[SurveyDate]) Or (([InspectionsTestQuery].[MaxofInspection Date])=Null),True,False) AS Expr1
FROM tblApiaryAddress AS [To] LEFT JOIN InspectionsTestQuery ON To.ApiaryIdNo = InspectionsTestQuery.ApairyNo, (Inspections INNER JOIN InspectionsTestQuery AS InspectionsTestQuery_1 ON (Inspections.[Inspection Date] = InspectionsTestQuery_1.[MaxOfInspection Date]) AND (Inspections.ApairyNo = InspectionsTestQuery_1.ApairyNo)) INNER JOIN tblApiaryAddress AS [From] ON InspectionsTestQuery_1.ApairyNo = From.ApiaryIdNo
WHERE (((Inspections.InspectionType)=[Forms]![InspectionDueReport]![CmbInspType]) AND ((Inspections.[Inspection Date])>=[Forms]![InspectionDueReport]![BeginningDate] And (Inspections.[Inspection Date])<=[Forms]![InspectionDueReport]![EndingDate]) AND ((Inspections.DiseaseSurvey)=True) AND ((From.GridRefNo) Is Not Null) AND ((To.ApiaryIdNo)<>[From].[ApiaryIdNo]) AND ((To.GridRefNo) Is Not Null) AND ((GetGridDistance([from].[GridRefNo],[to].[GridRefNo]))<4.827) AND ((IIf(([InspectionsTestQuery].[MaxofInspection Date])>([Inspections].[SurveyDate]) Or (([InspectionsTestQuery].[MaxofInspection Date])=Null),True,False))=False));

Just looking at this maybe it is the best solution to have two queries as I have built up the main query quite a bit.
tamus