Complex query formula needed

[Guest]

I need to create a query based on two tables.

The one table includes a 'Visited Date' field. It is based on these dates
(of which there will be an increasing number as I want to link the table to
an excel spreadsheet, for others to update) that I need to extract premiums
from the other table, based on the 'it_date' field. I need to show premiums
for each month for the full 3 months after the visit date and for the same
period 12 months before the visit

For example, if a visit was done 11 Feb 2007, I would need to show the
relevant premiums for March 07 - May 07 (inclusive) and March 06 - May 06
(inclusive).

A colleague had done a similar thing previously, whereby as the dates would
vary, and thus the monitoring months would differ, he established the various
months as 'periods' by using the following formula:

Period: (Year([Run off / Canc Date])-Year([it_date]))*12+(Month([Run off /
Canc Data])-Month([it_date])))

(where 'Run off / Canc Date' was referring to another field in a different
table - mine would be the 'visited date' field).

I've tried to recreate this, but it doesn't seem to work.

Any ideas?

 

[John Spencer]

Since we don't now how the premiums are dated this is only a speculative
answer.

To get the date ranges you could use the following functions

1st of the following month
DateSerial(Year([Visited Date]),Month([Visited Date]) +1, 1)

Last day of the 4th month
DateSerial(Year([Visited Date]),Month([Visited Date]) +4, 0)

1st of following month of prior year
DateSerial(Year([Visited Date])-1,Month([Visited Date]) +1, 1)

Last day of 4th month Prior Year
DateSerial(Year([Visited Date])-1,Month([Visited Date]) +4, 0)

Hopefully with those calculations you can identiry the Premium records you
need.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..