Dates to reflect Fiscal month

[Misha]

Hi:

I have a bunch of queries, most of which need to be assigned to a
Fiscal Month/year. I have a separate table with all the dates as
individual records with the relevant Fiscal month attached.
unfortunately, when i join the tables together by DATE and try to query
for Fiscal Month, I get either nothing or very few records returned.

I also tried creating another table that had the Fiscal Month; Start
Date; and End Date to do a query i found on another message board:
IF(actiondate() between Start_Date and End_Date, Fiscal_Month,
"error"). But this didn't work either.

Please help.

Thank you,

Misha

 

[Tom Ellison]

Dear Misha:

It seems likely that when you "join the tables together by DATE" there are
dates in the query that do not exist in the "separate table". This causes
the missing data. Using an INNER JOIN returns rows only where the match
specified in the ON clause succeeds.

Now, the approach of having a table of FiscalMonth, StartDate, and EndDate
is probably what is needed. Please provide details of this table and the
query you produced to do the job. Perhaps I can fix it up for you.

Tom Ellison

 

[Misha]

Dear Tom,

Thanks so much for your offer - I actually fixed it myself (not sure
how).

In the end, I didn't need to JOIN the tables. What I needed to do was
include FISCAL_MONTH in the query; and in my field that required the
date limits, I just put in BETWEEN START_DATE AND END_DATE, which
seemed to do the trick if I didn't GROUP BY anything.

Thanks again!

Misha