G
Gomez3
I currently have multiple tables in a database that contain data seperated by
year. Incidents 2006, Incidents2007, IncidentsCurrent (incidents for current
year), etc.
I have a make table query linked to the IncidentsCurrent. In this query I
have a DateDiff function to calculate the number of days between 1/1/2008 and
Date() (todays date). Useing this I calculate average incidents per day,
estimated incidents for the year, etc. What I need to do is create another
query for previous years that does the same thing. The problem I have is
figureing out how to get a similar function to DateDiff that will calculate
from e.g. 1/1/2007 to how ever many days we are into the current year and
reference that back as an end date in 2007 to make comparitive calculations
for incidents over the same time frame.
Once I'm able to do this I will be able to then compare current incident
counts to date from those to date of previous years, which will also allow me
to calculate percentage increases in count numbers year to year.
The current query I have referenced to the Incidents Current Table bases its
results off the total incidents in the database and updates itself as new
incidents are entered. Basically a running tally year to date - with
calculations from that. I will need to some how limit the results of
previous years to the same number of days into the year as the current date
also.
Any help would be appreciated.
year. Incidents 2006, Incidents2007, IncidentsCurrent (incidents for current
year), etc.
I have a make table query linked to the IncidentsCurrent. In this query I
have a DateDiff function to calculate the number of days between 1/1/2008 and
Date() (todays date). Useing this I calculate average incidents per day,
estimated incidents for the year, etc. What I need to do is create another
query for previous years that does the same thing. The problem I have is
figureing out how to get a similar function to DateDiff that will calculate
from e.g. 1/1/2007 to how ever many days we are into the current year and
reference that back as an end date in 2007 to make comparitive calculations
for incidents over the same time frame.
Once I'm able to do this I will be able to then compare current incident
counts to date from those to date of previous years, which will also allow me
to calculate percentage increases in count numbers year to year.
The current query I have referenced to the Incidents Current Table bases its
results off the total incidents in the database and updates itself as new
incidents are entered. Basically a running tally year to date - with
calculations from that. I will need to some how limit the results of
previous years to the same number of days into the year as the current date
also.
Any help would be appreciated.