Solved

Question on SQL query

Posted on 2014-09-13
13
271 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 48

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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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 48

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Form - Button Format Issue 13 40
CRM 2011 Advanced Find Producing Error 3 34
Create a varying recordset 5 47
Why Won't My Excel Workbook Refresh The Data 2 34
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

740 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