Finding Date of 3rd to last landing

  • Thread starter Thread starter Ed Finley
  • Start date Start date
E

Ed Finley

I'm sure there's an easier way to do this. I have a table of pilot names,
flight dates, and number of landings that day. I'm trying to get a query or
VB routine to give me the date of the third to last landing.
Any help will be appreciated.
Ed
 
There is probably be another query to do this more efficiently, but for the
third to last landing overall, you might try a query whose SQL looks
something like this:

SELECT
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3

Likewise, for the third to last landing for each pilot, you might try a
query whose SQL looks something like this:

SELECT
[Your Table].[Pilot Name],
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Pilot Name] = [Your Table].[Pilot Name]
AND
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3
GROUP BY
[Your Table].[Pilot Name]
 
SELECT Min(T.LandingDate)
FROM (SELECT TOP 3 LandingDates
FROM yourTable
WHERE PilotID = ???) as T


--
HTH

Dale Fye


I'm sure there's an easier way to do this. I have a table of pilot
names,
flight dates, and number of landings that day. I'm trying to get a
query or
VB routine to give me the date of the third to last landing.
Any help will be appreciated.
Ed
 
Sorry, forgot the Order By clause.

SELECT Min(T.LandingDate)
FROM (SELECT TOP 3 LandingDates
FROM yourTable
WHERE PilotID = ???
ORDER BY LandingDates DESC) as T

--
HTH

Dale Fye


SELECT Min(T.LandingDate)
FROM (SELECT TOP 3 LandingDates
FROM yourTable
WHERE PilotID = ???) as T


--
HTH

Dale Fye


I'm sure there's an easier way to do this. I have a table of pilot
names,
flight dates, and number of landings that day. I'm trying to get a
query or
VB routine to give me the date of the third to last landing.
Any help will be appreciated.
Ed
 
Brian,
Thanks for the help. I've tried these but the WHERE clause keeps causing
"Enter Parameter Value" boxes with [My Table].[Pilot Name] and [My
Table].[Flight Date] prompts. I've checked the spelling a few times and
that doesn't seem to be the problem.
Ed

Brian Camire said:
There is probably be another query to do this more efficiently, but for the
third to last landing overall, you might try a query whose SQL looks
something like this:

SELECT
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3

Likewise, for the third to last landing for each pilot, you might try a
query whose SQL looks something like this:

SELECT
[Your Table].[Pilot Name],
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Pilot Name] = [Your Table].[Pilot Name]
AND
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3
GROUP BY
[Your Table].[Pilot Name]

Ed Finley said:
I'm sure there's an easier way to do this. I have a table of pilot names,
flight dates, and number of landings that day. I'm trying to get a
Click to expand...
query
or
VB routine to give me the date of the third to last landing.
Any help will be appreciated.
Ed
Click to expand...
Click to expand...
 
It sounds like you've picked up on this already, but in case you haven't, I
assumed that your table was named "Your Table", and had fields named "Pilot
Name", "Flight Date", and "Landings". If your table or fields have
different names, you'll need to change every instance in the SQL of the name
I used to your corresponding name. If this doesn't work, I might be able to
help if you post your SQL.

Ed Finley said:
Brian,
Thanks for the help. I've tried these but the WHERE clause keeps causing
"Enter Parameter Value" boxes with [My Table].[Pilot Name] and [My
Table].[Flight Date] prompts. I've checked the spelling a few times and
that doesn't seem to be the problem.
Ed

Brian Camire said:
There is probably be another query to do this more efficiently, but for the
third to last landing overall, you might try a query whose SQL looks
something like this:

SELECT
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3

Likewise, for the third to last landing for each pilot, you might try a
query whose SQL looks something like this:

SELECT
[Your Table].[Pilot Name],
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Pilot Name] = [Your Table].[Pilot Name]
AND
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3
GROUP BY
[Your Table].[Pilot Name]

Ed Finley said:
I'm sure there's an easier way to do this. I have a table of pilot names,
flight dates, and number of landings that day. I'm trying to get a
Click to expand...
query
or
VB routine to give me the date of the third to last landing.
Any help will be appreciated.
Ed
Click to expand...
Click to expand...
Click to expand...
 
Brian,
As I pasted the SQL to post I noticed a space between the "[" and my table
name. Your SQL works great. Thanks 1,000,000.
Ed

Brian Camire said:
It sounds like you've picked up on this already, but in case you haven't, I
assumed that your table was named "Your Table", and had fields named "Pilot
Name", "Flight Date", and "Landings". If your table or fields have
different names, you'll need to change every instance in the SQL of the name
I used to your corresponding name. If this doesn't work, I might be able to
help if you post your SQL.

Ed Finley said:
Brian,
Thanks for the help. I've tried these but the WHERE clause keeps causing
"Enter Parameter Value" boxes with [My Table].[Pilot Name] and [My
Table].[Flight Date] prompts. I've checked the spelling a few times and
that doesn't seem to be the problem.
Ed

Brian Camire said:
There is probably be another query to do this more efficiently, but
Click to expand...
for
the
third to last landing overall, you might try a query whose SQL looks
something like this:

SELECT
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3

Likewise, for the third to last landing for each pilot, you might try a
query whose SQL looks something like this:

SELECT
[Your Table].[Pilot Name],
Max([Your Table].[Flight Date]) AS [Flight Date of Third to Last Landing]
FROM
[Your Table]
WHERE
(SELECT
Sum([Self].[Landings])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Pilot Name] = [Your Table].[Pilot Name]
AND
[Self].[Flight Date] >= [Your Table].[Flight Date]) >= 3
GROUP BY
[Your Table].[Pilot Name]

I'm sure there's an easier way to do this. I have a table of pilot names,
flight dates, and number of landings that day. I'm trying to get a query
or
VB routine to give me the date of the third to last landing.
Any help will be appreciated.
Ed
Click to expand...
Click to expand...
Click to expand...
 
Back
Top