T
Tom Ellison
DEAR LHEMA:
In the clear light of day, I see an obvious problem in the second query.
SELECT SUM(TA1.[Total by Employee])
FROM [tbl EmployeeAccident] EA1
WHERE EA1.Department = xxxx
This could be:
SELECT SUM(TA1.[Total by Department])
FROM [tbl EmployeeAccident] EA1
INNER JOIN [tbl TotalAccidents] TA1
ON TA1.AccidentID = EA1.AccidentID
WHERE EA1.Department = xxxx
However, that doesn't seem to be a total by department. It depends on what
you mean.
Really to show you how to identify these totals, I must first know where all
the rows are that make up that total.
Also, I don't really know what you mean "Total by Department". Is that a
department total for one accident, for one employee, over some period of
time? How do you qualify what rows need to be added?
Tom Ellison
In the clear light of day, I see an obvious problem in the second query.
SELECT SUM(TA1.[Total by Employee])
FROM [tbl EmployeeAccident] EA1
WHERE EA1.Department = xxxx
This could be:
SELECT SUM(TA1.[Total by Department])
FROM [tbl EmployeeAccident] EA1
INNER JOIN [tbl TotalAccidents] TA1
ON TA1.AccidentID = EA1.AccidentID
WHERE EA1.Department = xxxx
However, that doesn't seem to be a total by department. It depends on what
you mean.
Really to show you how to identify these totals, I must first know where all
the rows are that make up that total.
Also, I don't really know what you mean "Total by Department". Is that a
department total for one accident, for one employee, over some period of
time? How do you qualify what rows need to be added?
Tom Ellison
LHEMA said:It work partially, I am receiving a -1 for the result and for the
department
I and receive and error. As for points accessed its the total for the
accident that each person received
--
LHEMA
Tom Ellison said:Dear LHEMA:
The query says:
[tbl Accidents].EmployeeID AS [Total by employee],
You've told it to report the EmployeeID as the [Total by employee].
Going way back, I gave you code for this. I said:
(SELECT SUM(TA1.[Total by Employee])
FROM [tbl EmployeeAccident] EA1
INNER JOIN [tbl Total Accidents] TA1
ON TA1.AccidentID = EA.AccidentID
WHERE EA1.EmployeeID = EA.EmployeeID)
AS [SumOfTotal by Employee]
(SELECT SUM(TA1.[Total by Employee])
FROM [tbl EmployeeAccident] EA1
WHERE EA1.Department = EA.Department)
AS [SumOfTotal by Department]
I did not give a method for finding the Total Points Assesed. Not sure I
have enough information for that.
I have no idea how you ended up with what you have now. Perhaps we need
to
go back a long way and get this working.
The advice given in this newsgroup is free of charge. The rest was just
joking around. It seems even the joking was premature.
Now, to get this working, lets concentrate mementarily on these two
smaller
queries:
SELECT SUM(TA1.[Total by Employee])
FROM [tbl EmployeeAccident] EA1
INNER JOIN [tbl Total Accidents] TA1
ON TA1.AccidentID = EA.AccidentID
WHERE EA1.EmployeeID = xxxx
For the xxxx above, put in a real employee ID number.
SELECT SUM(TA1.[Total by Employee])
FROM [tbl EmployeeAccident] EA1
WHERE EA1.Department = xxxx
Replace xxxx here with an actual department.
What does this do?
Tom Ellison
LHEMA said:In the total field for employees it is showing the name instead of the
number
of points also for the department its showing the deaprtment name and
not
the
number of points are in that department. I also notice that I can not
use
the
"E" symbol in the query, if I use the symbol I can not edit the form
LHEMA
:
Dear LHEMA:
Are you saying the name is showing up in the EmployeeID column?
Tom Ellison
the query is giving me the name and the report is working correctly
here
is
the sql:
SELECT [tbl Employee Accident].AccidentID, [tbl Employee
Accident].EmployeeID, [tbl Employee Accident].Name, [tbl Employee
Accident].Address, [tbl Employee Accident].City, [tbl Employee
Accident].Zipcode, [tbl Employee Accident].DOB, [tbl Employee
Accident].[Date
Hired], [tbl Employee Accident].Department, [tbl Employee
Accident].Jobtitle,
[tbl Accidents].[Location of accident], [tbl Accidents].[Date of
accident],
[tbl Accidents].[Time of accident], [tbl Accidents].[Time workday
began],
[tbl Accidents].[Date employer notified], [tbl Accidents].[Did
employee
work
the next day], [tbl Accidents].[Type of Injury], [tbl
Accidents].[Part
of
body affected], [tbl Accidents].Summary, [tbl Accidents].[Treating
Physician], [tbl Accidents].[Treating Hospital], [tbl Accidents].[No
treatment], [tbl Accidents].[Minor:by employer], [tbl
Accidents].[Minor:by
clinic/hospital], [tbl Accidents].[Emergency care], [tbl
Accidents].[Hospitalized>24hrs], [tbl Accidents].[Report prepared
by],
[tbl
Accidents].Position, [tbl Accidents].Telephone, [tbl
Accidents].[Date
of
report], [tbl Accidents].[Points Accessed], [tbl
Accidents].Avoidable,
[tbl
Accidents].Unavoidable, [tbl Accidents].EmployeeID AS [Total by
employee],
[tbl Employee Accident].Department AS [Total by department], [tbl
Accidents].[Points Accessed] AS [Points accessed]
FROM [tbl Employee Accident] INNER JOIN [tbl Accidents] ON [tbl
Employee
Accident].AccidentID = [tbl Accidents].AccidentID;
--
LHEMA
:
Dear LHEMA:
I didn't know there was a problem. Which column in the query has a
name
in
it which should be a number?
Tom Ellison
no because i am still stuck...I do not know why the query is
given
me
name
instead of numbers
--
LHEMA
:
That's what I was afraid of. Well, I hope you got your money's
worth!
Tom Ellison
I think it was good ol' advise....lol
--
LHEMA
:
Dear LHEMA:
Yeah, I'll send a bill. How much was it we agreed for these
services?
Tom Ellison
thanks, the struggle is almost over I hope. This is what I
have
done
I
change
everything back to where I was before, I got the form back
working
and
the
total now shows the name of the person in the query and the
numbers
in
the
report ....what have I done here is an refresher of my sql:
SELECT [tbl Employee Accident].AccidentID, [tbl Employee
Accident].EmployeeID, [tbl Employee Accident].Name, [tbl
Employee
Accident].Address, [tbl Employee Accident].City, [tbl
Employee
Accident].Zipcode, [tbl Employee Accident].DOB, [tbl
Employee
Accident].[Date
Hired], [tbl Employee Accident].Department, [tbl Employee
Accident].Jobtitle,
[tbl Accidents].[Location of accident], [tbl
Accidents].[Date
of
accident],
[tbl Accidents].[Time of accident], [tbl Accidents].[Time
workday
began],
[tbl Accidents].[Date employer notified], [tbl
Accidents].[Did
employee
work
the next day], [tbl Accidents].[Type of Injury], [tbl
Accidents].[Part
of
body affected], [tbl Accidents].Summary, [tbl
Accidents].[Treating
Physician], [tbl Accidents].[Treating Hospital], [tbl
Accidents].[No
treatment], [tbl Accidents].[Minor:by employer], [tbl
Accidents].[Minor:by
clinic/hospital], [tbl Accidents].[Emergency care], [tbl
Accidents].[Hospitalized>24hrs], [tbl Accidents].[Report
prepared
by],
[tbl
Accidents].Position, [tbl Accidents].Telephone, [tbl
Accidents].[Date
of
report], [tbl Accidents].[Points Accessed], [tbl
Accidents].Avoidable,
[tbl
Accidents].Unavoidable, [tbl Accidents].EmployeeID AS
[Total
by
employee],
[tbl Employee Accident].Department AS [Total by
department],
[tbl
Accidents].[Points Accessed] AS [Points accessed]
FROM [tbl Employee Accident] INNER JOIN [tbl Accidents] ON
[tbl
Employee
Accident].AccidentID = [tbl Accidents].AccidentID;
Thanks for all your help....PS... send the bill to Liberty
Emergency
Management
--
LHEMA
:
Dear LHEMA:
When several rows are aggregated in a query you cannot
then
edit
the
data.
Indeed, this concept makes no sense. Edit the tables, not
the
aggregate
query.
Tom Ellison
Within the query....I just notice now that I can not add
new
records,
I'm
stll struggling
--
LHEMA
:
Dear LHEMA:
How are you accomplishing these totals? Within the
query
or
by
adding
the
values within the report, accross and/or down?
It looks like you should not use sum to add accross.
Use
=
count(employeeID) + count(department).
Sum() adds "down" a column, not accross.
Does that help?
Tom Ellison
message
Good morning Tom
I got it to work,I made a couple of changes and it
work
almost
perfect.
The
only thing wrong is the total on the report does not
total
correctly. I
have
them group and the expression I use
=count([employeeID]),
=count([department]), =sum([points accessed])
they are not working correctly.