Updating Fiscal month field value based on transaction date

  • Thread starter Thread starter MIchel Khennafi
  • Start date Start date
M

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
 
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]));
 
Back
Top