Query for event and outcome.

[PtotheC]

I have a problem that I need to solve in MS Access 2000. I know how to do this in Excel. However this needs to be in Access due to a customer’s requirement.

I have a list of events and what the out come was on a particular date. To give you some idea, the event is basically number given to a day and the outcome is all possible things that “could happen” and that did happen (so not all 160 possible Outcomes are used all the time).

So it is quite possible to have:

Event 1 (This could be “Monday”)
Outcome 12 (This could be “Rain”)

There for as you collect your data over time the “Outcome Diff” would = 12 (for the first time) this is because it happened 12 day into the checks. Then the counter sets to zero, event 2, out come 12 is then 1 as it happened on out come 2, but on Event 13 as it did not rain for a while, is then 11.

The problem comes when I try to create a report to automatically calculate the “Outcome Diff” this in a Query as the “counter” that should have been added at the time this was created was not done at the time. I also do not mind if this has to involve creating to a table, as long as I get the “outcome diff” calculated in MS Access, I do not mind how this is done. I also cannot use date diff as event mean more than just an index for the day.

Event, Outcome, Date, Outcome Diff
1, 12, 14-Feb-95, 1
2, 12, 19-Apr-95, 1
13, 12, 07-Jun-95, 11
18, 12, 08-Jul-95, 5
2, 35, 12-Jul-95, 2
4, 35, 15-Aug-95, 2
7, 35, 12-Sep-95, 3
20, 73, 16-Apr-96, 20
60, 102, 31-Oct-96, 60
16, 105, 16-Nov-96, 16

*This is just a representation with test data.

Just for further clarification, the reason I need this is because, they need to know the difference in days as opposed to an average (an average on 12 outcome’s would 4.5) when really, they need to know that 2/18 days it rained without any day gaps.

[Rabbit]

You'll have to use some subqueries to return what you need.

SELECT x.Event, x.Outcome,iif(x.Outcome = (SELECT TOP 1 Outcome FROM Table1 WHERE EDate < x.EDate ORDER BY EDate Desc), x.Event-nz((SELECT TOP 1 Event FROM Table1 WHERE EDate  < x.EDate ORDER BY EDate Desc),0), x.Event) AS OutcomeDiff
FROM Table1 AS x;
 

[PtotheC]

Thanks ever so much :-)

[Rabbit]

Not a problem, good luck.