Updating Fiscal month field value based on transaction date

[MIchel Khennafi]

Good afternoon.

In a table I have a "fiscal month" table containing 3 fields:
- Fiscal month (text, 2)
- Date from (date/time)
- Date To (date/time)

In a second table I have transactions and each record has a "transaction
date". There is also a field named "fiscal month" that I would like to
update based on the "transaction date" field value. The logic is to analyze
each transaction date and based on the value lookup into the "fiscal month"
table and update the fiscal month field with the corresponding fiscal month.
Anyone can help explaining how this can be achieved? How to determine a
field value based on the date to analyze and compare to a date bracket?

Thanks so much

 

[Guest]

Use the query below, changing the table and field names to get fiscal month
for your primary key. [Change Requests].z is the equivaliant to your
"transaction date" field.

SELECT [Change Requests].Primary_Key, [Change Requests].y, [Change
Requests].z, FiscalMonth.FiscalMonth, FiscalMonth.StartDate,
FiscalMonth.EndDate
FROM [Change Requests], FiscalMonth
WHERE ((([Change Requests].z) Between [StartDate] And [EndDate]));