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!