• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Access query - Find earliest record (reposting)

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
terpsichore
Asked:
terpsichore
1 Solution
 
ThomasianCommented:
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
 
terpsichoreAuthor Commented:
worked perfectly.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now