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.
3  1607  Rabbit 12,516
Recognized Expert Moderator MVP
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;
-
Rabbit 12,516
Recognized Expert Moderator MVP
Not a problem, good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Együd Csaba |
last post by:
Hi All,
how can I improve the query performance in the following situation:
I have a big (4.5+ million rows) table. One query takes approx. 9 sec to
finish resulting ~10000 rows. But if I run simultaneously 4 similar queries
it takes nearly 5 minutes instead of 4 times 9 seconds or something near of
that.
here is a sample query:
select mertido, fomeazon, ertektipus, mertertek from t_me30 where fomeazon
in (select distinct fomeazon...
|
by: Scott Morford |
last post by:
This is a followup to a question I posed last summer.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=utf-8&selm=3f451dd4%240%241095%247f8943f3%40newsreader.visi.com
I'm developing a weed management database to track our weed management
practices in the eventual hope of using some statistical models on the data
to measure outcome\success. The database contains principally two tables. One
table contains static information such as an...
|
by: Q. John Chen |
last post by:
On a webForm, I have a DropDownList with AutoPostBack=true and I also
have couple textbox with AutoPostBack=false.
I am expect that the DropDownList's SelectedIndexChanged event being
handled after the textboxs' TextChanged event since this is the one
that cause the PostBack.
But I got the DropDownList's event first.
Reading the document, I noticed it only mention all the change event
|
by: Bob Bridges |
last post by:
Start with two tables, parent records in one and child records in the other,
a one-to-many relationship. Create a select statement joining the two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...which is what I wanted.
Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child...
|
by: Photo |
last post by:
Hello everyone this is my first post so as you could guess I need some help. Here is my situation, I have 2 tables, one caled personnel and one called DET_Dates, DET_Dates is linked to Personnel on Admin_ID. The Perrsonnel table has a list of names and dates pertaining to that person, the DET_Date table contains events and dates of events that each person in the Personnel table participated in. What I need to do and have been trying to do is...
| | |
by: theaybaras |
last post by:
Hi all,
I have a table and a subtable .
CAssessments is a combobox that looks up a table called
AssessList has 2 columns, an autonumber PK and a string for the title of the Assessment. When the user meets with a client, they input the valid demographic details and time spent with client, and then in the subtable CAssessments they input any assessments they ran with their client. I need to have a report of the total number of hours...
|
by: Ugo |
last post by:
Hi guys,
until now I make so:
<div id="my_div"><!-- content --></div>
MyClass.prototype.doSomething = function( ) {};
MyClass.prototype.assignFun = function( )
|
by: hedges98 |
last post by:
The thread title probably isn't very clear but I can't think how to word my problem properly. Here goes:
I have a form that has textboxes for users to enter information for first appointments and then a subform (continuous) for them to enter subsequent appointments into (Date and Outcome boxes). After 12 weeks and 6 months (from the first appointment) an appointment is arranged to check progress of the client.
What I need to do is produce...
|
by: SteveGod |
last post by:
Hi,
I'm trying to create a report that will produce automated sets of Committee Minutes for School Appeals, where there are a set range of different outcomes.
The field involved are all from the same table, and called:
, , , and
In the query where the result of the outcome in needed, it has the following in a field:
Minute: MINUTEOUTCOME(,,,,)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
