使用Min（Date）更新查询 [英] Update Query Using Min(Date)

问题描述

我有一个表格中的记录列表（TBEvents），其中包含以下字段。


EventDate

NewDate

ClientNumber

DaysTillReview

IsAReview


可以有许多具有相同ClientNumber的记录。

I想要获取在DaysTillReview字段中写入的值并将其写入另一个表（TBClients）上使用的另一个字段（MinDays），但是，仅使用TBEvents中使用最小EventDate的记录，其中ClientNumber与使用的ClientNumber相同在TBEvents中没有NewDate，IsAReview是True。 Client表中的字段包括：


ClientNumber（PK）

MinDays


所以我需要一种将TBEvents.DaysTillReview写入TBClients.MinDays的方法，其中TBEvent.ClientNumber = TBClients.ClientNumber和Min（TBEvent.EventDate）和NewDate是空的并且IsAReview = True。


我假设我需要将所有这些合并到一个追加查询中，但我不确定正确的语法。


谢谢。

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.DaysTillReview into TBClients.MinDays where TBEvent.ClientNumber = TBClients.ClientNumber And Min(TBEvent.EventDate) 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.

推荐答案

因此，您希望通过[TBEvents]处理并更新[TBClients]中的记录每个[TBEvents]记录共享相同的[ClientNumber]，没有[NewDate]值，[IsAReview] = True并将[TBClients]。[MinDays]更新为[TBEvents]的最小值。[DaysTillReview]。是吗？


如果是这样，那么你现在应该知道我们需要在问题中看到你已经尝试过的东西。我们的规则不允许我们为你做这件事，但我们可以帮助（而且我们会）如果你尝试但是卡住了。

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.

如果你觉得有趣的话你给了我所有的答案;）


试图创建一个追加查询，但不知道如何放入Min（[EventDate]）以及如何用[ClientNumber]链接这两个表在SQL中。当我运行下面的SQL时，它永远不会找到任何记录。


一些字段名称有点复杂，无法理解它们的相关内容所以我重写了一些字段，所以它会更容易理解。当可能不需要时，试着让事情变得更容易。


所以我真正想做的是通过[TBSchemeOfWorkEvents]处理并在[TBSchemeOfWork]中为每个共享相同[SchemeOfWorkID]的[TBSchemeOfWorkEvents]记录组更新记录，没有[ActualReviewDate]值，[IsAReview] = True并将[TBSchemeOfWork]。[DaysTillNextReview]更新为[TBSchemeOfWorkEvent]的最小值。[DaysToReview]

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 [TBSchemeOfWorkEvents] and update a record in [TBSchemeOfWork] for every group of [TBSchemeOfWorkEvents] records that share the same [SchemeOfWorkID], have no [ActualReviewDate] value, have [IsAReview] = True and update [TBSchemeOfWork].[DaysTillNextReview] to the minimum value of [TBSchemeOfWorkEvent].[DaysToReview]

展开 | 选择 | Wrap | 行号

根据你的问题，我认为APPEND查询没有任何意义（我认为我的喜欢在＃2的帖子中，相当广泛。）


我会尝试解决你最新帖子的内容，但似乎你的代码中的名字似乎与那些名字在解释中使用。当我需要将两者结合在一起时，这会让生活更加困难。我怀疑在今晚之前我会得到任何帮助。遗憾的是，这种事情是如此容易（并且我敢说明显）要避免（我认为它增加了乐趣，但是以一种奇怪的方式:-D）。


我会在下次有空的时候再看一遍。

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.

这篇关于使用Min（Date）更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！