Formula and Data Entry Field

[Christine]

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 DEWEY]

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;

 

[Christine]

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.

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

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 DEWEY]

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

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.

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

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?