Help.
I am a novice at Access. I need to create a formula that will look for an
occurence of a value for a 3 month time frame.

Example: I will have each month in a field, each year in a field, a number
value for occurences in a field. This will be put into a data entry form.

I will then need to run a report that will show the # of occurrences for a
selected 3 month period.

Ultimately I want users to be alerted when an occurrence is approaching 3
within a 3 month period.

How do I do this?

>>Example: I will have each month in a field, each year in a field, a number
value for occurences in a field.
You should use a DateTime DataType field to store the date instead of
separate fields for each.
Is the 'value for occurences' a field to indicate how many occurences there
were or data value at the occurence.

I think you need the following table structure --
Occurence --
OccurenceID - Autonumber - Primary key
OccurDate - DateTime
Value - Number - long integer (or maybe single based of data being stored -
1, 2, 5, etc. OR 2.1, 5.7, 3.97, etc.)

For the last 3 months use this query --
SELECT Count([OccurenceID] AS Occurences
FROM Occurence
WHERE OccurDate Between Date() - Day(Date()) And DateAdd("m", -3, Date() -
Day(Date()))+1;


--
KARL DEWEY
Build a little - Test a little


"Christine" wrote:

> Help.
> I am a novice at Access. I need to create a formula that will look for an
> occurence of a value for a 3 month time frame.
>
> Example: I will have each month in a field, each year in a field, a number
> value for occurences in a field. This will be put into a data entry form.
>
> I will then need to run a report that will show the # of occurrences for a
> selected 3 month period.
>
> Ultimately I want users to be alerted when an occurrence is approaching 3
> within a 3 month period.
>
> How do I do this?

Karl: Thank you, but I think I'm not savvy enough for that.

I think I have a better explanation now of what I'm trying to do.

It's absence database -- employees are allowed so many occurences in a 3
month, 9 month, and 12 month in a rolling 12 month timeframe. What I am
trying to identify is those employees getting close to 3, 9, & 12 months.
My absence dates are stored as mm/dd/yy. Here's my other problem if they
have for example 3 in 3month time frame and don't have any in the next 3
months there bank is set back to 0. Write now its a manual review process
and if I could just get the rolling 12 function to work, it would cut down
alot of time.

Thanks very much.

"KARL DEWEY" wrote:

> >>Example: I will have each month in a field, each year in a field, a number
> value for occurences in a field.
> You should use a DateTime DataType field to store the date instead of
> separate fields for each.
> Is the 'value for occurences' a field to indicate how many occurences there
> were or data value at the occurence.
>
> I think you need the following table structure --
> Occurence --
> OccurenceID - Autonumber - Primary key
> OccurDate - DateTime
> Value - Number - long integer (or maybe single based of data being stored -
> 1, 2, 5, etc. OR 2.1, 5.7, 3.97, etc.)
>
> For the last 3 months use this query --
> SELECT Count([OccurenceID] AS Occurences
> FROM Occurence
> WHERE OccurDate Between Date() - Day(Date()) And DateAdd("m", -3, Date() -
> Day(Date()))+1;
>
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Christine" wrote:
>
> > Help.
> > I am a novice at Access. I need to create a formula that will look for an
> > occurence of a value for a 3 month time frame.
> >
> > Example: I will have each month in a field, each year in a field, a number
> > value for occurences in a field. This will be put into a data entry form.
> >
> > I will then need to run a report that will show the # of occurrences for a
> > selected 3 month period.
> >
> > Ultimately I want users to be alerted when an occurrence is approaching 3
> > within a 3 month period.
> >
> > How do I do this?

What is it you do not understand?
I know what you want to do with your data. What I assumed was that your
data was stored like a spreadsheet.
Explain how your data is stored in the same fashion as below --
Absence table --
Employee - text
AbsenceMonth - text
AbsenceYear - number
Occurences - number

Sample data --
Joe Smith July 2008 2
Bill Jones July 2008 1
Joe Smith June 2008 0
Bill Jones June 2008 1

--
KARL DEWEY
Build a little - Test a little


"Christine" wrote:

> Karl: Thank you, but I think I'm not savvy enough for that.
>
> I think I have a better explanation now of what I'm trying to do.
>
> It's absence database -- employees are allowed so many occurences in a 3
> month, 9 month, and 12 month in a rolling 12 month timeframe. What I am
> trying to identify is those employees getting close to 3, 9, & 12 months.
> My absence dates are stored as mm/dd/yy. Here's my other problem if they
> have for example 3 in 3month time frame and don't have any in the next 3
> months there bank is set back to 0. Write now its a manual review process
> and if I could just get the rolling 12 function to work, it would cut down
> alot of time.
>
> Thanks very much.
>
> "KARL DEWEY" wrote:
>
> > >>Example: I will have each month in a field, each year in a field, a number
> > value for occurences in a field.
> > You should use a DateTime DataType field to store the date instead of
> > separate fields for each.
> > Is the 'value for occurences' a field to indicate how many occurences there
> > were or data value at the occurence.
> >
> > I think you need the following table structure --
> > Occurence --
> > OccurenceID - Autonumber - Primary key
> > OccurDate - DateTime
> > Value - Number - long integer (or maybe single based of data being stored -
> > 1, 2, 5, etc. OR 2.1, 5.7, 3.97, etc.)
> >
> > For the last 3 months use this query --
> > SELECT Count([OccurenceID] AS Occurences
> > FROM Occurence
> > WHERE OccurDate Between Date() - Day(Date()) And DateAdd("m", -3, Date() -
> > Day(Date()))+1;
> >
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "Christine" wrote:
> >
> > > Help.
> > > I am a novice at Access. I need to create a formula that will look for an
> > > occurence of a value for a 3 month time frame.
> > >
> > > Example: I will have each month in a field, each year in a field, a number
> > > value for occurences in a field. This will be put into a data entry form.
> > >
> > > I will then need to run a report that will show the # of occurrences for a
> > > selected 3 month period.
> > >
> > > Ultimately I want users to be alerted when an occurrence is approaching 3
> > > within a 3 month period.
> > >
> > > How do I do this?