Solved

Access query - Find earliest record (reposting)

Posted on 2013-06-09
2
513 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 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