selecting records BACK from the maximum date

G

Guest

Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master database
records by selecting the records by (x) number of days from the maximum date.
I reviewed the query date discussions here and come up with the following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But instead
of seeing the “7†days, I got the whole database dates. What did I do wrong?
Thanks again for your reviewing.
 
T

Tom Ellison

Dear Zimme:

You told the query to display the ProductionDay and you grouped on that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison
 
G

Guest

Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the objective
and then have it grouped. I am still pretty green with query.

Best regards.


Tom Ellison said:
Dear Zimme:

You told the query to display the ProductionDay and you grouped on that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison


Zimme said:
Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master database
records by selecting the records by (x) number of days from the maximum
date.
I reviewed the query date discussions here and come up with the following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Zimme:

I don't know what your data looks like. What is TheHr?

Here's a simple change that may help:

SELECT TheHr, Sum(Volume)
FROM qryOper
WHERE Weekday(productionday, 2) < 6
GROUP BY TheHr
HAVING ProductionDay BETWEEN DateAdd("d", -7, Max(ProductionDay) And
Max(ProductionDay)

Best would be for you to show me some sample data, and the results you
expect.

Tom Ellison


Zimme said:
Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the objective
and then have it grouped. I am still pretty green with query.

Best regards.


Tom Ellison said:
Dear Zimme:

You told the query to display the ProductionDay and you grouped on that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison


Zimme said:
Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master
database
records by selecting the records by (x) number of days from the maximum
date.
I reviewed the query date discussions here and come up with the
following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dear Tom:

TheHr is the hour of the day on the hour. I need to group/total the volume
within the date range selected; by production day, the hours. Sampled as
below:

Original
ProductionDay TheHr Volume PeriodStart
02-Jan-06 8 127 08:39:42
02-Jan-06 16 0 16:09:32
03-Jan-06 10 431 10:42:09
03-Jan-06 10 1578 10:46:50
04-Jan-06 12 0 12:55:04
05-Jan-06 16 0 16:18:39
05-Jan-06 16 88591 16:27:00
05-Jan-06 23 3259 23:40:00
05-Jan-06 23 2375 23:45:00

Expectation after totalling the Volume
ProductionDay TheHr Volume PeriodStart
02-Jan-06 8 127 08:39:42
02-Jan-06 16 0 16:09:32
03-Jan-06 10 2009 10:46:50
04-Jan-06 12 0 12:55:04
05-Jan-06 16 88591 16:27:00
05-Jan-06 23 5634 23:45:00

I tried your last suggested query and Access came back with the following
message "You tried to execute a query that does not include the specified
expression "(qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])" as part of an
aggregate function."

How do I include the expression and having the specified period returned
with the ProductionDay, theHr, sum of Volume fields?

Thanks you for assisting.


Tom Ellison said:
Dear Zimme:

I don't know what your data looks like. What is TheHr?

Here's a simple change that may help:

SELECT TheHr, Sum(Volume)
FROM qryOper
WHERE Weekday(productionday, 2) < 6
GROUP BY TheHr
HAVING ProductionDay BETWEEN DateAdd("d", -7, Max(ProductionDay) And
Max(ProductionDay)

Best would be for you to show me some sample data, and the results you
expect.

Tom Ellison


Zimme said:
Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the objective
and then have it grouped. I am still pretty green with query.

Best regards.


Tom Ellison said:
Dear Zimme:

You told the query to display the ProductionDay and you grouped on that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison



Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master
database
records by selecting the records by (x) number of days from the maximum
date.
I reviewed the query date discussions here and come up with the
following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Zimme:

I believe I see the problem. In your HAVING clause, you used
MAX(productiondate). However, your query is GROUPed by procudtiondate.
Now, for each productiondate, the maximum productiondate is that
productiondate. The filtering effect of this HAVING clause would be nothing
at all.

Likely, you intend the MAX(productiondate) to be the greatest date in the
entire table. Is that correct? If so, it must be derived independently
with a subquery, not correlated with the main query.

SELECT ProductionDay, TheHr, Sum(Volume), MAX(PeriodStart)
FROM qryOper
WHERE Weekday([productionday], 2) < 6
GROUP BY ProductionDay, TheHr
HAVING ProductionDay BETWEEN
DateAdd("d", -7, (SELECT Max(ProductionDay) FROM qryOper))
AND (SELECT MAX(ProductionDay) FROM qryOper)

Please let me know if this helped. Sorry I didn't see it sooner.

Tom Ellison


Zimme said:
Dear Tom:

TheHr is the hour of the day on the hour. I need to group/total the volume
within the date range selected; by production day, the hours. Sampled as
below:

Original
ProductionDay TheHr Volume PeriodStart
02-Jan-06 8 127 08:39:42
02-Jan-06 16 0 16:09:32
03-Jan-06 10 431 10:42:09
03-Jan-06 10 1578 10:46:50
04-Jan-06 12 0 12:55:04
05-Jan-06 16 0 16:18:39
05-Jan-06 16 88591 16:27:00
05-Jan-06 23 3259 23:40:00
05-Jan-06 23 2375 23:45:00

Expectation after totalling the Volume
ProductionDay TheHr Volume PeriodStart
02-Jan-06 8 127 08:39:42
02-Jan-06 16 0 16:09:32
03-Jan-06 10 2009 10:46:50
04-Jan-06 12 0 12:55:04
05-Jan-06 16 88591 16:27:00
05-Jan-06 23 5634 23:45:00

I tried your last suggested query and Access came back with the following
message "You tried to execute a query that does not include the specified
expression "(qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])" as part of
an
aggregate function."

How do I include the expression and having the specified period returned
with the ProductionDay, theHr, sum of Volume fields?

Thanks you for assisting.


Tom Ellison said:
Dear Zimme:

I don't know what your data looks like. What is TheHr?

Here's a simple change that may help:

SELECT TheHr, Sum(Volume)
FROM qryOper
WHERE Weekday(productionday, 2) < 6
GROUP BY TheHr
HAVING ProductionDay BETWEEN DateAdd("d", -7, Max(ProductionDay) And
Max(ProductionDay)

Best would be for you to show me some sample data, and the results you
expect.

Tom Ellison


Zimme said:
Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the
objective
and then have it grouped. I am still pretty green with query.

Best regards.


:

Dear Zimme:

You told the query to display the ProductionDay and you grouped on
that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison



Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master
database
records by selecting the records by (x) number of days from the
maximum
date.
I reviewed the query date discussions here and come up with the
following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I
do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dear Tom:

After my last message, I tried again. This time, I got it resolved.
Basically, I put another "select" within the "where" as followed:

SELECT ... FROM qryOper
WHERE qryOper.ProductionDay>(select Max([ProDuctionDay]) from qryoper)-7)
GROUP BY qryOper.ProductionDay, qryOper.TheHr, Weekday([ProductionDay],2);

Thanks for helping. Cheers.


Tom Ellison said:
Dear Zimme:

I don't know what your data looks like. What is TheHr?

Here's a simple change that may help:

SELECT TheHr, Sum(Volume)
FROM qryOper
WHERE Weekday(productionday, 2) < 6
GROUP BY TheHr
HAVING ProductionDay BETWEEN DateAdd("d", -7, Max(ProductionDay) And
Max(ProductionDay)

Best would be for you to show me some sample data, and the results you
expect.

Tom Ellison


Zimme said:
Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the objective
and then have it grouped. I am still pretty green with query.

Best regards.


Tom Ellison said:
Dear Zimme:

You told the query to display the ProductionDay and you grouped on that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison



Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master
database
records by selecting the records by (x) number of days from the maximum
date.
I reviewed the query date discussions here and come up with the
following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dear Tom:

Thank you kindly for your explanation. I always get to learn more from it.
Instead of just happened to get it or just fill in the the formula, your
explanation really provide an intelletual backbone.

Best wishes.

Zimme


Tom Ellison said:
Dear Zimme:

I believe I see the problem. In your HAVING clause, you used
MAX(productiondate). However, your query is GROUPed by procudtiondate.
Now, for each productiondate, the maximum productiondate is that
productiondate. The filtering effect of this HAVING clause would be nothing
at all.

Likely, you intend the MAX(productiondate) to be the greatest date in the
entire table. Is that correct? If so, it must be derived independently
with a subquery, not correlated with the main query.

SELECT ProductionDay, TheHr, Sum(Volume), MAX(PeriodStart)
FROM qryOper
WHERE Weekday([productionday], 2) < 6
GROUP BY ProductionDay, TheHr
HAVING ProductionDay BETWEEN
DateAdd("d", -7, (SELECT Max(ProductionDay) FROM qryOper))
AND (SELECT MAX(ProductionDay) FROM qryOper)

Please let me know if this helped. Sorry I didn't see it sooner.

Tom Ellison


Zimme said:
Dear Tom:

TheHr is the hour of the day on the hour. I need to group/total the volume
within the date range selected; by production day, the hours. Sampled as
below:

Original
ProductionDay TheHr Volume PeriodStart
02-Jan-06 8 127 08:39:42
02-Jan-06 16 0 16:09:32
03-Jan-06 10 431 10:42:09
03-Jan-06 10 1578 10:46:50
04-Jan-06 12 0 12:55:04
05-Jan-06 16 0 16:18:39
05-Jan-06 16 88591 16:27:00
05-Jan-06 23 3259 23:40:00
05-Jan-06 23 2375 23:45:00

Expectation after totalling the Volume
ProductionDay TheHr Volume PeriodStart
02-Jan-06 8 127 08:39:42
02-Jan-06 16 0 16:09:32
03-Jan-06 10 2009 10:46:50
04-Jan-06 12 0 12:55:04
05-Jan-06 16 88591 16:27:00
05-Jan-06 23 5634 23:45:00

I tried your last suggested query and Access came back with the following
message "You tried to execute a query that does not include the specified
expression "(qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])" as part of
an
aggregate function."

How do I include the expression and having the specified period returned
with the ProductionDay, theHr, sum of Volume fields?

Thanks you for assisting.


Tom Ellison said:
Dear Zimme:

I don't know what your data looks like. What is TheHr?

Here's a simple change that may help:

SELECT TheHr, Sum(Volume)
FROM qryOper
WHERE Weekday(productionday, 2) < 6
GROUP BY TheHr
HAVING ProductionDay BETWEEN DateAdd("d", -7, Max(ProductionDay) And
Max(ProductionDay)

Best would be for you to show me some sample data, and the results you
expect.

Tom Ellison


Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the
objective
and then have it grouped. I am still pretty green with query.

Best regards.


:

Dear Zimme:

You told the query to display the ProductionDay and you grouped on
that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison



Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master
database
records by selecting the records by (x) number of days from the
maximum
date.
I reviewed the query date discussions here and come up with the
following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I
do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Zimme:

Yup, just what I posted half an hour before. You got it!

Tom Ellison


Zimme said:
Dear Tom:

After my last message, I tried again. This time, I got it resolved.
Basically, I put another "select" within the "where" as followed:

SELECT ... FROM qryOper
WHERE qryOper.ProductionDay>(select Max([ProDuctionDay]) from qryoper)-7)
GROUP BY qryOper.ProductionDay, qryOper.TheHr, Weekday([ProductionDay],2);

Thanks for helping. Cheers.


Tom Ellison said:
Dear Zimme:

I don't know what your data looks like. What is TheHr?

Here's a simple change that may help:

SELECT TheHr, Sum(Volume)
FROM qryOper
WHERE Weekday(productionday, 2) < 6
GROUP BY TheHr
HAVING ProductionDay BETWEEN DateAdd("d", -7, Max(ProductionDay) And
Max(ProductionDay)

Best would be for you to show me some sample data, and the results you
expect.

Tom Ellison


Zimme said:
Dear Tom:

Thank you for your explaination. I tried a number of switches,
unsuccessfully.

May I trouble you again for a query statement of writing out the
objective
and then have it grouped. I am still pretty green with query.

Best regards.


:

Dear Zimme:

You told the query to display the ProductionDay and you grouped on
that.

Replace that with the thing you actually want to see, and group on it.

Tom Ellison



Hello:

Best wishes and thank you all for your previous assistance.

I have another situation where I would like to filter the master
database
records by selecting the records by (x) number of days from the
maximum
date.
I reviewed the query date discussions here and come up with the
following:

SELECT qryOper.ProductionDay, qryOper.TheHr, Sum(qryOper.Volume)
FROM qryOper
WHERE (((Weekday([productionday],2))<6))
GROUP BY qryOper.ProductionDay, qryOper.TheHr
HAVING (((qryOper.ProductionDay) Between
DateAdd("d",-7,Max([ProductionDay])) And Max([ProductionDay])));

a sample query for 7 days back from the maximum production day. But
instead
of seeing the "7" days, I got the whole database dates. What did I
do
wrong?
Thanks again for your reviewing.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Holidays and Records count 3
Filtering records by date 2
How do I select the maximum of a subset of records? 3
How to ask user to criteria 3
Selecting one of duplicate records with a maximum value. 5
Selecting from a database where the joins have history 1
Sum Only Subset Records Included in Max Sale Date 5
The query cannot be completed. 1

Top