I have 12 working areas to report injuries on.
Every morning the area supervisors look at my form that displays:
Area
Injuries YTD (YearToDate)
Injury Free Days (Current since last Area Injury)
Now they would like to see a column with the AreasMostInjuryFreeDaysToDate.
This is above my level of expertise.
The CmInjury table has the data for each injury including fields:
dtmInjury (which is the date/time stamp of the injury)
strJobNum (which is the employees jobnum)
strJobNum joins tables CmInjury and CmOccupation
lngCostCentre joins tables CmOccupation and CmCostCentre (1 to many -- key
index of CmCostCentre)
lngCostCentre joins tables CmCostCentre and CmArea
(An Area can have many costcentres and they can be duplicated in other
areas
i.e. areas can be groups of areas -- I find this confusing and I work
here)
My query pulls the areas from the 4 joined tables and calculates the
[Injuries YTD] and [Injury Free Days] fields via the dtmInjury field in the
CmInjury table:
SELECT
CmArea.Area,
Sum(Abs(DatePart("yyyy",[CmInjury]![dtmInjury])=DatePart("yyyy",Now())))
AS [Injuries YTD],
DateDiff("d",Max([dtmInjury]),Now()) AS [Injury Free Days]
FROM
(CmCostCentre INNER JOIN CmArea ON CmCostCentre.lngCostCentre =
CmArea.lngCostCentre)
INNER JOIN
(CmOccupation INNER JOIN CmInjury ON CmOccupation.strJobNum =
CmInjury.strJobNum)
ON CmCostCentre.lngCostCentre = CmOccupation.lngCostCentre
WHERE (((CmInjury.blnNonOccupational)=False))
GROUP BY CmArea.Area;
---------------------------------------------
I can get the area and datetime sorted by area then datetime with:
SELECT
CmArea.Area,
CmInjury.dtmInjury
FROM
(CmCostCentre INNER JOIN CmArea ON CmCostCentre.lngCostCentre =
CmArea.lngCostCentre)
INNER JOIN
(CmOccupation INNER JOIN CmInjury ON CmOccupation.strJobNum =
CmInjury.strJobNum)
ON CmCostCentre.lngCostCentre = CmOccupation.lngCostCentre
ORDER BY CmArea.Area, CmInjury.dtmInjury;
--If two or more injuries on same day--I don't care-- this would just give
zero days which would not be the "max day difference" anyway.
--So then how would I use a correlated subquery to go through areas then
datetime and find the max time difference in days between the datetimes for
each area?--Very confusing--Should I do each area separately and union them
together--right now I don't know how to do one of them.
Once I got the Aarea and MostInjuryFreeDays query working, I could tie the
two queries together via Area to display the data.
Any help appreciated, Sandy.
--------------------------------------------
Tom Ellison said:
Dear Sandy:
In order to do this, your query must be able to look at two rows of data
simultaneously - the "current" row it is working on, and a "previous" row.
This is commonly done with a correlated subquery.
I suggest you write a query that gives you the information you want, but
without this InjuryFreeDays column. It must be sorted by the date of injury
column.
I also suggest you should consider what the results should show if there
were two injuries on the same date. Probably such a day would be considered
the same as if only one incident had happened on that date. But, if you
expect the query to tell you what injury happened on that date, and there
were several injuries on that date, what would it then say? The alternative
might be to list each injury on that date and show 0 days between them.
It's best to think this through before writing code.
If you post such a query as I suggested and cover the issue of multiple
injuries on any day, I can probably get you the results you want.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Sandy Eastman said:
Using Access97 on WinNT and Win9*
I have a table of injuries with a date/time stamp field "dtmInjury".
I can get the latest InjuryFreeDays:
=DateDiff("d",Max([dtmInjury]),Now())
...I want to check the # of days between each dtmInjury so I can display
MostInjuryFreeDays so far.
Thanks in advance, Sandy.
