Solved

Access query - Find earliest record (reposting)

Posted on 2013-06-09
2
476 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
Comment Utility
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
Comment Utility
worked perfectly.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

9 Experts available now in Live!

Get 1:1 Help Now