Date ranges

[Bob Griendling]

My table has only three fields: Date, Price1 and Price 2



I need to know if, during the first 7 days of the month, price2 was $4 more
or less than price1 was on the first of the month.



I developed my query using the wizard and then tweaked it because I couldn't
figure out how the query could make the comparison. The query is of a query
called 1st 7 days. (I had data from all month but wanted to look at only the
1st 7 days).



My first field in the query is:

Date By Month: Format$([1st 7 days].[Date],'yyyy mm') and TOTAL is "Group
by"



2nd field:

Price1 WHERE (Day([Date])=1). It is returning the first date of the month,
only if it is 1. Given that not every month will have a date 1 (Prices are
only Mon.-Fri.), I need to figure out how to make it Date=2 if and only if
there is no Date=1, or Date=3 if and only if there is no Date=1 or 2.



3rd field is Price 1 and TOTAL is "Group by"



4th field is Price 2 WHERE (Day([Date])>=1) ... but it only returns Price2
of Day 1



5th field is Price2 TOTAL is Group by



6th field is Expr: ([Price2]-{Price1]) with criteria >=4 OR <=4



7th field (which I do not understand but was generated by the wizard) is:

Expr2: Year([1st 7 days].[Date])*12+DatePart('m',[1st 7 days].[Date])-1



Any help getting the query to compare the Price1 on Day 1 with all Price2s
on days 1-7 and how to look at dates 2 or 3 when there is no preceding date
that month would be appreciated.



(Thanks to Karl Dewey and John Spencer for getting me this far.)



Bob

 

[Michael Gramelspacher]

I do not really know if this is what you need, but it seems to work. It find
rows where price2_amt is greater than first-day price1_amt by some threshold
amount. Notice that it uses a separate table to furnish the first day of each
month.

CREATE TABLE ProductPrices
(
product_id VARCHAR(10) NOT NULL
,price_date DATETIME NOT NULL
,price1_amt DECIMAL (12,2) NOT NULL
,price2_amt DECIMAL (12,2) NOT NULL
,PRIMARY KEY (product_id, price_date)
);

CREATE TABLE MonthCalendar
(
month_first_date DATETIME NOT NULL
,PRIMARY KEY (month_first_date)
);

Query: First Day Prices
----------------------
SELECT p.product_id,
p.price_date,
p.price1_amt
FROM ProductPrices AS p,
MonthCalendar AS c
WHERE (((p.price_date) = c.month_first_date));

SELECT a.product_id,
a.price_date,
b.price1_amt,
a.price2_amt,
(a.price2_amt - b.price1_amt) AS price_diff
FROM ProductPrices AS a,
[First Day Prices] AS b
WHERE YEAR(a.price_date) = YEAR(b.price_date)
AND MONTH(a.price_date) = MONTH(b.price_date)
AND a.product_id = b.product_id
GROUP BY a.product_id,a.price_date,b.price1_amt,a.price2_amt
HAVING 3 < (a.price2_amt - b.price1_amt);

 

[Guest]

Try these two queries ---
[Griendling-1] ---
SELECT Day([TransDate]) AS Expr1, Table1.Price1
FROM Table1
WHERE (((Day([TransDate]))=1));

SELECT Day([TransDate]) AS [Day of Month], [Griendling-1].Price1,
Table1.Price2, Abs([Griendling-1].[Price1]-[Price2]) AS Difference
FROM Table1, [Griendling-1]
WHERE (((Day([TransDate]))<=7) AND
((Abs([Griendling-1].[Price1]-[Price2]))>=4));

 

[Bob Griendling]

Thanks, Michael, I'll give it a try. Parenthetically, can you recommend a
book on Access?

Bob

I do not really know if this is what you need, but it seems to work. It
find
rows where price2_amt is greater than first-day price1_amt by some
threshold
amount. Notice that it uses a separate table to furnish the first day of
each
month.

CREATE TABLE ProductPrices
(
product_id VARCHAR(10) NOT NULL
,price_date DATETIME NOT NULL
,price1_amt DECIMAL (12,2) NOT NULL
,price2_amt DECIMAL (12,2) NOT NULL
,PRIMARY KEY (product_id, price_date)
);

CREATE TABLE MonthCalendar
(
month_first_date DATETIME NOT NULL
,PRIMARY KEY (month_first_date)
);

Query: First Day Prices
----------------------
SELECT p.product_id,
p.price_date,
p.price1_amt
FROM ProductPrices AS p,
MonthCalendar AS c
WHERE (((p.price_date) = c.month_first_date));

SELECT a.product_id,
a.price_date,
b.price1_amt,
a.price2_amt,
(a.price2_amt - b.price1_amt) AS price_diff
FROM ProductPrices AS a,
[First Day Prices] AS b
WHERE YEAR(a.price_date) = YEAR(b.price_date)
AND MONTH(a.price_date) = MONTH(b.price_date)
AND a.product_id = b.product_id
GROUP BY a.product_id,a.price_date,b.price1_amt,a.price2_amt
HAVING 3 < (a.price2_amt - b.price1_amt);

My table has only three fields: Date, Price1 and Price 2



I need to know if, during the first 7 days of the month, price2 was $4
more
or less than price1 was on the first of the month.



I developed my query using the wizard and then tweaked it because I
couldn't
figure out how the query could make the comparison. The query is of a
query
called 1st 7 days. (I had data from all month but wanted to look at only
the
1st 7 days).



My first field in the query is:

Date By Month: Format$([1st 7 days].[Date],'yyyy mm') and TOTAL is "Group
by"



2nd field:

Price1 WHERE (Day([Date])=1). It is returning the first date of the
month,
only if it is 1. Given that not every month will have a date 1 (Prices
are
only Mon.-Fri.), I need to figure out how to make it Date=2 if and only if
there is no Date=1, or Date=3 if and only if there is no Date=1 or 2.



3rd field is Price 1 and TOTAL is "Group by"



4th field is Price 2 WHERE (Day([Date])>=1) ... but it only returns Price2
of Day 1



5th field is Price2 TOTAL is Group by



6th field is Expr: ([Price2]-{Price1]) with criteria >=4 OR <=4



7th field (which I do not understand but was generated by the wizard) is:

Expr2: Year([1st 7 days].[Date])*12+DatePart('m',[1st 7 days].[Date])-1



Any help getting the query to compare the Price1 on Day 1 with all Price2s
on days 1-7 and how to look at dates 2 or 3 when there is no preceding
date
that month would be appreciated.



(Thanks to Karl Dewey and John Spencer for getting me this far.)



Bob

 

[Bob Griendling]

Thanks, Karl, I'll give it a try. Can you recommend a book on Access?

Bob

Try these two queries ---
[Griendling-1] ---
SELECT Day([TransDate]) AS Expr1, Table1.Price1
FROM Table1
WHERE (((Day([TransDate]))=1));

SELECT Day([TransDate]) AS [Day of Month], [Griendling-1].Price1,
Table1.Price2, Abs([Griendling-1].[Price1]-[Price2]) AS Difference
FROM Table1, [Griendling-1]
WHERE (((Day([TransDate]))<=7) AND
((Abs([Griendling-1].[Price1]-[Price2]))>=4));

--
KARL DEWEY
Build a little - Test a little

My table has only three fields: Date, Price1 and Price 2



I need to know if, during the first 7 days of the month, price2 was $4
more
or less than price1 was on the first of the month.



I developed my query using the wizard and then tweaked it because I
couldn't
figure out how the query could make the comparison. The query is of a
query
called 1st 7 days. (I had data from all month but wanted to look at only
the
1st 7 days).



My first field in the query is:

Date By Month: Format$([1st 7 days].[Date],'yyyy mm') and TOTAL is "Group
by"



2nd field:

Price1 WHERE (Day([Date])=1). It is returning the first date of the
month,
only if it is 1. Given that not every month will have a date 1 (Prices
are
only Mon.-Fri.), I need to figure out how to make it Date=2 if and only
if
there is no Date=1, or Date=3 if and only if there is no Date=1 or 2.



3rd field is Price 1 and TOTAL is "Group by"



4th field is Price 2 WHERE (Day([Date])>=1) ... but it only returns
Price2
of Day 1



5th field is Price2 TOTAL is Group by



6th field is Expr: ([Price2]-{Price1]) with criteria >=4 OR <=4



7th field (which I do not understand but was generated by the wizard) is:

Expr2: Year([1st 7 days].[Date])*12+DatePart('m',[1st 7 days].[Date])-1



Any help getting the query to compare the Price1 on Day 1 with all
Price2s
on days 1-7 and how to look at dates 2 or 3 when there is no preceding
date
that month would be appreciated.



(Thanks to Karl Dewey and John Spencer for getting me this far.)



Bob

 

[Michael Gramelspacher]

For Access I would go with Microsoft Office Access 2003 Inside Out by John L.
Viescas.
For SQL it is SQL Queries for Mere Mortals by Michael J. Hernandez and John L.
Viescas