Help with query please

[Jon Lewis]

My tblActions table has the following fields (amongst others)

TaskID (Primary Key)
CompanyID (Foreign Key)
OwnerID (ForeignKey)
DateDue
DateDone

I would like a SAVED query to return for each CompanyID, the Earliest
DateDue for uncompleted (DateDate Is Null) tasks together with the OwnerID
for the task.

Many thanks

 

[Douglas J. Steele]

Create a query (call it qryEarliest) that returns the earliest date due for
each company:

SELECT CompanyID, Min(DateDue) As Earliest
FROM tblActions
WHERE DateDone IS NULL
GROUP BY CompanyID

Join that subquery to your main table:

SELECT tblActions.TaskID,
tblActions.CompanyID,
tblActions.OwnerID,
tblActions.DateDue
FROM tblActions INNER JOIN qryEarliest
ON tblActions.CompanyID = qryEarliest.CompanyID
AND tblActions.DateDue = qryEarliest.Earliest

Note that it is possible to do this in a single query (assuming Access 2000
or newer)

SELECT tblActions.TaskID,
tblActions.CompanyID,
tblActions.OwnerID,
tblActions.DateDue
FROM tblActions INNER JOIN
(
SELECT CompanyID, Min(DateDue) As Earliest
FROM tblActions
WHERE DateDone IS NULL
GROUP BY CompanyID
) AS qryEarliest
ON tblActions.CompanyID = qryEarliest.CompanyID
AND tblActions.DateDue = qryEarliest.Earliest

 

[Jon Lewis]

Many thanks Douglas

Jon

Create a query (call it qryEarliest) that returns the earliest date due
for each company:

SELECT CompanyID, Min(DateDue) As Earliest
FROM tblActions
WHERE DateDone IS NULL
GROUP BY CompanyID

Join that subquery to your main table:

SELECT tblActions.TaskID,
tblActions.CompanyID,
tblActions.OwnerID,
tblActions.DateDue
FROM tblActions INNER JOIN qryEarliest
ON tblActions.CompanyID = qryEarliest.CompanyID
AND tblActions.DateDue = qryEarliest.Earliest

Note that it is possible to do this in a single query (assuming Access
2000 or newer)

SELECT tblActions.TaskID,
tblActions.CompanyID,
tblActions.OwnerID,
tblActions.DateDue
FROM tblActions INNER JOIN
(
SELECT CompanyID, Min(DateDue) As Earliest
FROM tblActions
WHERE DateDone IS NULL
GROUP BY CompanyID
) AS qryEarliest
ON tblActions.CompanyID = qryEarliest.CompanyID
AND tblActions.DateDue = qryEarliest.Earliest

 

[Daniel]

One thing that has caused me a lot of grief that you may want to keep
in mind is that joining it into a single query (that is, using a
subquery) prevents the recordset from being updated.

If you keep it as two queries, with one referencing the other, then it
should still be updateable.

Daniel

 

[Jon Lewis]

Yes Daniel! After I implemented Doug's SQL (single query) I realised the
recordset is no longer updateable. You have hopefully just saved me from a
major form redesign. Many thanks