Question on SQL query

Dear experts -
We have a table of milestones on a project - the table includes a date/time field. Each project can have many records.
We would like to pull a query that ultimately shows the next deadline (defined as the next date + sort AND sort by record ID ascending, in case of ties), and also include the record ID.
I am stumbling on this - any help would be appreciated.
Thank you!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Perhaps you could tell us the names of the table and the names of the fields? Here's a guess in the meantime

select
*
from milestones
where date_time_field > date()
order by date_time_field, record_id
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
maybe then the SELECT TOP 1 ...
0
terpsichoreAuthor Commented:
if I am sorting by date, what is the difference please between top, min, and first? I think this will guide me to the solution.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SheilsCommented:
First you need to decide what exactly is the next deadline. Is it the next date in relation to the current date (PortletPaul code address this) or is it the next uncompleted milestone.

If it is the later then you may complete a milestone before the due date that should push the deadline to the due date of the following milestone. Alternatively you may fall behind and the deadline will be in the past. Using date criteria in your SQL will not work in both scenarios.

The work around is to add a completed field (Boolean) in tblMilestones. The your SQL will be something like:-

Select * from tblMilestone Where fldCompleted=-1 Orderby fldDueDate,fldMilestoneId
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
This gets me where i need to go - i am basically looking for the next uncompleted milestone (complete <> -1). I need to pick the EARLIEST record for each project (linkage through a few tables to derive the project_ID) - so I will find the MIN or FIRST, I guess, as an aggregate function. THANK YOU.
0
SheilsCommented:
If you are sorting by date then first,min,top date will not do much if you relate them to the date field.

The definitions are as follows:-

Min: returns the smallest value of the selected column. That's the earliest date in the table.

First: returns the first value of the selected column. That will be the first date in the table or if you have a where condition the first date in the table that satisfy the where condition.

Top: Allows you to specify the number of records from the first that you want to display. eg

Select Top 5 * from tblMilestone where fldDueDate>date()

will give the next five milestones due after today.
0
terpsichoreAuthor Commented:
thank you
0
SheilsCommented:
If you have multiple projects and you want the next uncompleted milestone in each, you may need to use a union query that will look something like:

Select * from tblMilestone Where fldCompleted<>-1 AND fldProjectId=1 Orderby fldDueDate,fldMilestoneId

Union

Select * from tblMilestone Where fldCompleted<>-1 AND fldProjectId=2 Orderby fldDueDate,fldMilestoneId

Union

Select * from tblMilestone Where fldCompleted<>-1 AND fldProjectId=3 Orderby fldDueDate,fldMilestoneId

If this works, you may consider using vba to generate the sql programmatically so that you don't have to rewrite the query when you add a forth project.
0
terpsichoreAuthor Commented:
thanks - what I am doing (your answers helped guide me) - is to do an aggregate query with the minimum deadline for each project, where complete <> -1. Then I am taking this query and using it to drive another query, where based on that date I look up the ID of the original record and pull data from there, if that makes sense.
0
SheilsCommented:
As the saying goes, there's more than one way to skin a cat (my apology to all cat lovers out there). If this method is working for you then it all good.
0
terpsichoreAuthor Commented:
the basic problem I have been struggling with - for a long time - is that I want to be sorting a record set on multiple fields and then grabbing the record ID of the one that appears first. What I have come to learn is that the First keyword has nothing to do with this, and that the sorting is ignored in aggregate functions. Thus my original question, which really requires a workaround...
0
PortletPaulfreelancerCommented:
this makes absolute sense:

do an aggregate query with the minimum deadline for each project, where complete <> -1
Then I am taking this query and using it to drive another query,
0
terpsichoreAuthor Commented:
thank you all very much - you are real experts!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.