Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access query - Find earliest record (reposting)

Posted on 2013-06-09
2
Medium Priority
?
546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

670 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