Running Month to date query

D

DIH

Hi all,

I have a query that gives me a month to date efficiency %.
The user selects a date from a control called, "cboDate" in a form
called, "frmDailyProdReport". The query will use that date as the end
date and the 1st of the month (that it's in) as the start date. This
obviously returns just a single value. What I would like to do is get a
running month to date for each day so you could see the month to date
for the 1st, the 2nd, the 3rd etc. up to the date selected in the
control. Below is the SQL for the original (single value)query.

SELECT Sum([DAILY SHIFT]!ACTUAL)/Sum([DAILY SHIFT]!THEORETICAL) AS
Efficiency, [DAILY SHIFT].DATEofPROD
FROM [DAILY SHIFT]
GROUP BY [DAILY SHIFT].DATEofPROD
HAVING ((([DAILY SHIFT].DATEofPROD) Between
DateAdd("d",1-DatePart("d",[forms]![frmDailyProdReport]![cboDate]),[forms]![frmDailyProdReport]![cboDate])
And [forms]![frmDailyProdReport]![cboDate]));

Any help would be greatly appreciated.
 
J

Jeff Boyce

To "calculate" the date one month ago, you could use the DateAdd() function,
or you could use the DateSerial() function. Either way, it sounds like you
are trying to find all rows that fall within the last month from Date()
(today's date). Take a look at:

Between <your earlier date> And Date()

as a selection criterion.

Then, to get the average, use a Totals query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
D

DIH

Jeff said:
To "calculate" the date one month ago, you could use the DateAdd() function,
or you could use the DateSerial() function. Either way, it sounds like you
are trying to find all rows that fall within the last month from Date()
(today's date). Take a look at:

Between <your earlier date> And Date()

as a selection criterion.

Then, to get the average, use a Totals query.
Click to expand...
Thanks for replying.

To be clear here is how I want the results to look
(If for example 11/15/07 was the date selected in the control):

11/1/07 50.2% (month to date as of the 1st)
11/2/07 50.7% (month to date as of the 2nd)
11/3/07 50.8% (month to date as of the 3rd)
11/4/07 50.9% (month to date as of the 4th)
11/5/07 50.9% (month to date as of the 5th)
..
..
..
etc... (thru 11/15/07).
 
K

Ken Sheridan

You'll need to use a subquery correlated with the outer query on the date of
production column to return the efficiency up to and including the date of
production in each row returned by the outer query. Try this:

PARAMETERS [Forms]![frmDailyProdReport]![cboDate] DATETIME;
SELECT DateOfProd,
(SELECT FORMAT(SUM(Actual)/SUM(Theoretical),"Percent")
FROM [Daily Shift] AS DS2
WHERE YEAR(DS2.DateOfProd) = YEAR(DS1.DateOfProd)
AND MONTH(DS2.DateOfProd) = MONTH(DS1.DateOfProd)
AND DAY(DS2.DateOfProd) <= DAY(DS1.DateOfProd))
AS [Month to date efficiency]
FROM [Daily Shift] AS DS1
WHERE DateOfProd BETWEEN
DATESERIAL(YEAR([Forms]![frmDailyProdReport]![cboDate]),
MONTH([Forms]![frmDailyProdReport]![cboDate]),1)
AND [Forms]![frmDailyProdReport]![cboDate]
ORDER BY DateOfProd;

Note that its always best to declare date/time parameters as a value in
short date format might otherwise be interpreted as an arithmetical
expression and give a false result.

If the query is being used as the RecordSource of a report omit the ORDER BY
clause and use the report's internal sorting and grouping mechanism to order
the results by date.

Ken Sheridan
Stafford, England
 
D

DIH

Ken said:
You'll need to use a subquery correlated with the outer query on the date of
production column to return the efficiency up to and including the date of
production in each row returned by the outer query. Try this:

PARAMETERS [Forms]![frmDailyProdReport]![cboDate] DATETIME;
SELECT DateOfProd,
(SELECT FORMAT(SUM(Actual)/SUM(Theoretical),"Percent")
FROM [Daily Shift] AS DS2
WHERE YEAR(DS2.DateOfProd) = YEAR(DS1.DateOfProd)
AND MONTH(DS2.DateOfProd) = MONTH(DS1.DateOfProd)
AND DAY(DS2.DateOfProd) <= DAY(DS1.DateOfProd))
AS [Month to date efficiency]
FROM [Daily Shift] AS DS1
WHERE DateOfProd BETWEEN
DATESERIAL(YEAR([Forms]![frmDailyProdReport]![cboDate]),
MONTH([Forms]![frmDailyProdReport]![cboDate]),1)
AND [Forms]![frmDailyProdReport]![cboDate]
ORDER BY DateOfProd;

Note that its always best to declare date/time parameters as a value in
short date format might otherwise be interpreted as an arithmetical
expression and give a false result.

If the query is being used as the RecordSource of a report omit the ORDER BY
clause and use the report's internal sorting and grouping mechanism to order
the results by date.

Ken Sheridan
Stafford, England

DIH said:
Thanks for replying.

To be clear here is how I want the results to look
(If for example 11/15/07 was the date selected in the control):

11/1/07 50.2% (month to date as of the 1st)
11/2/07 50.7% (month to date as of the 2nd)
11/3/07 50.8% (month to date as of the 3rd)
11/4/07 50.9% (month to date as of the 4th)
11/5/07 50.9% (month to date as of the 5th)
..
..
..
etc... (thru 11/15/07).
Click to expand...
Click to expand...
Thanks Ken,

I think there is something I'm missing or don't understand.
When I run your sql, I get the correct results for each day but
it is returning the results approx 42 times for each day (not just once).
The [DAILY SHIFT] table has many records for each day. It basically
holds a converting machine lines daily figures. Structure is something
like this:

DATEofProd Line THEO ACTUAL Downtime SchedMin

11/1/07 1BT 1500 790 120 480
11/1/07 2BT 1700 950 90 480
11/1/07 3BT 1575 850 135 480
11/2/07 1BT 1500 820 120 480
11/2/07 2BT 1700 792 90 480
11/2/07 3BT 1575 813 135 480

etc...

The query would sum all the "ACTUAL" and divide it by the sum
of all the "THEO" to return one efficiency % value for each day (of
course in the running month to date fashion that I mentioned in my first
post).

I'm still a bit new to all of this so I not quite sure what you mean
when you say, "use a subquery correlated with the outer query". Do I
need to have two queries?

I'm sure you are on the right track (and can nail this) since the values
that get returned are correct (just need to get the query to only return
it once for each day).

Thanks again for your help!
 
K

Ken Sheridan

I'd assumed there was one row per day. Use SELECT DISTINCT, that should do it:

PARAMETERS [Forms]![frmDailyProdReport]![cboDate] DATETIME;
SELECT DISTINCT DateOfProd,
(SELECT FORMAT(SUM(Actual)/SUM(Theoretical),"Percent")
etc.

Ken Sheridan
Stafford, England

DIH said:
Ken said:
You'll need to use a subquery correlated with the outer query on the date of
production column to return the efficiency up to and including the date of
production in each row returned by the outer query. Try this:

PARAMETERS [Forms]![frmDailyProdReport]![cboDate] DATETIME;
SELECT DateOfProd,
(SELECT FORMAT(SUM(Actual)/SUM(Theoretical),"Percent")
FROM [Daily Shift] AS DS2
WHERE YEAR(DS2.DateOfProd) = YEAR(DS1.DateOfProd)
AND MONTH(DS2.DateOfProd) = MONTH(DS1.DateOfProd)
AND DAY(DS2.DateOfProd) <= DAY(DS1.DateOfProd))
AS [Month to date efficiency]
FROM [Daily Shift] AS DS1
WHERE DateOfProd BETWEEN
DATESERIAL(YEAR([Forms]![frmDailyProdReport]![cboDate]),
MONTH([Forms]![frmDailyProdReport]![cboDate]),1)
AND [Forms]![frmDailyProdReport]![cboDate]
ORDER BY DateOfProd;

Note that its always best to declare date/time parameters as a value in
short date format might otherwise be interpreted as an arithmetical
expression and give a false result.

If the query is being used as the RecordSource of a report omit the ORDER BY
clause and use the report's internal sorting and grouping mechanism to order
the results by date.

Ken Sheridan
Stafford, England

DIH said:
Jeff Boyce wrote:
To "calculate" the date one month ago, you could use the DateAdd() function,
or you could use the DateSerial() function. Either way, it sounds like you
are trying to find all rows that fall within the last month from Date()
(today's date). Take a look at:

Between <your earlier date> And Date()

as a selection criterion.

Then, to get the average, use a Totals query.

Thanks for replying.

To be clear here is how I want the results to look
(If for example 11/15/07 was the date selected in the control):

11/1/07 50.2% (month to date as of the 1st)
11/2/07 50.7% (month to date as of the 2nd)
11/3/07 50.8% (month to date as of the 3rd)
11/4/07 50.9% (month to date as of the 4th)
11/5/07 50.9% (month to date as of the 5th)
..
..
..
etc... (thru 11/15/07).
Click to expand...
Click to expand...
Thanks Ken,

I think there is something I'm missing or don't understand.
When I run your sql, I get the correct results for each day but
it is returning the results approx 42 times for each day (not just once).
The [DAILY SHIFT] table has many records for each day. It basically
holds a converting machine lines daily figures. Structure is something
like this:

DATEofProd Line THEO ACTUAL Downtime SchedMin

11/1/07 1BT 1500 790 120 480
11/1/07 2BT 1700 950 90 480
11/1/07 3BT 1575 850 135 480
11/2/07 1BT 1500 820 120 480
11/2/07 2BT 1700 792 90 480
11/2/07 3BT 1575 813 135 480

etc...

The query would sum all the "ACTUAL" and divide it by the sum
of all the "THEO" to return one efficiency % value for each day (of
course in the running month to date fashion that I mentioned in my first
post).

I'm still a bit new to all of this so I not quite sure what you mean
when you say, "use a subquery correlated with the outer query". Do I
need to have two queries?

I'm sure you are on the right track (and can nail this) since the values
that get returned are correct (just need to get the query to only return
it once for each day).

Thanks again for your help!
Click to expand...
 
D

DIH

Ken said:
I'd assumed there was one row per day. Use SELECT DISTINCT, that should do it:

PARAMETERS [Forms]![frmDailyProdReport]![cboDate] DATETIME;
SELECT DISTINCT DateOfProd,
(SELECT FORMAT(SUM(Actual)/SUM(Theoretical),"Percent")
etc.

Ken Sheridan
Stafford, England

DIH said:
Ken said:
You'll need to use a subquery correlated with the outer query on the date of
production column to return the efficiency up to and including the date of
production in each row returned by the outer query. Try this:

PARAMETERS [Forms]![frmDailyProdReport]![cboDate] DATETIME;
SELECT DateOfProd,
(SELECT FORMAT(SUM(Actual)/SUM(Theoretical),"Percent")
FROM [Daily Shift] AS DS2
WHERE YEAR(DS2.DateOfProd) = YEAR(DS1.DateOfProd)
AND MONTH(DS2.DateOfProd) = MONTH(DS1.DateOfProd)
AND DAY(DS2.DateOfProd) <= DAY(DS1.DateOfProd))
AS [Month to date efficiency]
FROM [Daily Shift] AS DS1
WHERE DateOfProd BETWEEN
DATESERIAL(YEAR([Forms]![frmDailyProdReport]![cboDate]),
MONTH([Forms]![frmDailyProdReport]![cboDate]),1)
AND [Forms]![frmDailyProdReport]![cboDate]
ORDER BY DateOfProd;

Note that its always best to declare date/time parameters as a value in
short date format might otherwise be interpreted as an arithmetical
expression and give a false result.

If the query is being used as the RecordSource of a report omit the ORDER BY
clause and use the report's internal sorting and grouping mechanism to order
the results by date.

Ken Sheridan
Stafford, England

:

Jeff Boyce wrote:
To "calculate" the date one month ago, you could use the DateAdd() function,
or you could use the DateSerial() function. Either way, it sounds like you
are trying to find all rows that fall within the last month from Date()
(today's date). Take a look at:

Between <your earlier date> And Date()

as a selection criterion.

Then, to get the average, use a Totals query.

Thanks for replying.

To be clear here is how I want the results to look
(If for example 11/15/07 was the date selected in the control):

11/1/07 50.2% (month to date as of the 1st)
11/2/07 50.7% (month to date as of the 2nd)
11/3/07 50.8% (month to date as of the 3rd)
11/4/07 50.9% (month to date as of the 4th)
11/5/07 50.9% (month to date as of the 5th)
..
..
..
etc... (thru 11/15/07).
Click to expand...
Thanks Ken,

I think there is something I'm missing or don't understand.
When I run your sql, I get the correct results for each day but
it is returning the results approx 42 times for each day (not just once).
The [DAILY SHIFT] table has many records for each day. It basically
holds a converting machine lines daily figures. Structure is something
like this:

DATEofProd Line THEO ACTUAL Downtime SchedMin

11/1/07 1BT 1500 790 120 480
11/1/07 2BT 1700 950 90 480
11/1/07 3BT 1575 850 135 480
11/2/07 1BT 1500 820 120 480
11/2/07 2BT 1700 792 90 480
11/2/07 3BT 1575 813 135 480

etc...

The query would sum all the "ACTUAL" and divide it by the sum
of all the "THEO" to return one efficiency % value for each day (of
course in the running month to date fashion that I mentioned in my first
post).

I'm still a bit new to all of this so I not quite sure what you mean
when you say, "use a subquery correlated with the outer query". Do I
need to have two queries?

I'm sure you are on the right track (and can nail this) since the values
that get returned are correct (just need to get the query to only return
it once for each day).

Thanks again for your help!
Click to expand...
Click to expand...

Perfect!!!

Thank you so much Ken. You saved me much time and work.
I greatly appreciate your help!

Dave
 

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

Fiscal year (another try) 6
Trying to find previous (fiscal) month to date 2
Date Query using Forms Parameter 1
Add a (sequential not autonumber) number in append query 5
Combo Box for Column Heading 2
Month, DATE, Week 7
Running total in query 2
Date Query 2

Top