How to query monthly updates field, past three months, where entry is "no new update"

[Missomissou]

I have a query that returns results for long text monthly updates over the past three months. I wanted to find all instances where a project had not had an update during that time. I set the query up like this:

As far as I can tell the query is returning every instance of any UpdateText containing the word "update" occurring during the specified period of time. That's interesting too, but not the question I'm trying to ask. I need to know when a project has gone "idle". Then I will try to write a query to find out when the last update was entered.

 

[ebs17]

To identify those projects for which there have been no updates in the past 3 months, you would have to do something like this:

SELECT
   U.*
FROM
   TblSecMonthlyUpdates AS U
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            TblSecMonthlyUpdates AS X
         WHERE
            X.ALWRIProjectID = U.ALWRIProjectID
               AND
            X.UpdateDate >= DateSerial(Year(Date()), Month(Date()) - 3, 1)
               AND
            X.UpdateDate < DateSerial(Year(Date()), Month(Date()), 1)
               AND
            X.UpdateText LIKE "*update*"
      )

This query replaces the TblSecMonthlyUpdates table in your query.

find out when the last update was entered

It's easier to determine this directly.

SELECT
   ALWRIProjectID
FROM
   TblSecMonthlyUpdates
WHERE
   UpdateText LIKE "*update*"
GROUP BY
   ALWRIProjectID
HAVING
   MAX(UpdateDate) < DateSerial(Year(Date()), Month(Date()) - 3, 1)

LIKE "*update*"

The fact that a pattern search is necessary, which excludes the use of an index, indicates a missing normalization step. Optimization is possible and necessary.

 

[Missomissou]

To identify those projects for which there have been no updates in the past 3 months, you would have to do something like this:

SELECT
   U.*
FROM
   TblSecMonthlyUpdates AS U
WHERE
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            TblSecMonthlyUpdates AS X
         WHERE
            X.ALWRIProjectID = U.ALWRIProjectID
               AND
            X.UpdateDate >= DateSerial(Year(Date()), Month(Date()) - 3, 1)
               AND
            X.UpdateDate < DateSerial(Year(Date()), Month(Date()), 1)
               AND
            X.UpdateText LIKE "*update*"
      )

This query replaces the TblSecMonthlyUpdates table in your query.


It's easier to determine this directly.

SELECT
   ALWRIProjectID
FROM
   TblSecMonthlyUpdates
WHERE
   UpdateText LIKE "*update*"
GROUP BY
   ALWRIProjectID
HAVING
   MAX(UpdateDate) < DateSerial(Year(Date()), Month(Date()) - 3, 1)

LIKE "*update*"

The fact that a pattern search is necessary, which excludes the use of an index, indicates a missing normalization step. Optimization is possible and necessary.

I'm running the analyzer now--it split the first table analyzed into 3 new tables, so I will spend some time now trying to understand the relationships before coming back to the query question, but I will come back to it! Thank you so much for this thoughtful response. O.

 

[mike60smart]

Why not just replace the Criteria Like "* Update*" with Like "No New updates*"

 

[Missomissou]

SELECT U.* FROM TblSecMonthlyUpdates AS U WHERE NOT EXISTS ( SELECT NULL FROM TblSecMonthlyUpdates AS X WHERE X.ALWRIProjectID = U.ALWRIProjectID AND X.UpdateDate >= DateSerial(Year(Date()), Month(Date()) - 3, 1) AND X.UpdateDate < DateSerial(Year(Date()), Month(Date()), 1) AND X.UpdateText LIKE "*update*" )

@ebs17 Hey, I swapped out the old Projects table for a new one, and deleted the MonthlyUpdates table from the query and opened the SQL view, and then added your code in after the code that was already there. When I try to run it, I get an error message indicating there are characters after the SQL statement. I don't see any extra characters, but maybe I'm missing something? I also don't see anywhere where the old table name was used in the code you wrote? Any idea what's going on here?

The full code now looks like:

SELECT TblAALWRIProjects_New.ALWRIProjectTitle, TblCLstALWRIStaff.LastName, TblCLstALWRIStaff.FirstName
FROM TblCLstALWRIStaff INNER JOIN TblAALWRIProjects_New ON TblCLstALWRIStaff.ResearcherID = TblAALWRIProjects_New.ResearcherID;



SELECT U.*
FROM TblSecMonthlyUpdates AS U
WHERE NOT EXISTS (
SELECT
NULL
FROM
TblSecMonthlyUpdates AS X
WHERE
X.ALWRIProjectID = U.ALWRIProjectID
AND
X.UpdateDate >= DateSerial(Year(Date()), Month(Date()) - 3, 1)
AND
X.UpdateDate < DateSerial(Year(Date()), Month(Date()), 1)
AND
X.UpdateText LIKE "*update*" )

 

[ebs17]

In Jet-SQL, only one SQL statement can be included in the query.

there are characters after the SQL statement

Anything that comes after the semicolon is too much.

 

[Missomissou]

@ebs17 so, then would I write a new query with the query above (before your code) as a field within the new query?

 

[Missomissou]

@mike60smart, the user can enter any text they like into the update field, but they usually include a phrase like "no further updates", "no new update", or sometimes just "no update".

Why not just replace the Criteria Like "* Update*" with Like "No New updates*"

 

[ebs17]

This query replaces the TblSecMonthlyUpdates table in your query.

Save the new query shown according to #2, for example under the name qryXX (a name in your naming regime).

Then this query is linked to the other two tables, just like the original table was before.

SELECT
   TblAALWRIProjects_New.ALWRIProjectTitle,
   TblCLstALWRIStaff.LastName,
   TblCLstALWRIStaff.FirstName,
   QryXX.UpdateDate
FROM
   (TblCLstALWRIStaff
      INNER JOIN TblAALWRIProjects_New
      ON TblCLstALWRIStaff.ResearcherID = TblAALWRIProjects_New.ResearcherID
   )
   INNER JOIN QryXX
   ON TblAALWRIProjects_New.ALWRIProjectID = QryXX.ALWRIProjectID

Or you can include this query directly as a subquery. But not everyone understands that.

SELECT
   TblAALWRIProjects_New.ALWRIProjectTitle,
   TblCLstALWRIStaff.LastName,
   TblCLstALWRIStaff.FirstName,
   QryXX.UpdateDate
FROM
   (TblCLstALWRIStaff
      INNER JOIN TblAALWRIProjects_New
      ON TblCLstALWRIStaff.ResearcherID = TblAALWRIProjects_New.ResearcherID
   )
   INNER JOIN
      (
         SELECT
            U.*
         FROM
            TblSecMonthlyUpdates AS U
         WHERE
            NOT EXISTS
               (
                  SELECT
                     NULL
                  FROM
                     TblSecMonthlyUpdates AS X
                  WHERE
                     X.ALWRIProjectID = U.ALWRIProjectID
                        AND
                     X.UpdateDate >= DateSerial(Year(Date()), Month(Date()) - 3, 1)
                        AND
                     X.UpdateDate < DateSerial(Year(Date()), Month(Date()), 1)
                        AND
                     X.UpdateText LIKE "*update*"
               )
            ) AS QryXX
            ON TblAALWRIProjects_New.ALWRIProjectID = QryXX.ALWRIProjectID

 

[ebs17]

the user can enter any text they like into the update field

If users can freely and creatively write whatever they want, they will do so. This will create problems.
If they write "wonderful and totally new" your check for the word update won't pick it up. Effect?

It is better to standardize the entries on a regular basis, e.g. using ComboBox. Then it would only contain the word update or a key to the word (Long), and you could test for equality instead of patterns. That would then be easier, safer and faster.