Re: selecting records BACK from the maximum date

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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" <Zimme@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:632DCC8D-7167-4541-924E-03BA64CEA3EE@xxxxxxxxxxxxxxxx
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" wrote:

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" <Zimme@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:127AF224-7BE0-4DC4-937F-F8511AA2E745@xxxxxxxxxxxxxxxx

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.








.

Relevant Pages