Updating query a one table using another table with dates

[Oleksandr]

[COLOR=var(--black-200) !important][COLOR=var(--black-500) !important]0



[/COLOR]
[/COLOR]
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.

[Gicu]

You may want to look at this example Browne from Allen Browne for using subqueries to get the data from another record;
http://allenbrowne.com/subquery-01.html

Cheers,

[Minty]

You shouldn't store that qty as a field value in the first table, you should always calculate it in a query and then display that when you need it.
If you store it you can't be certain that it's accurate unless you update it every time the second table is altered. This is poor practice and design, and causes you extra work.

[Oleksandr]

thank you for these advices!

[Oleksandr]

Tried to make it but again it finds first value under condition (for example 3) and sets it for all the rows even for those where value 4 should be.

UPDATE tasksImport INNER JOIN acCount ON tasksImport.Client = acCount.Client SET tasksImport.acGrow = [acCount]![acGrow]
WHERE (((Exists (SELECT tasksImport.DateStartDate
FROM tasksImport INNER JOIN acCount ON tasksImport.Client = acCount.Client
WHERE (((tasksImport.DateStartDate)>=[acCount]![DateFrom]));
))=True));

taksImport is Table1 from my request above, acCount is Tbale2 and acGrow are hummers values. Maybe you could review it and advise, please... Thank you!