Make Table Query

[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.

 

[Conan Kelly]

Gomez3,

DateSerial(2007,Month(Date()),Day(Date())) will return the current month &
day of 2007
DateSerial(Year(Date())-1,Month(Date()),Day(Date())) will return the current
month & day of the previous year.
DateSerial(2006,Month(Date()),Day(Date())) will return the current month &
day of 2006
DateSerial(Year(Date())-2,Month(Date()),Day(Date())) will return the current
month & day of 2 years back.

Also, as most people here would probably say, having more than one table
with the exact same design is a poor DB design. Your 3 tables you listed
(2006, 2007, Current) could be combined into one table if you add a "Year"
field/column. But, I'm guessing that there is some sort of "Date of
Incident" field/column in the tables already. If that is the case, then
there is no need to add a column to the table. All you would need to do is
create this Year column in a query like so..."Year: Year([Date of
Incedent]).

Keep in mind that by no means am I an Access/DB expert, but the only time
where 2 tables with the exact same design might make sense is when you have
an "Archive" table to remove old records from the current table to make
queries on current data run faster (please, any experts out there, correct
me if I'm wrong....feel free to agree or disagree).

HTH,

Conan

 

[John W. Vinson]

I currently have multiple tables in a database that contain data seperated by
year. Incidents 2006, Incidents2007, IncidentsCurrent (incidents for current
year), etc.

Conan's advice is right on the money. This design might make sense for
worksheets in an Excel workbook, but it's Just Dead Wrong for a relational
database. You should have *one* Incidents table, and if you need data for a
given year, use a Query filtering the incident date with a criterion such as

= DateSerial([Enter year:], 1, 1) AND < DateSerial([Enter year:] + 1, 1, 1)

John W. Vinson [MVP]