Update Query Using Min(Date)

[reginaldmerritt]

I have a list of records in a table (TBEvents) which hold the following fields.

EventDate
NewDate
ClientNumber
DaysTillReview
IsAReview

There can be many records with same ClientNumber.
I want to take the value written in the DaysTillReview field and write this into another field (MinDays) used on another table(TBClients ), however, only using the record from TBEvents using the minimum EventDate and where the ClientNumber is the same as the ClientNumber used in TBEvents and there is no NewDate and IsAReview is True. The fields in the Client table are amongst others:

ClientNumber (PK)
MinDays

So i need a way of writing TBEvents.DaysTi llReview into TBClients.MinDa ys where TBEvent.ClientN umber = TBClients.Clien tNumber And Min(TBEvent.Eve ntDate) And NewDate Is Null And IsAReview = True.

I assume that i need to incorporate all of this into a append query but i'm not sure on the correct syntax.

thanks.

[NeoPa]

So, you want to process through [TBEvents] and update a record in [TBClients] for every group of [TBEvents] records that share the same [ClientNumber], have no [NewDate] value, have [IsAReview] = True and update [TBClients].[MinDays] to the minimum value of [TBEvents].[DaysTillReview]. Is that right?

If so, then you should probably know by now that we need to see what you've already tried for yourself in the question. Our rules don't allow us simply to do it for you, but we can help (and we will) if you try but get stuck.

[reginaldmerritt]

Where would be the fun in that if you gave me all the answers ;)

Tried to create an append query but not sure how to put in the Min([EventDate]) and how to link the two tables with [ClientNumber] in the SQL. When I run the SQL below it never finds any records.

Some of the field names are a bit complicated to understand what they are related to so I reworded some of the fields so it would be simpler to understand. Trying to make things easier when it's probably not needed.

So what I actually want to do is to process through [TBSchemeOfWorkE vents] and update a record in [TBSchemeOfWork] for every group of [TBSchemeOfWorkE vents] records that share the same [SchemeOfWorkID], have no [ActualReviewDat e] value, have [IsAReview] = True and update [TBSchemeOfWork].[DaysTillNextRev iew] to the minimum value of [TBSchemeOfWorkE vent].[DaysToReview]

INSERT INTO TBSchemeOfWork ( DaysTillNextReview )
SELECT TBSchemeOfWorkEvent.DaysToReview
FROM TBSchemeOfWorkEvent
WHERE (((TBSchemeOfWorkEvent.SchemeOfWorkEventID)=[SchemeOfWorkID]) AND ((TBSchemeOfWorkEvent.ActualReviewDate) Is Null));

Is an append query the best way to go about this?

[NeoPa]

I see no way that an APPEND query would make sense in light of your question (I thought my hint in post #2 was pretty broad on that).

I will try to work through what your latest post is saying, but it already seems that the names in your code are different from those names used in the explanation. That makes life extra-difficult when I need to match the two together. I doubt I'll get anything helpful out before this evening. A shame as that sort of thing is so easy (and dare I say obvious) to avoid (I suppose it adds to the fun though, in a weird sort of way :-D).

I will look again later when next I have some time available though.

[NeoPa]

Firstly, an UPDATE query must be updatable (See Reasons for a Query to be Non-Updatable). If the following doesn't work then you may be forced to create the main data first into a separate table with a GROUP BY query and then use that pre-aggregated data to update your other table.

UPDATE [TBSchemeOfWork] AS [tSW]
       INNER JOIN
       (
    SELECT   [SchemeOfWorkEventID]
           , [DaysToReview]
           , [EventDate]
    FROM     [TBSchemeOfWorkEvent] AS tSWEo
    WHERE    ([EventDate] In (
        SELECT   Min([EventDate])
        FROM     [TBSchemeOfWorkEvent] AS tSWEi
        WHERE    (tSWEi.SchemeOfWorkEventID = tSWEo.SchemeOfWorkEventID)
          AND    ([IsAReview])
          AND    ([ActualReviewDate] Is Null)
        GROUP BY [SchemeOfWorkEventID]))
       ) AS [sSWE]
    ON tSW.SchemeOfWorkID = sSWE.SchemeOfWorkEventID
SET    tSW.DaysTillNextReview = sSWE.DaysToReview
WHERE  (tSW.IsAReview)
  AND  (sSWE.ActualReviewDate Is Null)

No guarantees as I said, and I have no rig to test it with myself. Let us know if you need to take the other option.

[reginaldmerritt]

No Pa, firstly sorry for not getting back to you sooner.

(I suppose it adds to the fun though, in a weird sort of way :-D).

Really. I suppose, I don't know how you and a lot of other forum members do it, I find reading some posts really hard to get my head round. I find I need a lot of background information to know what the fields relate to, just so I can understand it. That's why I try to simplify things, but in the long run that's not always the best option.

The SQL is a bit over my head but that makes it all the more fun, in a weird sort of way ;).

Where you have

GROUP BY [SchemeOfWorkEventID]))
       ) AS [sSWE]
    ON tSW.SchemeOfWorkID = sSWE.SchemeOfWorkEventID
SET    tSW.DaysTillNextReview = sSWE.DaysToReview

the field SchemeOfWorkEve ntID is named as sSWE but then sSWE is used as a table as in sSWE.SchemeOfWo rkEventID??? Doesn't that just evaluate to SchemeOfWorkEve ntID.SchemeOfWo rkEventID?

[NeoPa]

Reginald Merritt:
the field SchemeOfWorkEve ntID is named as sSWE but then sSWE is used as a table as in sSWE.SchemeOfWo rkEventID??? Doesn't that just evaluate to SchemeOfWorkEve ntID.SchemeOfWo rkEventID?

Good thinking. But no.

If you look at the parentheses you'll see that the AS (or ALIAS) sSWE refers to the whole sub-query, and not the last field referenced therein.

IE. The reference sSWE.SchemeOfWo rkEventID refers to the field called [SchemeOfWorkEve ntID] provided by the subquery (which is actually a record source, rather than a table per se.) now named [sSWE].

PS. I always appreciate it when people go to the trouble of attempting to make a question easier to understand. Unfortunately this doesn't always stop the frustration of seeing the same mistakes repeated endlessly (not always by the same members of course). To me it's obvious (even without all the experience of dealing with such questions) but then not everyone is as geeky and naturally fussy as I am. Ultimately though, the intention is the important thing, so good on you for trying to make it easier anyway :-)

[reginaldmerritt]

Sorry I wasn't quite looking at the bracketing correctly. I see what you mean now. It's the WHERE statement that has the ALIAS of sSWE, quite cool that it can be grouped together like that and used as a record source.

When i try to run the query it asks for the value of the fields 'tSW.IsAReview' and 'sSwe.ActualRev iewDate'. Can't see why it's not picking these values up from the record sources.

[NeoPa]

You missed a parenthesis Reginald. The 'AS' on line #15 follows (and pertains to) a closing parenthesis which is paired with the opening parenthesis found on line #3. [sSWE] therefore is an ALIAS for all that is included within (which is the subquery).

tSW.IsAReview and sSWE.ActualRevi ewDate are problems with my code. I suspect that [IsAReview] is not found as a field in [tSW] (or [TBSchemeOfWork]), but only found in [TBSchemeOfWorkE vent] instead. sSWE.ActualRevi ewDate is not returned by the subquery either, so cannot be tested in the WHERE clause.

Actually, the SQL had already been designed so that lines #18 and #19 are no longer required (See lines 12# and #13), but I forgot to remove them before I posted the proposed solution.

You should be left with something like :

UPDATE [TBSchemeOfWork] AS [tSW]
       INNER JOIN
       (
    SELECT   [SchemeOfWorkEventID]
           , [DaysToReview]
           , [EventDate]
    FROM     [TBSchemeOfWorkEvent] AS tSWEo
    WHERE    ([EventDate] In (
        SELECT   Min([EventDate])
        FROM     [TBSchemeOfWorkEvent] AS tSWEi
        WHERE    (tSWEi.SchemeOfWorkEventID = tSWEo.SchemeOfWorkEventID)
          AND    ([IsAReview])
          AND    ([ActualReviewDate] Is Null)
        GROUP BY [SchemeOfWorkEventID]))
       ) AS [sSWE]
    ON tSW.SchemeOfWorkID = sSWE.SchemeOfWorkEventID
SET    tSW.DaysTillNextReview = sSWE.DaysToReview

[reginaldmerritt]

Thanks Ne Pa, query is running now. Not updating any records though, I'll have to look into it a bit more tomorrow.

[NeoPa]

Sounds better. The first thing to check is whether or not there are records that you believe ought to have been updated. It's so easy to forget that when you've struggled to get to this stage.

[reginaldmerritt]

There is a update query I use to fill in all records for DaysToReview in TBSchemeOfWorkE vent. I used the following query to display the data I'm trying to gather, this shows the data I'm after so it's defiantly there.

SELECT TBSchemeOfWorkEvent.SchemeOfWorkEventID, TBSchemeOfWorkEvent.EventDate, TBSchemeOfWorkEvent.DaysToReview, TBSchemeOfWorkEvent.IsAReview, TBSchemeOfWorkEvent.ActualReviewDate, TBSchemeOfWorkEvent.SchemeOfWorkID
FROM TBSchemeOfWorkEvent
WHERE (((TBSchemeOfWorkEvent.IsAReview)=True) AND ((TBSchemeOfWorkEvent.ActualReviewDate) Is Null));

Can't work out why the Update SQL doesn't work.
I've created a small test database with the two tables, some example data and Update SQL.

[reginaldmerritt]

Worked it out. Was looking at it but not seeing it. The link between TBSchemeOfWork and TBSchemeOfWorkE vent is the PK in TBSchemeOfWork which is SchemeOfWorkID.

The Update SQL was code using the PK from TBSchemeOfWorkE vent which is SchemeOfWorkEve ntID. I switched this round to SchemeOfWorkID and now it works.

UPDATE TBSchemeOfWork AS tSW INNER JOIN (
SELECT [SchemeOfWorkID], [DaysToReview], [EventDate] FROM TBSchemeOfWorkEvent AS tSWEo 
WHERE ([EventDate] In (              
SELECT Min([EventDate])             
FROM   [TBSchemeOfWorkEvent]  AS tSWEi             
WHERE  (tSWEi.SchemeOfWorkEventID = tSWEo.SchemeOfWorkEventID)             
AND    ([IsAReview] = True)             
AND    ([ActualReviewDate] Is Null)  
GROUP BY [SchemeOfWorkEventID])))  AS sSWE 
ON tSW.SchemeOfWorkID=sSWE.SchemeOfWorkID SET tSW.DaysTillNextReview = sSWE.DaysToReview;

[reginaldmerritt]

Thanks for all your help NeoPa. You might want to change the title of the post to 'Update query using min date' rather than Append, not quite sure how I got those two terms mixed up ;)

[reginaldmerritt]

The Update SQL sees to work but only captures the Maximum DaysToReview, I tried swapping

SELECT Min([EventDate])

for

SELECT Max([EventDate])

but the SQL captured the same values. There was no difference between MIN and MAX ??