Solved

Help with MS ACCESS Query

Posted on 2012-04-05
2
263 Views
Last Modified: 2012-04-06
It seems to me that i can't figure out simple thing.
I have two tables

table 1 - tblMilestones ( PKMilestoneID-number, ProjectID, MilestoneDate,MilestoneTypeID)
table2 - tblTypesOfMilestones(PKMilestoneTypeID,MilestoneName)

they have one to many relationships based on milestonetypeid.

I need to show The oldest Milestone per project.
But query should output ProjectId, MilestoneName and MilestoneDate

thanks for help.
db-question1.accdb
0
Comment
Question by:maximyshka
[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 1

Expert Comment

by:Michel_Nialon
ID: 37813250
Hello
please try something like this :

select  
   tbl1.ProjectID,
   tbl1.MilestoneDate,
   tbl2.MilestoneName
from
   tblMilestones tbl1,
   tblTypesOfMilestones tbl2
where
   tbl2.PKMilestoneTypeID = tbl1.MilestoneTypeID
   and tbl1.MilestoneDate = (select min(MilestoneDate ) from tblMilestones )

I have nothing to test it here, but it should work
michel
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37813258
try this


SELECT A.Project_ID, A.MilestoneName,A.Milestone_date
From
(SELECT T1.Project_Id, T2.MilestoneName, T1.Milestone_Date
FROM tblTypesOfMilestones as T2 INNER JOIN tblMilestone as T1 ON T2.Milestonetypeid = T1.Milestonetypeid
) As A
Inner Join
(SELECT T.Project_Id, Min(T.Milestone_Date) AS MinOfMilestone_Date
FROM tblMilestone T
GROUP BY T.Project_Id) as B
On A.Milestone_date=B.MinOfMilestone_Date and A.Project_ID=B.Project_ID
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

752 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