Sheri:
That the two types of incident have no relation was the very point I was
making, which is why I was suggesting having them as two separate subreport's
within a single main report. Whether you have one main report with two
subreports in it, or two separate main reports the underlying basis is much
the same.
With two separate reports, if you make the changes to the tables I suggested
then an employee incidents report for a date range defined by start and end
dates entered as parameters when the report is opened would be based on a
query such as this:
PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [employeeID], [name], [datetime of accident], [location]
FROM [tbl Employee] INNER JOIN [tbl employee incident]
ON [tbl Employee].[employeeID] = [tbl Employee Incident].[employeeID]
WHERE [datetime of accident] >= [Enter start date:]
AND [datetime of accident] < DATEADD("d", 1, [Enter end date:]);
Note that I've declared the parameters. This is always a good idea with
date time data types as otherwise a date parameter value entered at the
prompt in a short date format might be interpreted as an arithmetical
expression and give the wrong result.
This should return one row per incident. In report design view sort the
report first by name and then by employeeID and then by datetime of accident
to group it by employee and sort by date within each employee's set of
incidents.
If you want to count the number of incidents per employee, give the
employeeID group a group footer in the sorting and grouping dialogue in
report design view and put a text box in the group footer with a
ControlSource property of:
=Count(*)
You can also put a text box in the report footer with the same ControlSource
property to give a count of incidents for all employees if you wish.
A report of vehicle accidents would use a similar query, but this rime
joining the tbl Employee and tbl vehicle accident tables like so:
PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [employeeID], [name], [datetime of accident], [place of accident],
[driving points]
FROM [tbl Employee] INNER JOIN [tbl vehicle accident]
ON [tbl Employee].[employeeID] = [tbl vehicle accident].[employeeID]
WHERE [datetime of accident] >= [Enter start date:]
AND [datetime of accident] < DATEADD("d", 1, [Enter end date:]);
This report would be set up in the same way, sorted first by name and then
by employeeID and then by datetime of accident, giving it an employeeID group
footer. To sum the total points per employee put a text box in the group
footer with a ControlSource property of:
=Sum([driving points])
Note that in both cases the footer is on the employeeID group, not the name
group. This is to cater for the possibility of two employees having the same
name, in which case they'd show as separate groups because the grouping is on
the unique employeeID values.
The above should give you what you are looking for. If not then the problem
probably lies in the values of the employeeID foreign keys in tbl employee
incident and tbl vehicle incident. Going back to your original example of
John, the value in the employeeID column in his row in tbl Employee, and in
each of the rows for his incidents in tbl employee incident and tbl employee
incident should be 2 in each case. This is how rows in different tables are
related to each other, by having matching values in the relevant primary and
foreign key columns. As the employeeID values in tbl employee incident and
tbl vehicle incident will be duplicated they cannot be autonumber columns of
course, but must be straightforward long integer number data types. The
employeeID column in tbl Employee can be an autonumber however.
For data entry the usual arrangement would be to have an employees form,
based on the tbl Employee table, and two subforms within it, one based on tbl
employee incident, the other on tbl vehicle incident. Both subforms would be
linked to the parent employees form on employeeID. As incidents are entered
in the subforms the employeeID of the current employee will automatically be
entered into the subforms underlying tables by means of the linking mechanism.
Ken Sheridan
Stafford, England
LHEMA said:
Wow you shed some light but the two reports are separate. When an employee
have an incident it has nothing to do with the vehicle report. Not all
employee will drive a county vehicle. They have no relation. The problem that
I have is that I am not getting the right total for driving points for each
individual on the vehicle report. As for the inicident report I am getting
duplicate entries instead of have subdata attach to each employee ID if they
have multiple incidents
--
Sheri
Ken Sheridan said:
Sheri:
I think your problem stems from your trying to cover both (non-vehicular)
employee incidents and vehicle accidents in a single main report. As the two
are not directly related other than by employee, a better approach would be
to have a main report based on the employees table and two subreports
embedded in its detail section, one for incidents one vehicle accidents.
Each subreport would be linked to the main report on employeeID. You can
then include a text box in the footer of the vehicle accidents subreport to
show the total points per employee, with a ControlSource property of:
=Sum([driving points])
I see that you have vehicleID as the primary key of the vehicle accidents
table. This really should be a foreign key referencing the primary key of a
separate vehicles table containing one row per vehicle. The primary key of
the vehicle accidents table could a composite one made up of the vehicleID
and the accident date and time columns, or one made up of the employeeID and
the accident date and time columns. Both sets of three columns are
'candidate keys', so if one set is chosen as the primary key, the other set
should be indexed uniquely, as a set that is, not individually.
In fact it would be better to have a single accident datetime column in this
table rather than separate columns for the date and time. In Access there
is no such thing as date value or time value per se. A date entered without
a time actually has a time of day of midnight at the start of the day, and a
time entered without a date is in fact the time on 30 December 1899, which is
day zero in Access's date/time implementation. Having both date and time in
one column is a better approach therefore.
This does have one implication for defining a date range, however as if you
use a BETWEEN….AND operation it will not return datetime values on the final
day of the range other than those precisely at midnight at the start of the
day. A date range should be defined like this therefore:
WHERE [Accident DateTime] >= [Enter start date:] AND [Accident DateTime] <
DATEADD("d", 1, [Enter end date:])
This looks for dates on or after the start date and before the day following
the end date, so picks up values on the last day regardless of their time of
day.
The same applies to the dates/times in the employee incident table of course
where the primary key should be a combination of an employeeID and incident
datetime columns, the former being a foreign key referencing the primary key
of the employees table.
Ken Sheridan
Stafford, England
LHEMA said:
I have the driving points and total points as a footer in my report but the
total does not calculate. Also what other work needs to be done to the
database, you mention it earlier
--
Sheri
:
On Thu, 19 Jun 2008 08:39:01 -0700, LHEMA
as for the total points in our policy an
employee is only allowed 15 points in one year so when given points for
accidents I was ask to keep track of the points they receive and on the last
report I should always have a total to show the Board how many total points
that employee receive.
That total should not be stored in each record. You can use a query,
a Sum in a report header or footer, or a DSUM to calulate the total
points from multiple records whenever you need it.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
