How do this tricky query?

I have 2 tables, a CASE table and an INVESTIGATOR table.
This is a one-to many relationship. Each Investigator has an Assign Date and
a Completion Date.
There may be zero, one or many investigators per case but only one at a time.

Completion dates could be missing:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed null
Assigned dates could be the same e.g.
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/11/09
Or, different e.g.
Investigator1 Assigned 12/10/09 Completed 12/11/09
Investigator2 Assigned 12/11/09 Completed 12/11/09
This is theoretically possible, in which case either record could be
returned:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/10/09

I am trying to come up with a LatestInvestigator query that I can join to
the Case table.

The rule is I need the latest completion date (null being later than any
date) or if all completion dates are the same, the latest assign date or if
all assign/completion dates are the same, any one record.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Is there a primary key in the Case table? If so, you could use a query that
looks like the following generic query

SELECT Investigator.*
, Case.*
FROM Investigator INNER JOIN Case
ON Investigator.PrimaryKey = Case.ForeignInvestigatorKey
WHERE Case.PrimaryKey IN
(SELECT TOP 1 C1.PrimaryKey
FROM Case as C1
WHERE C1.ForeignInvestigatorKey = Case.ForeignInvestigatorKey
ORDER BY Nz(C1.Completed,#2999-12-31#) DESC
, C1.Assigned DESC
, C1.PrimaryKey)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
I have 2 tables, a CASE table and an INVESTIGATOR table.
This is a one-to many relationship. Each Investigator has an Assign Date and
a Completion Date.
There may be zero, one or many investigators per case but only one at a time.

Completion dates could be missing:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed null
Assigned dates could be the same e.g.
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/11/09
Or, different e.g.
Investigator1 Assigned 12/10/09 Completed 12/11/09
Investigator2 Assigned 12/11/09 Completed 12/11/09
This is theoretically possible, in which case either record could be
returned:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/10/09

I am trying to come up with a LatestInvestigator query that I can join to
the Case table.

The rule is I need the latest completion date (null being later than any
date) or if all completion dates are the same, the latest assign date or if
all assign/completion dates are the same, any one record.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Never mind. I fixed it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Dorian" wrote:

I have 2 tables, a CASE table and an INVESTIGATOR table.
This is a one-to many relationship. Each Investigator has an Assign Date and
a Completion Date.
There may be zero, one or many investigators per case but only one at a time.

Completion dates could be missing:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed null
Assigned dates could be the same e.g.
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/11/09
Or, different e.g.
Investigator1 Assigned 12/10/09 Completed 12/11/09
Investigator2 Assigned 12/11/09 Completed 12/11/09
This is theoretically possible, in which case either record could be
returned:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/10/09

I am trying to come up with a LatestInvestigator query that I can join to
the Case table.

The rule is I need the latest completion date (null being later than any
date) or if all completion dates are the same, the latest assign date or if
all assign/completion dates are the same, any one record.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

If you'll post your solution, other folks may be able to benefit from your
(painful) experience... g

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Dorian" wrote in message
...
Never mind. I fixed it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".


"Dorian" wrote:

I have 2 tables, a CASE table and an INVESTIGATOR table.
This is a one-to many relationship. Each Investigator has an Assign Date
and
a Completion Date.
There may be zero, one or many investigators per case but only one at a
time.

Completion dates could be missing:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed null
Assigned dates could be the same e.g.
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/11/09
Or, different e.g.
Investigator1 Assigned 12/10/09 Completed 12/11/09
Investigator2 Assigned 12/11/09 Completed 12/11/09
This is theoretically possible, in which case either record could be
returned:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/10/09

I am trying to come up with a LatestInvestigator query that I can join to
the Case table.

The rule is I need the latest completion date (null being later than any
date) or if all completion dates are the same, the latest assign date or
if
all assign/completion dates are the same, any one record.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".

I did something similar to this. Instead of using the assign and completion
dates, I simply used the latest primary key in the Investigator table. It's
an autonumber key and I understand not guaranteed to increment reliably but
it seems to work ok so far and I have other more important things to worry
about.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John Spencer" wrote:

Is there a primary key in the Case table? If so, you could use a query that
looks like the following generic query

SELECT Investigator.*
, Case.*
FROM Investigator INNER JOIN Case
ON Investigator.PrimaryKey = Case.ForeignInvestigatorKey
WHERE Case.PrimaryKey IN
(SELECT TOP 1 C1.PrimaryKey
FROM Case as C1
WHERE C1.ForeignInvestigatorKey = Case.ForeignInvestigatorKey
ORDER BY Nz(C1.Completed,#2999-12-31#) DESC
, C1.Assigned DESC
, C1.PrimaryKey)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
I have 2 tables, a CASE table and an INVESTIGATOR table.
This is a one-to many relationship. Each Investigator has an Assign Date and
a Completion Date.
There may be zero, one or many investigators per case but only one at a time.

Completion dates could be missing:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed null
Assigned dates could be the same e.g.
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/11/09
Or, different e.g.
Investigator1 Assigned 12/10/09 Completed 12/11/09
Investigator2 Assigned 12/11/09 Completed 12/11/09
This is theoretically possible, in which case either record could be
returned:
Investigator1 Assigned 12/10/09 Completed 12/10/09
Investigator2 Assigned 12/10/09 Completed 12/10/09

I am trying to come up with a LatestInvestigator query that I can join to
the Case table.

The rule is I need the latest completion date (null being later than any
date) or if all completion dates are the same, the latest assign date or if
all assign/completion dates are the same, any one record.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
.