Updating values in Table1 based on date condition from Table2

[Oleksandr]

I have two tables, one with tasks and the other with some relative data as below:

Task             Client    Date         Hummer qty
Made something a   X1   01.02.2022
Made something b   X2   05.02.2022
Made something c   X3   05.02.2022
Made something d   X2   07.02.2022

So this one represents daily activity for different clients. I need to fill "Hummer qty" column for every day entered for every Client using second table below where there is Dated history of each Client, at which dates quantity of hummers were increased by each Client.

Client  Date     Hummer qty
X1    15.03.2021    1
X1    23.05.2021    3
X2    08.02.2019    1
X2    06.02.2022    2
X2    06.03.2022    3
X3    16.03.2022    1

as a result, first table should be as below:

Task             Client   Date     Hummer qty
Made something a   X1	01.02.2022    3
Made something b   X2	05.02.2022    1
Made something c   X3	05.02.2022    0
Made something d   X2	07.02.2022    2

in this case tried to use Dlookup with conditions where daily dates >= to history dates + Clients are identical between tables. But it does not work (empty). Simple query returns only first found quantities in history and set it to all the rows of respective client.

[NeoPa]

Hi Oleksandr.

Welcome to Bytes.com :-)

Oleksandr:
I need to fill "Hummer qty" column for every day entered for every Client using second table below where there is Dated history of each Client, at which dates quantity of hummers were increased by each Client.

Are you sure? Is this your idea or was it suggested to you?

That approach goes against the doctrine of normalisation (Database Normalisation and Table Structures) of data and, while there are cases where the practicalities override the requirements of managing data in a logical way, I would advise against this unless the reasons for doing so are very solid & impelling.

Normally, when presented with a requirement to show totals against any group headings, it is done with a query where the grouping is matched between the tables and the quantity values are aggregated (in this case summed together to reach a total). There is no requirement to store these calculated values. Logically they can change over time - thus leaving the updated records in your approach unreliable anyway.

In your example data it isn't clear what your actual grouping is. At one point it looks like just the Client and the Date, yet at another it seems to include the Client, the Date and the Task. As far as explaining the situation generally is concerned, this doesn't matter. I have explained the issues that work whatever is the actual case for you. However, if you continue to work with this without a clear understanding yourself of what fits where, then you will struggle massively.

Best of luck with your project.

[XPS35]

You also asked the same question on another forum (Link removed) . Crossposting without mentioning it is not appreciated. There too you got the answer that you are breaking the rules of good database design by wanting this.