Struggling with query

[Guest]

I am new to access...I am need of help!! I have been working on this project
for about a month trying to get it to work. This is the problem I am trying
to get the sum for a field: totpointsaccessed. Example: Albert has two
reports in the first report he receive 2 points and in the second report he
receive 1 point, now I am trying to get the total for the points Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total points
accessed",1,0) but it does not work. i went to the query to calculated the
field there but when I hit the E and use my total field as Sum I can not add
any record to the form....any help would be appreciated. Just to keep you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by Employee]) AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by Department]) AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN [tbl Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Tom Ellison]

Dear LH:

I have reproduced your query here for my study, using my own preferences to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part of body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital], A.[Emergency care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position, A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable, A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3 things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more information about
where this comes from.

I hope this may be a step in the right direction. If you wish to proceed,
please let me know.

Tom Ellison

I am new to access...I am need of help!! I have been working on this
project
for about a month trying to get it to work. This is the problem I am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has two
reports in the first report he receive 2 points and in the second report
he
receive 1 point, now I am trying to get the total for the points Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total points
accessed",1,0) but it does not work. i went to the query to calculated the
field there but when I hit the E and use my total field as Sum I can not
add
any record to the form....any help would be appreciated. Just to keep you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by Employee]) AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN [tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Guest]

Thank you Tom but this does not work I get and syntax error for this part of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID = EmployeeAccident.EmployeeID)...why are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access points to
those who have violated the safety policy, and the only thing they ask is to
keep track of how many total points employees received. Now for the Employee
and Department I tried =sum([department]) and it did not work so I use count,
because all I need to do is keep track of how many accidents has happen
within the department and by which employee. Thanks for leading me in the
right direction
--
LHEMA

Dear LH:

I have reproduced your query here for my study, using my own preferences to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part of body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital], A.[Emergency care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position, A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable, A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3 things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more information about
where this comes from.

I hope this may be a step in the right direction. If you wish to proceed,
please let me know.

Tom Ellison

I am new to access...I am need of help!! I have been working on this
project
for about a month trying to get it to work. This is the problem I am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has two
reports in the first report he receive 2 points and in the second report
he
receive 1 point, now I am trying to get the total for the points Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total points
accessed",1,0) but it does not work. i went to the query to calculated the
field there but when I hit the E and use my total field as Sum I can not
add
any record to the form....any help would be appreciated. Just to keep you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by Employee]) AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN [tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Tom Ellison]

Dear LHEMA:

Those are aliases. If you had not changed them it might have worked.

So, look up aliases in the online help and try the query just as I posted
it.

Tom Ellison

Thank you Tom but this does not work I get and syntax error for this part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID = EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access points to
those who have violated the safety policy, and the only thing they ask is
to
keep track of how many total points employees received. Now for the
Employee
and Department I tried =sum([department]) and it did not work so I use
count,
because all I need to do is keep track of how many accidents has happen
within the department and by which employee. Thanks for leading me in the
right direction
--
LHEMA

Dear LH:

I have reproduced your query here for my study, using my own preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital], A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable, A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison

I am new to access...I am need of help!! I have been working on this
project
for about a month trying to get it to work. This is the problem I am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has two
reports in the first report he receive 2 points and in the second
report
he
receive 1 point, now I am trying to get the total for the points Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total
points
accessed",1,0) but it does not work. i went to the query to calculated
the
field there but when I hit the E and use my total field as Sum I can
not
add
any record to the form....any help would be appreciated. Just to keep
you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by Employee])
AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by
Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN [tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Guest]

I have copy this sql just like the one you posted and it does not work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that part but
with the error I am still struggling with this qry....let me share more about
the database. My relationship are as followed:tbl EA related tbl Accident one
to many jointo accidnetID and tbl TotalAccident related tbl Accidnet one to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both tables are
equal, I am at a lost please help
--
LHEMA

Dear LHEMA:

Those are aliases. If you had not changed them it might have worked.

So, look up aliases in the online help and try the query just as I posted
it.

Tom Ellison

Thank you Tom but this does not work I get and syntax error for this part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID = EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access points to
those who have violated the safety policy, and the only thing they ask is
to
keep track of how many total points employees received. Now for the
Employee
and Department I tried =sum([department]) and it did not work so I use
count,
because all I need to do is keep track of how many accidents has happen
within the department and by which employee. Thanks for leading me in the
right direction
--
LHEMA

Dear LH:

I have reproduced your query here for my study, using my own preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital], A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable, A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working on this
project
for about a month trying to get it to work. This is the problem I am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has two
reports in the first report he receive 2 points and in the second
report
he
receive 1 point, now I am trying to get the total for the points Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total
points
accessed",1,0) but it does not work. i went to the query to calculated
the
field there but when I hit the E and use my total field as Sum I can
not
add
any record to the form....any help would be appreciated. Just to keep
you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by Employee])
AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by
Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN [tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Guest]

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.
They are suppose to work like this Albert reported 2 accidents in the Road
deparment and 1 point was accessed to him by the SRB for each report and John
reported 1 accident in the Sheriff Department and 0 points accessed to him.
Now for the total it should read total by employee: 2 for Albert and 1 for
John ,total by department: should be 2 for road dept and 1 for sheriff and
points accessed should total for ALbert is 2 and 0 for John, its not workly
at all any help is appreciated
--
LHEMA

I have copy this sql just like the one you posted and it does not work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that part but
with the error I am still struggling with this qry....let me share more about
the database. My relationship are as followed:tbl EA related tbl Accident one
to many jointo accidnetID and tbl TotalAccident related tbl Accidnet one to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both tables are
equal, I am at a lost please help
--
LHEMA

Dear LHEMA:

Those are aliases. If you had not changed them it might have worked.

So, look up aliases in the online help and try the query just as I posted
it.

Tom Ellison

Thank you Tom but this does not work I get and syntax error for this part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID = EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access points to
those who have violated the safety policy, and the only thing they ask is
to
keep track of how many total points employees received. Now for the
Employee
and Department I tried =sum([department]) and it did not work so I use
count,
because all I need to do is keep track of how many accidents has happen
within the department and by which employee. Thanks for leading me in the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital], A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable, A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position, A.Telephone, A.[Date of report], A.[Points Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working on this
project
for about a month trying to get it to work. This is the problem I am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has two
reports in the first report he receive 2 points and in the second
report
he
receive 1 point, now I am trying to get the total for the points Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total
points
accessed",1,0) but it does not work. i went to the query to calculated
the
field there but when I hit the E and use my total field as Sum I can
not
add
any record to the form....any help would be appreciated. Just to keep
you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by Employee])
AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by
Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN [tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Tom Ellison]

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

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.
They are suppose to work like this Albert reported 2 accidents in the Road
deparment and 1 point was accessed to him by the SRB for each report and
John
reported 1 accident in the Sheriff Department and 0 points accessed to
him.
Now for the total it should read total by employee: 2 for Albert and 1 for
John ,total by department: should be 2 for road dept and 1 for sheriff and
points accessed should total for ALbert is 2 and 0 for John, its not
workly
at all any help is appreciated
--
LHEMA

I have copy this sql just like the one you posted and it does not work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that part but
with the error I am still struggling with this qry....let me share more
about
the database. My relationship are as followed:tbl EA related tbl Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl Accidnet one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both tables
are
equal, I am at a lost please help
--
LHEMA

Dear LHEMA:

Those are aliases. If you had not changed them it might have worked.

So, look up aliases in the online help and try the query just as I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error for this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access
points to
those who have violated the safety policy, and the only thing they
ask is
to
keep track of how many total points employees received. Now for the
Employee
and Department I tried =sum([department]) and it did not work so I
use
count,
because all I need to do is keep track of how many accidents has
happen
within the department and by which employee. Thanks for leading me in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more
information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working on
this
project
for about a month trying to get it to work. This is the problem I
am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has
two
reports in the first report he receive 2 points and in the second
report
he
receive 1 point, now I am trying to get the total for the points
Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total
points
accessed",1,0) but it does not work. i went to the query to
calculated
the
field there but when I hit the E and use my total field as Sum I
can
not
add
any record to the form....any help would be appreciated. Just to
keep
you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by
Employee])
AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by
Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total
points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN
[tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl
Employee
Accident].AccidentID
GROUP BY [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;

--Thank you
LHEMA

 

[Guest]

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

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.
They are suppose to work like this Albert reported 2 accidents in the Road
deparment and 1 point was accessed to him by the SRB for each report and
John
reported 1 accident in the Sheriff Department and 0 points accessed to
him.
Now for the total it should read total by employee: 2 for Albert and 1 for
John ,total by department: should be 2 for road dept and 1 for sheriff and
points accessed should total for ALbert is 2 and 0 for John, its not
workly
at all any help is appreciated
--
LHEMA

I have copy this sql just like the one you posted and it does not work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that part but
with the error I am still struggling with this qry....let me share more
about
the database. My relationship are as followed:tbl EA related tbl Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl Accidnet one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have worked.

So, look up aliases in the online help and try the query just as I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error for this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access
points to
those who have violated the safety policy, and the only thing they
ask is
to
keep track of how many total points employees received. Now for the
Employee
and Department I tried =sum([department]) and it did not work so I
use
count,
because all I need to do is keep track of how many accidents has
happen
within the department and by which employee. Thanks for leading me in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began], A.[Date employer
notified],
A.[Did employee work the next day], A.[Type of Injury], A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those 3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper Totals?

To produce a SumOfTotal Points Assessed I would need more
information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working on
this
project
for about a month trying to get it to work. This is the problem I
am
trying
to get the sum for a field: totpointsaccessed. Example: Albert has
two
reports in the first report he receive 2 points and in the second
report
he
receive 1 point, now I am trying to get the total for the points
Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]= "total
points
accessed",1,0) but it does not work. i went to the query to
calculated
the
field there but when I hit the E and use my total field as Sum I
can
not
add
any record to the form....any help would be appreciated. Just to
keep
you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by
Employee])
AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by
Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total
points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT JOIN
[tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl
Employee
Accident].AccidentID
GROUP BY [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

 

[Tom Ellison]

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

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.
They are suppose to work like this Albert reported 2 accidents in the
Road
deparment and 1 point was accessed to him by the SRB for each report
and
John
reported 1 accident in the Sheriff Department and 0 points accessed to
him.
Now for the total it should read total by employee: 2 for Albert and 1
for
John ,total by department: should be 2 for road dept and 1 for sheriff
and
points accessed should total for ALbert is 2 and 0 for John, its not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does not
work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that part
but
with the error I am still struggling with this qry....let me share
more
about
the database. My relationship are as followed:tbl EA related tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have
worked.

So, look up aliases in the online help and try the query just as I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access
points to
those who have violated the safety policy, and the only thing they
ask is
to
keep track of how many total points employees received. Now for
the
Employee
and Department I tried =sum([department]) and it did not work so I
use
count,
because all I need to do is keep track of how many accidents has
happen
within the department and by which employee. Thanks for leading me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date
of
accident],
A.[Time of accident], A.[Time workday began], A.[Date
employer
notified],
A.[Did employee work the next day], A.[Type of Injury],
A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital],
A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those
3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could
be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper
Totals?

To produce a SumOfTotal Points Assessed I would need more
information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working on
this
project
for about a month trying to get it to work. This is the problem
I
am
trying
to get the sum for a field: totpointsaccessed. Example: Albert
has
two
reports in the first report he receive 2 points and in the
second
report
he
receive 1 point, now I am trying to get the total for the
points
Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]=
"total
points
accessed",1,0) but it does not work. i went to the query to
calculated
the
field there but when I hit the E and use my total field as Sum
I
can
not
add
any record to the form....any help would be appreciated. Just
to
keep
you
inform with what I have done here is 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, Sum([tbl Total Accidents].[Total by
Employee])
AS
[SumOfTotal by Employee], Sum([tbl Total Accidents].[Total by
Department])
AS
[SumOfTotal by Department], Sum([tbl Total Accidents].[Total
points
accessed]) AS [SumOfTotal points accessed]
FROM [tbl Accidents] LEFT JOIN ([tbl Employee Accident] LEFT
JOIN
[tbl
Total
Accidents] ON [tbl Employee Accident].AccidentID = [tbl Total
Accidents].AccidentID) ON [tbl Accidents].AccidentID = [tbl
Employee
Accident].AccidentID
GROUP BY [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

 

[Guest]

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

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.
They are suppose to work like this Albert reported 2 accidents in the
Road
deparment and 1 point was accessed to him by the SRB for each report
and
John
reported 1 accident in the Sheriff Department and 0 points accessed to
him.
Now for the total it should read total by employee: 2 for Albert and 1
for
John ,total by department: should be 2 for road dept and 1 for sheriff
and
points accessed should total for ALbert is 2 and 0 for John, its not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does not
work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that part
but
with the error I am still struggling with this qry....let me share
more
about
the database. My relationship are as followed:tbl EA related tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have
worked.

So, look up aliases in the online help and try the query just as I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will access
points to
those who have violated the safety policy, and the only thing they
ask is
to
keep track of how many total points employees received. Now for
the
Employee
and Department I tried =sum([department]) and it did not work so I
use
count,
because all I need to do is keep track of how many accidents has
happen
within the department and by which employee. Thanks for leading me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident], A.[Date
of
accident],
A.[Time of accident], A.[Time workday began], A.[Date
employer
notified],
A.[Did employee work the next day], A.[Type of Injury],
A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital],
A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by], A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of those
3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It could
be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired], EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper
Totals?

To produce a SumOfTotal Points Assessed I would need more
information
about
where this comes from.

I hope this may be a step in the right direction. If you wish to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working on
this
project
for about a month trying to get it to work. This is the problem
I
am
trying
to get the sum for a field: totpointsaccessed. Example: Albert
has
two
reports in the first report he receive 2 points and in the
second
report
he
receive 1 point, now I am trying to get the total for the
points
Albert
receive which should be 3 points, I am getting 0. I have tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]=
"total
points
accessed",1,0) but it does not work. i went to the query to
calculated
the
field there but when I hit the E and use my total field as Sum
I
can
not
add
any record to the form....any help would be appreciated. Just
to
keep
you
inform with what I have done here is my SQL:

 

[Tom Ellison]

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

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.
They are suppose to work like this Albert reported 2 accidents in
the
Road
deparment and 1 point was accessed to him by the SRB for each report
and
John
reported 1 accident in the Sheriff Department and 0 points accessed
to
him.
Now for the total it should read total by employee: 2 for Albert and
1
for
John ,total by department: should be 2 for road dept and 1 for
sheriff
and
points accessed should total for ALbert is 2 and 0 for John, its not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does not
work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that
part
but
with the error I am still struggling with this qry....let me share
more
about
the database. My relationship are as followed:tbl EA related tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have
worked.

So, look up aliases in the online help and try the query just as
I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will
access
points to
those who have violated the safety policy, and the only thing
they
ask is
to
keep track of how many total points employees received. Now for
the
Employee
and Department I tried =sum([department]) and it did not work
so I
use
count,
because all I need to do is keep track of how many accidents
has
happen
within the department and by which employee. Thanks for leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by
Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident],
A.[Date
of
accident],
A.[Time of accident], A.[Time workday began], A.[Date
employer
notified],
A.[Did employee work the next day], A.[Type of Injury],
A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital],
A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of
those
3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents
for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It
could
be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
(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]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID

Just for starters, does this work? Does it give the proper
Totals?

To produce a SumOfTotal Points Assessed I would need more
information
about
where this comes from.

I hope this may be a step in the right direction. If you wish
to
proceed,
please let me know.

Tom Ellison



I am new to access...I am need of help!! I have been working
on
this
project
for about a month trying to get it to work. This is the
problem
I
am
trying
to get the sum for a field: totpointsaccessed. Example:
Albert
has
two
reports in the first report he receive 2 points and in the
second
report
he
receive 1 point, now I am trying to get the total for the
points
Albert
receive which should be 3 points, I am getting 0. I have
tried
=Sum([totalpointsaccessed]) and =Sum(IIf([pointsaccessed]=
"total
points
accessed",1,0) but it does not work. i went to the query to
calculated
the
field there but when I hit the E and use my total field as
Sum
I
can
not
add
any record to the form....any help would be appreciated.
Just
to
keep
you
inform with what I have done here is my SQL:

 

[Guest]

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

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.
They are suppose to work like this Albert reported 2 accidents in
the
Road
deparment and 1 point was accessed to him by the SRB for each report
and
John
reported 1 accident in the Sheriff Department and 0 points accessed
to
him.
Now for the total it should read total by employee: 2 for Albert and
1
for
John ,total by department: should be 2 for road dept and 1 for
sheriff
and
points accessed should total for ALbert is 2 and 0 for John, its not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does not
work. I
keep getting the syntax error for this particular section: (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)...i did the research on aliases i understand that
part
but
with the error I am still struggling with this qry....let me share
more
about
the database. My relationship are as followed:tbl EA related tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have
worked.

So, look up aliases in the online help and try the query just as
I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will
access
points to
those who have violated the safety policy, and the only thing
they
ask is
to
keep track of how many total points employees received. Now for
the
Employee
and Department I tried =sum([department]) and it did not work
so I
use
count,
because all I need to do is keep track of how many accidents
has
happen
within the department and by which employee. Thanks for leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by
Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department, EA.Jobtitle, A.[Location of accident],
A.[Date
of
accident],
A.[Time of accident], A.[Time workday began], A.[Date
employer
notified],
A.[Did employee work the next day], A.[Type of Injury],
A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating Hospital],
A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of
those
3
things
for just that one accident.

I take it you may want the SUM() to be accross all accidents
for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It
could
be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the next
day],
A.[Type of Injury], A.[Part of body affected], A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No

 

[Tom Ellison]

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

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.
They are suppose to work like this Albert reported 2 accidents in
the
Road
deparment and 1 point was accessed to him by the SRB for each
report
and
John
reported 1 accident in the Sheriff Department and 0 points
accessed
to
him.
Now for the total it should read total by employee: 2 for Albert
and
1
for
John ,total by department: should be 2 for road dept and 1 for
sheriff
and
points accessed should total for ALbert is 2 and 0 for John, its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does
not
work. I
keep getting the syntax error for this particular section:
(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)...i did the research on aliases i understand that
part
but
with the error I am still struggling with this qry....let me
share
more
about
the database. My relationship are as followed:tbl EA related tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have
worked.

So, look up aliases in the online help and try the query just
as
I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error
for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will
access
points to
those who have violated the safety policy, and the only
thing
they
ask is
to
keep track of how many total points employees received. Now
for
the
Employee
and Department I tried =sum([department]) and it did not
work
so I
use
count,
because all I need to do is keep track of how many accidents
has
happen
within the department and by which employee. Thanks for
leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my
own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the
next
day],
A.[Type of Injury], A.[Part of body affected],
A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by
Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by
Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date
Hired],
EA.Department, EA.Jobtitle, A.[Location of accident],
A.[Date
of
accident],
A.[Time of accident], A.[Time workday began], A.[Date
employer
notified],
A.[Did employee work the next day], A.[Type of Injury],
A.[Part
of
body
affected],
A.Summary, A.[Treating Physician], A.[Treating
Hospital],
A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency
care],
A.[Hospitalized>24hrs], A.[Report prepared by],
A.Position,
A.Telephone,
A.[Date of report], A.[Points Accessed], A.Avoidable,
A.Unavoidable;

When you GROUP BY the AccidentID, you will get the SUM() of
those
3
things
for just that one accident.

I take it you may want the SUM() to be accross all
accidents
for
that one
Employee, Department, and overall. Is that the case?

If that is what you want, you cannot do this as shown. It
could
be
done
something like this:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the
next
day],
A.[Type of Injury], A.[Part of body affected],
A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No

 

[Guest]

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

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.
They are suppose to work like this Albert reported 2 accidents in
the
Road
deparment and 1 point was accessed to him by the SRB for each
report
and
John
reported 1 accident in the Sheriff Department and 0 points
accessed
to
him.
Now for the total it should read total by employee: 2 for Albert
and
1
for
John ,total by department: should be 2 for road dept and 1 for
sheriff
and
points accessed should total for ALbert is 2 and 0 for John, its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does
not
work. I
keep getting the syntax error for this particular section:
(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)...i did the research on aliases i understand that
part
but
with the error I am still struggling with this qry....let me
share
more
about
the database. My relationship are as followed:tbl EA related tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might have
worked.

So, look up aliases in the online help and try the query just
as
I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax error
for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID = EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will
access
points to
those who have violated the safety policy, and the only
thing
they
ask is
to
keep track of how many total points employees received. Now
for
the
Employee
and Department I tried =sum([department]) and it did not
work
so I
use
count,
because all I need to do is keep track of how many accidents
has
happen
within the department and by which employee. Thanks for
leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my
own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name, EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the
next
day],
A.[Type of Injury], A.[Part of body affected],
A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs], A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report], A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by
Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by
Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date
Hired],
EA.Department, EA.Jobtitle, A.[Location of accident],
A.[Date
of
accident],
A.[Time of accident], A.[Time workday began], A.[Date
employer
notified],

 

[Tom Ellison]

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

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.
They are suppose to work like this Albert reported 2 accidents
in
the
Road
deparment and 1 point was accessed to him by the SRB for each
report
and
John
reported 1 accident in the Sheriff Department and 0 points
accessed
to
him.
Now for the total it should read total by employee: 2 for
Albert
and
1
for
John ,total by department: should be 2 for road dept and 1 for
sheriff
and
points accessed should total for ALbert is 2 and 0 for John,
its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does
not
work. I
keep getting the syntax error for this particular section:
(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)...i did the research on aliases i understand
that
part
but
with the error I am still struggling with this qry....let me
share
more
about
the database. My relationship are as followed:tbl EA related
tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom
both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might
have
worked.

So, look up aliases in the online help and try the query
just
as
I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax
error
for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID =
EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will
access
points to
those who have violated the safety policy, and the only
thing
they
ask is
to
keep track of how many total points employees received.
Now
for
the
Employee
and Department I tried =sum([department]) and it did not
work
so I
use
count,
because all I need to do is keep track of how many
accidents
has
happen
within the department and by which employee. Thanks for
leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my
own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address,
EA.City, EA.Zipcode, EA.DOB, EA.[Date Hired],
EA.Department,
EA.Jobtitle, A.[Location of accident], A.[Date of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date employer notified], A.[Did employee work the
next
day],
A.[Type of Injury], A.[Part of body affected],
A.Summary,
A.[Treating Physician], A.[Treating Hospital], A.[No
treatment],
A.[Minor:by employer], A.[Minor:by clinic/hospital],
A.[Emergency care], A.[Hospitalized>24hrs],
A.[Report
prepared
by],
A.Position, A.Telephone, A.[Date of report],
A.[Points
Accessed],
A.Avoidable, A.Unavoidable,
Sum(TA.[Total by Employee]) AS [SumOfTotal by
Employee],
Sum(TA.[Total by Department]) AS [SumOfTotal by
Department],
Sum(TA.[Total points accessed]) AS [SumOfTotal
points
accessed]
FROM [tbl Accidents] A
LEFT JOIN ([tbl Employee Accident] EA
LEFT JOIN [tbl Total Accidents] TA
ON EA.AccidentID = TA.AccidentID)
ON A.AccidentID = EA.AccidentID
GROUP BY EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address, EA.City, EA.Zipcode, EA.DOB, EA.[Date
Hired],
EA.Department, EA.Jobtitle, A.[Location of
accident],
A.[Date
of
accident],
A.[Time of accident], A.[Time workday began],
A.[Date
employer
notified],

 

[Guest]

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

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.
They are suppose to work like this Albert reported 2 accidents
in
the
Road
deparment and 1 point was accessed to him by the SRB for each
report
and
John
reported 1 accident in the Sheriff Department and 0 points
accessed
to
him.
Now for the total it should read total by employee: 2 for
Albert
and
1
for
John ,total by department: should be 2 for road dept and 1 for
sheriff
and
points accessed should total for ALbert is 2 and 0 for John,
its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it does
not
work. I
keep getting the syntax error for this particular section:
(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)...i did the research on aliases i understand
that
part
but
with the error I am still struggling with this qry....let me
share
more
about
the database. My relationship are as followed:tbl EA related
tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined fieldsfrom
both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might
have
worked.

So, look up aliases in the online help and try the query
just
as
I
posted
it.

Tom Ellison


Thank you Tom but this does not work I get and syntax
error
for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID =
EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that part

about total points accessed, Our Safety Review Board will
access
points to
those who have violated the safety policy, and the only
thing
they
ask is
to
keep track of how many total points employees received.
Now
for
the
Employee
and Department I tried =sum([department]) and it did not
work
so I
use
count,
because all I need to do is keep track of how many
accidents
has
happen
within the department and by which employee. Thanks for
leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using my
own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address,

 

[Tom Ellison]

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

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.
They are suppose to work like this Albert reported 2
accidents
in
the
Road
deparment and 1 point was accessed to him by the SRB for
each
report
and
John
reported 1 accident in the Sheriff Department and 0 points
accessed
to
him.
Now for the total it should read total by employee: 2 for
Albert
and
1
for
John ,total by department: should be 2 for road dept and 1
for
sheriff
and
points accessed should total for ALbert is 2 and 0 for
John,
its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it
does
not
work. I
keep getting the syntax error for this particular section:
(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)...i did the research on aliases i
understand
that
part
but
with the error I am still struggling with this qry....let
me
share
more
about
the database. My relationship are as followed:tbl EA
related
tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related
tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined
fieldsfrom
both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might
have
worked.

So, look up aliases in the online help and try the query
just
as
I
posted
it.

Tom Ellison


message
Thank you Tom but this does not work I get and syntax
error
for
this
part
of
the sql:
(SELECT SUM(TotalAccident1.[Total by Employee])
FROM [tbl EmployeeAccident] EmployeeAccident1
INNER JOIN [tbl Total Accidents]TotalAccident 1
ON TotalAccident1.AccidentID =
EmployeeAccident.AccidentID
WHERE EmployeeAccident1.EmployeeID =
EmployeeAccident.EmployeeID)...why
are
there 1's after EA and TA...I not understanding that
part

about total points accessed, Our Safety Review Board
will
access
points to
those who have violated the safety policy, and the
only
thing
they
ask is
to
keep track of how many total points employees
received.
Now
for
the
Employee
and Department I tried =sum([department]) and it did
not
work
so I
use
count,
because all I need to do is keep track of how many
accidents
has
happen
within the department and by which employee. Thanks
for
leading
me
in
the
right direction
--
LHEMA


:

Dear LH:

I have reproduced your query here for my study, using
my
own
preferences
to
aid my understanding:

SELECT EA.AccidentID, EA.EmployeeID, EA.Name,
EA.Address,

 

[Guest]

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

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.
They are suppose to work like this Albert reported 2
accidents
in
the
Road
deparment and 1 point was accessed to him by the SRB for
each
report
and
John
reported 1 accident in the Sheriff Department and 0 points
accessed
to
him.
Now for the total it should read total by employee: 2 for
Albert
and
1
for
John ,total by department: should be 2 for road dept and 1
for
sheriff
and
points accessed should total for ALbert is 2 and 0 for
John,
its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and it
does
not
work. I
keep getting the syntax error for this particular section:
(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)...i did the research on aliases i
understand
that
part
but
with the error I am still struggling with this qry....let
me
share
more
about
the database. My relationship are as followed:tbl EA
related
tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related
tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined
fieldsfrom
both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it might
have
worked.

So, look up aliases in the online help and try the query
just
as
I

 

[Tom Ellison]

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

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

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.
They are suppose to work like this Albert reported 2
accidents
in
the
Road
deparment and 1 point was accessed to him by the SRB for
each
report
and
John
reported 1 accident in the Sheriff Department and 0
points
accessed
to
him.
Now for the total it should read total by employee: 2
for
Albert
and
1
for
John ,total by department: should be 2 for road dept and
1
for
sheriff
and
points accessed should total for ALbert is 2 and 0 for
John,
its
not
workly
at all any help is appreciated
--
LHEMA


:

I have copy this sql just like the one you posted and
it
does
not
work. I
keep getting the syntax error for this particular
section:
(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)...i did the research on aliases i
understand
that
part
but
with the error I am still struggling with this
qry....let
me
share
more
about
the database. My relationship are as followed:tbl EA
related
tbl
Accident
one
to many jointo accidnetID and tbl TotalAccident related
tbl
Accidnet
one
to
many - join to totalaccident into AccidentID
Join type for both is only rows where the joined
fieldsfrom
both
tables
are
equal, I am at a lost please help
--
LHEMA


:

Dear LHEMA:

Those are aliases. If you had not changed them it
might
have
worked.

So, look up aliases in the online help and try the
query
just
as
I

 

[Guest]

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

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

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

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.