By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Query for event and outcome.

P: 3
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.
Jan 14 '08 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 12,355
You'll have to use some subqueries to return what you need.
Expand|Select|Wrap|Line Numbers
  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;
Jan 15 '08 #2

P: 3
Thanks ever so much :-)
Jan 16 '08 #3

Expert Mod 10K+
P: 12,355
Not a problem, good luck.
Jan 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.