Solved

Access query - Find earliest record (reposting)

Posted on 2013-06-09
2
490 Views
Last Modified: 2013-06-10
I posted this and thought it was answered, but alas it didn't work properly. (I'm not faulting the respondent, who was very insightful, I probably just didn't askt he question correctly.)

What we need (sample data attached):
1) We have a Projects table that lists projects (the only field here we are concerned with is Project_ID). We then have a Project_Parts table (linked on that field) that gives the parts of a given project (key field Part_ID).
2) We then have a table of milestones on a project. Name of this table = project_parts_revenue.
This links to Project_Parts on Part_ID field.
The other important fields are revenueID [key field], expdate [date for this milestone], complete [boolean], and milestone_ID.
milestone_ID links to a picklist table that explains what TYPE of milestone this is (the linked table is PICK_PartMilestoneType - for example, start date, final delivery, tec.).

3) WHAT WE NEED
We need to find out FOR A GIVEN PROJECT_ID, what is the earliest milestone that is NOT complete.
We need to grab multiple fields from that record, including: the date, and the TEXT of the milestone type (from the picklist table).
We would invoke this query by project_ID - typically we would have a dashboard listing many projects, and for each one, we would need to display the NEXT PROJECT MILESTONE (with these fields).

Any ideas on how to do efficiently?
Thank you, experts!
Database2.zip
0
Comment
Question by:terpsichore
2 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 39234064
Replace [ProjectID] with the project id you want to query
SELECT TOP 1 *
FROM
	(Project_Parts PP INNER JOIN
	Project_Parts_Revenue R ON PP.Part_ID = R.Part_ID) INNER JOIN
	PICK_PartMilestoneType M ON R.Milestone_ID = M.ID
WHERE
	R.Complete = False AND
	PP.Project_ID = [ProjectID]
ORDER BY
	R.ExpDate

Open in new window

The query above returns 2 records based on the sample data provided since they have the same ExpDate. If you only want 1 record returned, add additional columns on the ORDER BY clause
ORDER BY
	R.ExpDate,R.RevenueID

Open in new window

0
 

Author Closing Comment

by:terpsichore
ID: 39234252
worked perfectly.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now