Help - Another hard query

[Timothy V]

Hi everyone, i hope someone can help me construct this query. To make it
easy, I will provide a simplified example of my problem and relationships
will exist between fields that have the same names. Three tables exist:
Programs(programID) - pk is programID
Jobs(jobID,dueDate) - pk is jobID
ProgrammedJobs(programID,jobID) - pk is programID and jobID

Ok, so what I want to do is create a query that will list the programIDs
without duplicates... that is, similar to SELECT DISTINCT and GROUP BY. So,
not this:
programID
2000
1000
2000
1000
1000
1000

But this:
programID
1000
2000

However, there is a twist... Since one programID will have many jobIDs, I
want to sort the query of programIDs that have jobIDs with earlier dates. To
help you understand, here is some data.

Program (1000) has Jobs (100,1/21/2007), (101,1/31/2007), (102,1/31/2007)
associated with it.
Program (2000) has Jobs (103,1/19/2007), (104,1/31/2007), (105,1/28/2007)
associated with it.

Basically, the query I want will have the data (in order):
programID
2000
1000

Where 2000 has a job that is more due before any that are within 1000.

Can anyone help me?

Thank you very very very much in advance,

Tim.

PS. here is my current query that can sort the data, but not group the
program numbers:
SELECT Programs.programID
FROM Programs INNER JOIN (Jobs INNER JOIN ProgrammedJobs ON (Jobs.jobAsn =
ProgrammedJobs.jobAsn) AND (Jobs.jobID = ProgrammedJobs.jobID)) ON
Programs.programID = ProgrammedJobs.programID
ORDER BY Jobs.dueDate;

 

[John Spencer]

How about using Group By on ProgramID and the earliest (minimum) date for the jobs?

Something like

SELECT Programs.programID, Min(Jobs.DueDate) as Earliest
FROM Programs INNER JOIN
(Jobs INNER JOIN ProgrammedJobs ON
(Jobs.jobAsn = ProgrammedJobs.jobAsn)
AND (Jobs.jobID = ProgrammedJobs.jobID)) ON
Programs.programID = ProgrammedJobs.programID
GROUP BY Programs.ProgramID
ORDER BY Min(Jobs.dueDate)

 

[Timothy V]

Thank you, that worked like a charm! Another question on the same data if
you don't mind answering it.

I want to make a query that will list all Jobs that are not assigned to a
Program. That is, list all Jobs that do not have an entry in the
ProgrammedJobs table.

Any ideas?

Thank you so much for your previous answer.

 

[Smartin]

The "Find Unmatched Query Wizard" should do the trick.