GET VALUE for Table 1 from Table 2 WHERE Table 1 Record's Date FALLS BETWEEN Table 2 Record's START& END DATE

[Rosemary]

Hi
I am working on a little app to record and evaluate diet information. At the end of each day the user enters their consumption info (which is captured with a Diet Diary App) . This is a variables like calcium, protien, vegetables and importantly, CALORIES for each day.

I have a main table called DATA which contains a column called TARGET CALORIES. This is used for a bunch of calculations that compare the person’s ACTUAL CALORIES CONSUMED to their TARGET CALORIES.

I want the user to be able to change the TARGET CALORIES every now and then and have that change apply to all subsequent days’ records until they change it again.
I don’t want them to have to enter TARGET CALORIES every day
And I don’t want to have to do this manually in the Excel Spreadsheet (which is what I have been doing).

I have made a table called CAL_TARGETS to store the calorie targets as entered by the user.
This table has a One to Many relationship to the DATA Table.
There is only ever one CALORIE TARGET for any given day (row) in the DATA table but any given record in the CAL_TARGETS table will apply to many records in the DATA Table.
I need the previous target to remain on previous days and the new target to be applied to each subsequent day as the data is entered.

CAL_TARGETS TABLE
has 4 columns ROWNUMBER, STARTDATE, ENDDATE, CALORIES. Except for RowNumber (obviously) this table is updated via a UX called “EDIT CALORIE TARGET”.

DATA TABLE
has a lot of columns including DATE (one date per row) and CALORIE TARGET.
DATA Table has consecutive rows for every day since 23 March 2021.
I want DATA [CALORIE TARGET] to contain the value from CAL_TARGETS [CALORIES] where DATA [DATE] falls within the [STARTDATE] and [ENDDATE] of a record on CAL_TARGETS.

CAL_TARGETS TABLE
START DATE END DATE CALORIES
23/3/2021…31/05/2021…1673
01/06/2021…30/06/2021…1790
01/07/2021…31/0702021…1800

DATA TABLE
DATE…CALORIE TARGET
15/04/2021…1673 ( and the same for all subsequent rows until next record on CAL_TARGETS)
23/06/2021…1790 ( and the same for all subsequent rows until next record on CAL_TARGETS)
10/07/2021…1800

I have read a number of Support Pages, Question Threads and watched a few AppSheet YouTubes but I haven’t come across anything that addresses this particular challenge. I have experimented with combinations of AND() and SELECT() and linking the two tables.

Thanks
Rosemary