Find the occurance after a given date!

[neilmcmor]

Hope someone can help. I would like to query my database, (on a date field), to show the first record after a given date. The 2 date fields are in seperate tables and the query result is reliant on the date in one table being the first date after the date in the other table. Thanks

 

[PHV]

And what have you tried so far and where in your SQL code are you stuck ?

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

 

[neilmcmor]

This just returns no records

SELECT Class.Spin, Details.Enter_Date, First(Classes.Date) AS FirstOfDate, Last(Classes.Date) AS LastOfDate
FROM Details INNER JOIN (Classes INNER JOIN Class ON Classes.Class_Code = Class.Class_Code) ON Details.Spin = Class.Spin
GROUP BY Class.Spin, Details.Enter_Date
HAVING (((First(Classes.Date))>[Enter_ Date]));

 

[PHV]

What about this ?
SELECT Class.Spin, Details.Enter_Date, Min(Classes.Date) AS FirstOfDate, Max(Classes.Date) AS LastOfDate
FROM Details INNER JOIN (Classes INNER JOIN Class ON Classes.Class_Code = Class.Class_Code) ON Details.Spin = Class.Spin
WHERE Classes.Date > Details.Enter_Date
GROUP BY Class.Spin, Details.Enter_Date

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

 

[neilmcmor]

No I am afraid it never worked. Here is the full query sql I am using as there is quite a lot going on. Still returning no records.

SELECT Count(Prisoner_Class.Spin) AS CountOfSpin, Prisoner_Details.Enter_Prison_Date, Min(Classes.Date) AS FirstOfDate, Max(Classes.Date) AS LastOfDate, DateDiff("d",[FirstOfDate],[LastOfDate]) AS AttendancePeriodDays, [AttendancePeriodDays]/30.4 AS MonthsAttending, [AttendancePeriodDays]/7 AS WeekAttending, [CountOfSpin]/[WeekAttending] AS AvgPerWeek
FROM Prisoner_Details INNER JOIN (Classes INNER JOIN Prisoner_Class ON Classes.Class_Code = Prisoner_Class.Class_Code) ON Prisoner_Details.Spin = Prisoner_Class.Spin
WHERE (((Classes.Date)>[Prisoner_Details].[Enter_Prison_Date]))
GROUP BY Prisoner_Details.Enter_Prison_Date;

 

[PHV]

So, check the tables to be sure your criteria (JOIN and WHERE) are relevant and that records exist in the 3 tables meeting the conditions.

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