Unusual Query – advice needed please

[PtotheC]

I have a list of events and what the out come was on a particular day or 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”)

Therefore as you collect your data over time the “Outcome Diff” would = 12

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
12, 1, 14-Feb-95, 12 (0 to 12 = 12) i.e this is the 1st event outcome of 1
22, 1, 19-Apr-95, 10 (12 to 22 = 10) i.e this is the 2nd event outcome of 1
29, 4, 07-Jun-95, 29 (0 to 29 = 29) i.e this is the 1st event outcome of 29
34, Ect...
*This is just a representation with test data.

Just for further clarification: the reason this is need this is to find out difference in "event" days as opposed to just an average.

 

[PtotheC]

Finally, I managed to do this after much determination.

Basically by use of one huge Macro and 4 Append Queries:

Macro1 –
a) Crates a List to sort the data in to an acceptable format and remove bad data.
b) Index all this data (and then have the macro copy this and add 1 to the Index)
c) Query the Index v the New Index (created in b)
d) Query the results (of c) and subtract event 2 from event 1 (this will unfortunately remove the first event). You will need one for each outcome possible.
e) Query the max event and outcome and append d.
f) Use once last Query to “Glue” this together in one table.

I also included “Delete Object” and “Copy Object” to save time and duplication.

Thanks to anyone who had a look at this.

PS I have alos been given the lead of:

1.
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
2.
FROM Table1 AS x;