query vehicle accidents
SELECT [tbl Accidents in County Vehicles].[Accident#], [tbl Accidents in
County Vehicles].EmployeeID, [tbl Accidents in County Vehicles].Department,
[tbl Accidents in County Vehicles].Date, [tbl Accidents in County
Vehicles].Time, [tbl Accidents in County Vehicles].Location, [tbl Accidents
in County Vehicles].Description, [tbl Accidents in County
Vehicles].InjuryReport, [tbl Accidents in County Vehicles].VehDamageReport,
[tbl Accidents in County Vehicles].RepairEstimate, [tbl Accidents in County
Vehicles].AtFault, [tbl Accidents in County Vehicles].[Driving Points], [tbl
Accidents in County Vehicles].Total
FROM [tbl Accidents in County Vehicles];
qry Employee Incident
SELECT [tbl Employee Incidents].AccidentID, [tbl Employee Incidents].Name,
[tbl Employee Incidents].Address, [tbl Employee Incidents].City, [tbl
Employee Incidents].Zipcode, [tbl Employee Incidents].DOB, [tbl Employee
Incidents].[Date Hired], [tbl Employee Incidents].Department, [tbl Employee
Incidents].Jobtitle, [tbl Employee Incidents].[Location of accident], [tbl
Employee Incidents].[Date of accident], [tbl Employee Incidents].[Time of
accident], [tbl Employee Incidents].[Time workday began], [tbl Employee
Incidents].[Date employer notified], [tbl Employee Incidents].[Did employee
work the next day], [tbl Employee Incidents].[Type of Injury], [tbl Employee
Incidents].[Part of body affected], [tbl Employee Incidents].Summary, [tbl
Employee Incidents].[Treating Physician], [tbl Employee Incidents].[Treating
Hospital], [tbl Employee Incidents].[No treatment], [tbl Employee
Incidents].[Minor:by employer], [tbl Employee Incidents].[Minor:by
clinic/hospital], [tbl Employee Incidents].[Emergency care], [tbl Employee
Incidents].[Hospitalized>24hrs], [tbl Employee Incidents].[Report prepared
by], [tbl Employee Incidents].Position, [tbl Employee Incidents].Telephone,
[tbl Employee Incidents].[Date of report], [tbl Employee
Incidents].Avoidable, [tbl Employee Incidents].Unavoidable
FROM [tbl Employee Incidents];
Maybe this will help
--
Sheri
Evi said:
You really should have an Employee table. It will contain EmpID (primary
Key) FirstName, LastName, Dept, JobTitle. These fields will not be in your
Accident Table. the Accident table will now contain AccidentID and the
foreign key field EmployeeID..
You need only add to the Employee table those Employees who have had
accidents if you want to. However, not all employees need to be added to the
Accident table whilst John will probably need to be added several times
before he is fired!
This is an excellent example of why tables need to be 'Normalized'.
If you don't do this, then you only have to spell John's name or his
department slightly wrongly for him to get away with his last misdemeanor or
else you will have to ensure that your company never puts 2 John Smiths into
the same department. And woe betide you if Diane Leggot from Human Resources
finally gets married and changes her name to Diane Atwood or Willoughby
manages to get his promotion to another department
The other advantage is that next time John scrapes the Director's Mercedes
you will only have to click a combo box for him to be entered instead of
having to type all his guff over again.
Trust me, if you don't do it now, you will have to do it when your database
gets bigger and it will be harder then. Don't make me say 'I told you so'!
Evi
Yes my primary key is accidentID in the vehicle table I do not have an
Employee table because not all emplyees drive vehicles. Here is the table
for
the vehicles
AccidentID
Lastname
Firstname
dept
jobtitle
date
time
location
description
Injury
damage
repair estimate
at fault - yes/no/ undetermine
driving points
total for driving points
total for repair estimate
I hope this helps
--
Sheri
:
Hopefully, your database will have (at least) an Employee table (Primary
Key
EmpID)
and an Accident Table. This latter should have EmpID as the Foreign Key.
It
will contain a record for each accident, some date field (say
AccidentDate)
and Points
If you have this structure then what you want is easy.
Use the Sorting Grouping box to Group by EmployeeID. Here, you can
choose to
have a Header and Footer for this group.
In the footer, you can
=Sum(Points) (to get the total of that person's points
and
in the other one have
=IIf([AccidentDate]=Max([AccidentDate]),[Points])
Evi
In my report I have a footer for the total. The database is design to
keep
track of all vehicle accidents that happen and if they are access
driving
points it should total each employee separately. Example
On 3-9 John receive 4 points for careless driving and on 4-8 he
receive 2
points so when I print a report for john it would give me the last
points
access which is 2 and then the total points which is 6, is this
possible.
Also I need to do this for each employee, and in my database I have
about
360
employees. Thanks in advance
