?
Solved

Question on SQL query

Posted on 2014-09-13
13
Medium Priority
?
274 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 1000 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 16

Accepted Solution

by:
Sheils earned 1000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

719 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