date queries

[Bob Griendling]

I have a simple table of dates over several years and two fields of prices
in dollars for each date. I want to write a query that would look at ONLY
dates that are from the first of the month to the seventh of the month,
compare the two prices, and return to me only those dates where the second
price was higher than the first price by at least $4 at any time between the
first and the seventh of the month, with the calculated greatest value in
the query results.

I haven't been able to write the date query correctly and I don't know how
to get a calculated sum

This may be a job for Excel, but I now little about Excel.

Any help would be appreciated.

Bob

 

[Guest]

Try this --
SELECT Griendling.TransDate, Griendling.Price1, Griendling.Price2,
[Price2]-[Price1] AS Price_Diff
FROM Griendling
WHERE (((Griendling.Price2)>=[Price1]+4) AND ((Day([TransDate]))>=1 And
(Day([TransDate]))<=7))
ORDER BY Format([TransDate],"yyyymm");

 

[John Spencer]

To get the basic data

SELECT TheDateField, PriceOne, PriceTwo
FROM YourTable
WHERE PriceOne + 4 <= PriceTwo
And Day(TheDateField) Between 1 and 7

If you want the greatest Price for each 7 day period that meets your
criteria.

SELECT Format(TheDateField,"yyyy/mm") as YearMonth
, Max(PriceTwo)
FROM YourTable
WHERE PriceOne + 4 <= PriceTwo
And Day(TheDateField) Between 1 and 7
GROUP BY Format(TheDateField,"yyyy/mm")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Bob Griendling]

Thanks for your quick reply, but I've forgotten too much about Access to
make use of your answer. I can't figure out how to input this info in a
query form. Let me go another route.

Thanks again...

Try this --
SELECT Griendling.TransDate, Griendling.Price1, Griendling.Price2,
[Price2]-[Price1] AS Price_Diff
FROM Griendling
WHERE (((Griendling.Price2)>=[Price1]+4) AND ((Day([TransDate]))>=1 And
(Day([TransDate]))<=7))
ORDER BY Format([TransDate],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

I have a simple table of dates over several years and two fields of
prices
in dollars for each date. I want to write a query that would look at
ONLY
dates that are from the first of the month to the seventh of the month,
compare the two prices, and return to me only those dates where the
second
price was higher than the first price by at least $4 at any time between
the
first and the seventh of the month, with the calculated greatest value in
the query results.

I haven't been able to write the date query correctly and I don't know
how
to get a calculated sum

This may be a job for Excel, but I now little about Excel.

Any help would be appreciated.

Bob

 

[Bob Griendling]

Thanks for your quick reply, but I've forgotten too much about Access to
make use of your answer. I can't figure out how to input this info in a
query form. Let me go another route.

Thanks again...

 

[Bob Griendling]

I figured out how to put what you wrote into the query, but now realize that
I didn't describe the query correctly.

I need to know if, during the first 7 days of the month, price2 was $4 or
more than price1 on the first of the month. But you've given me something
to work with, so let me try to figure it out. Thanks again.

Thanks for your quick reply, but I've forgotten too much about Access to
make use of your answer. I can't figure out how to input this info in a
query form. Let me go another route.

Thanks again...

Try this --
SELECT Griendling.TransDate, Griendling.Price1, Griendling.Price2,
[Price2]-[Price1] AS Price_Diff
FROM Griendling
WHERE (((Griendling.Price2)>=[Price1]+4) AND ((Day([TransDate]))>=1 And
(Day([TransDate]))<=7))
ORDER BY Format([TransDate],"yyyymm");

--
KARL DEWEY
Build a little - Test a little

I have a simple table of dates over several years and two fields of
prices
in dollars for each date. I want to write a query that would look at
ONLY
dates that are from the first of the month to the seventh of the month,
compare the two prices, and return to me only those dates where the
second
price was higher than the first price by at least $4 at any time between
the
first and the seventh of the month, with the calculated greatest value
in
the query results.

I haven't been able to write the date query correctly and I don't know
how
to get a calculated sum

This may be a job for Excel, but I now little about Excel.

Any help would be appreciated.

Bob

 

[Bob Griendling]

I figured out how to put what you wrote into the query, but now realize that
I didn't describe the query correctly.

I need to know if, during the first 7 days of the month, price2 was $4 or
more than price1 on the first of the month. But you've given me something
to work with, so let me try to figure it out. Thanks again.

 

[John Spencer]

Query One:
SELECT TheDateField, PriceOne
FROM YourTable
WHERE Day(TheDateField) =1


Query Two:
SELECT Y.TheDateField, Q.PriceOne, Y.PriceTwo
FROM YourTable As Y INNER JOIN QueryOne as Q
ON (Y.TheDateField >= Q.TheDateField)
And (Y.TheDateField <= (Q.TheDateField + 6))
WHERE q.PriceOne + 4 <= PriceTwo


Query Three
SELECT Format(TheDateField,"yyyy/mm") as YearMonth
, Max(PriceTwo)
FROM QueryTwo
GROUP BY Format(TheDateField,"yyyy/mm")

IF your table names and field names are named with just letters, Numbers
and underscore characters this could all be assembled into one query

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================