Solved

Question on SQL query

Posted on 2014-09-13
13
273 Views
Last Modified: 2014-09-13
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!
0
Comment
Question by:terpsichore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40321211
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40321221
maybe then the SELECT TOP 1 ...
0
 

Author Comment

by:terpsichore
ID: 40321227
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 16

Accepted Solution

by:
Sheils earned 250 total points
ID: 40321236
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
 

Author Closing Comment

by:terpsichore
ID: 40321243
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
 
LVL 16

Expert Comment

by:Sheils
ID: 40321249
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
 

Author Comment

by:terpsichore
ID: 40321251
thank you
0
 
LVL 16

Expert Comment

by:Sheils
ID: 40321260
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
 

Author Comment

by:terpsichore
ID: 40321263
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
 
LVL 16

Expert Comment

by:Sheils
ID: 40321269
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
 

Author Comment

by:terpsichore
ID: 40321271
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40321275
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
 

Author Comment

by:terpsichore
ID: 40321277
thank you all very much - you are real experts!
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question