Output query based on data field.

[bknight]

I have a table indexed and has dates with data. What I would like to do is
search for a minor difference between a date and the next date and output x
number of days AFTER the day the minor difference was found. ie:
Date Diff
5/1/2009 +50
5/4/2009 -25
5/5/2009 +04 This would be my definition of minor change
5/6/2009 Output data fields for this date
5/7/2009 Output data fields for this date
5/8/2009 Output data fields for this date
5/11/2009 Output data fields for this date
5/12/2009 Output data fields for this date
End output UNTIL the next minor change found (Note the minor change could be
found in a previous output, which would extend the outputs x days)

 

[bknight]

I forgot to mention that the difference is a value determined from the
difference between a field's value and the previous days filed value. Makes
it more complicated.

 

[John Spencer]

You could write a query to give you the change date that looks like the
following

SELECT T2.Date+1 as StartDate, T2.Date+5 as EndDate
FROM Table as T INNER JOIN Table as T2
ON T1.Date = (T2.Date-1)
WHERE Abs(T1.Value-T2.Value) < 4


So for your overall query, you could use:

SELECT *
FROM Table INNER JOIN SavedQuery
On Table.Date >= SavedQuery.StartDate
and Table.Date <=SavedQuery.EndDate

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[bknight]

Before I start making the queries(?) I'm assuming your first statement would
be "SavedQuery"?

 

[John Spencer]

Yes the first query would be saved and I referred to it as SavedQuery.
Sorry about the brevity of my response.

In theory, you should be able to use the a subquery in the from clause
of the second query. The subquery would be the first query I posted.

I say in theory, because your table and field names would have to
conform to the naming convention of only letters, numbers, and the
underscore character. And they should avoid reserved words (such as Date).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[bknight]

The only filed that has any resemblance would be Day, referring to the date.

 

[John Spencer]

Then this MIGHT work

SELECT YourTableName.*
FROM YourTableName INNER JOIN
(SELECT T2.Day+1 as StartDate, T2.Day+5 as EndDate
FROM YourTableName as T INNER JOIN YourTableName as T2
ON T1.Day = (T2.Day-1)
WHERE Abs(T1.Value-T2.Value) < 4
) as TheDates
On YourTableName.Day >= TheDates.StartDate
and YourTableName.Day <=TheDates.EndDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[bknight]

I have been busy with other similar small tasks preventing me from wrting
this into the Db. One of the tasks was to duplicate the data and perform
similar but diffent calculations on the data. To prevent errors and make
life easier I attempted to write a append query in the second Db to add each
days data from the first. I added a link to the first Db in the second and
then ATTEMPTED the query:

INSERT INTO tblMcClellan ( Day, UStk, DStk, UVol, DVol, Trin, Diff,
CumAdvDec, Ten_Pct, Five_Pct, OSC, SUM )
SELECT tblMcClellan1.Day, tblMcClellan1.UStk, tblMcClellan1.DStk,
tblMcClellan1.UVol, tblMcClellan1.DVol, tblMcClellan1.Trin,
tblMcClellan1.Diff, tblMcClellan1.CumAdvDec, tblMcClellan1.Ten_Pct,
tblMcClellan1.Five_Pct, tblMcClellan1.OSC, tblMcClellan1.SUM
FROM tblMcClellan1 INNER JOIN tblMcClellan ON tblMcClellan1.INDEX =
tblMcClellan.INDEX
WHERE (([tblMcClellan1]![Day]>[tblMcClellan]![Day]));

Adding only the days data that isn't yet into the second. Didn't work
appended no records. So I manually did the task trying to figure out why it
won't work

The link is only temporary as I'm trying to determine which set of
calculations are more defining. The codes awould have to be changed to the
linked table and I didn't want to do that.

Can you see why this doesn't work?