Monthly Totals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've searched for this and the answers aren't quite what I'm looking for.

I have tables Order and OrderDetails. I use a query to total the Cost field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd expect.
I'd now like to have a seperate query that sums all costs where the OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can do this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS [SumOfSum Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate) Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every month. I'd
like the OrderDate field formatted as "January" etc if possible.

Ideas?

Thanks

Dave
 
SELECT Format(Order.OrderDate,"yyyy/mm") as YrMonth,
Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS [SumOfSum Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
WHERE Order.OrderDate between DateSerial(Year(Date()),1,1)
And DateSerial(Year(Date()),13,0)
GROUP BY Format(Order.OrderDate,"yyyy/mm") as YrMonth
 
Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.
 
OK, thanks for your help.

I have this code working as expected:

SELECT Month([Order].[OrderDate]) AS OrderMonth, Year([Order].[OrderDate])
AS OrderYear,
Sum(CCur([OrderDetails].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
MonthlyTotal
FROM [Order] INNER JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID
GROUP BY Year([Order].[OrderDate]), Month([Order].[OrderDate])
ORDER BY Year([Order].[OrderDate]), Month([Order].[OrderDate]);

Now I'm trying to format the OrderMonth field into "January" type format.
From Johns post I tried:

SELECT Format(Month([Order].[OrderDate]), "mmmm") AS OrderMonth

This formats the date into the correct format, except the months are one
behind where they should be. For example, 05/01/06 gets formatted as December
and 12/02/06 gets formatted as January.

Any ideas?

Dave

Allen Browne said:
Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David M C said:
I've searched for this and the answers aren't quite what I'm looking for.

I have tables Order and OrderDetails. I use a query to total the Cost
field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd expect.
I'd now like to have a seperate query that sums all costs where the
OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can do
this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS [SumOfSum
Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON Order.OrderID
=
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate)
Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every month. I'd
like the OrderDate field formatted as "January" etc if possible.

Ideas?

Thanks

Dave
Click to expand...
Click to expand...
 
You don't need both Format() and Month():
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

The Month() function returns 1 for January.
The Format function assumes the value is a date if it is format it as a
month, and so it looks at the 1 as a date. Internally, Access stores dates
as a number, and 1 happens to be December 1 1899, as you can show with:
? Format( 1 , "Short Date")
Hence, the date 1 is a December date, and so the entire expression correctly
returns December, because you have converted the month number 1 into a date
with a December value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David M C said:
OK, thanks for your help.

I have this code working as expected:

SELECT Month([Order].[OrderDate]) AS OrderMonth, Year([Order].[OrderDate])
AS OrderYear,
Sum(CCur([OrderDetails].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
MonthlyTotal
FROM [Order] INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
GROUP BY Year([Order].[OrderDate]), Month([Order].[OrderDate])
ORDER BY Year([Order].[OrderDate]), Month([Order].[OrderDate]);

Now I'm trying to format the OrderMonth field into "January" type format.
From Johns post I tried:

SELECT Format(Month([Order].[OrderDate]), "mmmm") AS OrderMonth

This formats the date into the correct format, except the months are one
behind where they should be. For example, 05/01/06 gets formatted as
December
and 12/02/06 gets formatted as January.

Any ideas?

Dave

Allen Browne said:
Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.

David M C said:
I've searched for this and the answers aren't quite what I'm looking
for.

I have tables Order and OrderDetails. I use a query to total the Cost
field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd
expect.
I'd now like to have a seperate query that sums all costs where the
OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can do
this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS
[SumOfSum
Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON
Order.OrderID
=
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between
DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate)
Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every month.
I'd
like the OrderDate field formatted as "January" etc if possible.
Click to expand...
Click to expand...
Click to expand...
 
Thanks very much.

Dave

Allen Browne said:
You don't need both Format() and Month():
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

The Month() function returns 1 for January.
The Format function assumes the value is a date if it is format it as a
month, and so it looks at the 1 as a date. Internally, Access stores dates
as a number, and 1 happens to be December 1 1899, as you can show with:
? Format( 1 , "Short Date")
Hence, the date 1 is a December date, and so the entire expression correctly
returns December, because you have converted the month number 1 into a date
with a December value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David M C said:
OK, thanks for your help.

I have this code working as expected:

SELECT Month([Order].[OrderDate]) AS OrderMonth, Year([Order].[OrderDate])
AS OrderYear,
Sum(CCur([OrderDetails].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
MonthlyTotal
FROM [Order] INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
GROUP BY Year([Order].[OrderDate]), Month([Order].[OrderDate])
ORDER BY Year([Order].[OrderDate]), Month([Order].[OrderDate]);

Now I'm trying to format the OrderMonth field into "January" type format.
From Johns post I tried:

SELECT Format(Month([Order].[OrderDate]), "mmmm") AS OrderMonth

This formats the date into the correct format, except the months are one
behind where they should be. For example, 05/01/06 gets formatted as
December
and 12/02/06 gets formatted as January.

Any ideas?

Dave

Allen Browne said:
Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.

I've searched for this and the answers aren't quite what I'm looking
for.

I have tables Order and OrderDetails. I use a query to total the Cost
field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd
expect.
I'd now like to have a seperate query that sums all costs where the
OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can do
this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS
[SumOfSum
Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON
Order.OrderID
=
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between
DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate)
Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every month.
I'd
like the OrderDate field formatted as "January" etc if possible.
Click to expand...
Click to expand...
Click to expand...
 
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

When I use this Access returns an error:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

Any ideas?

Thanks

Dave

Allen Browne said:
You don't need both Format() and Month():
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

The Month() function returns 1 for January.
The Format function assumes the value is a date if it is format it as a
month, and so it looks at the 1 as a date. Internally, Access stores dates
as a number, and 1 happens to be December 1 1899, as you can show with:
? Format( 1 , "Short Date")
Hence, the date 1 is a December date, and so the entire expression correctly
returns December, because you have converted the month number 1 into a date
with a December value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David M C said:
OK, thanks for your help.

I have this code working as expected:

SELECT Month([Order].[OrderDate]) AS OrderMonth, Year([Order].[OrderDate])
AS OrderYear,
Sum(CCur([OrderDetails].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
MonthlyTotal
FROM [Order] INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
GROUP BY Year([Order].[OrderDate]), Month([Order].[OrderDate])
ORDER BY Year([Order].[OrderDate]), Month([Order].[OrderDate]);

Now I'm trying to format the OrderMonth field into "January" type format.
From Johns post I tried:

SELECT Format(Month([Order].[OrderDate]), "mmmm") AS OrderMonth

This formats the date into the correct format, except the months are one
behind where they should be. For example, 05/01/06 gets formatted as
December
and 12/02/06 gets formatted as January.

Any ideas?

Dave

Allen Browne said:
Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.

I've searched for this and the answers aren't quite what I'm looking
for.

I have tables Order and OrderDetails. I use a query to total the Cost
field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd
expect.
I'd now like to have a seperate query that sums all costs where the
OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can do
this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS
[SumOfSum
Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON
Order.OrderID
=
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between
DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate)
Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every month.
I'd
like the OrderDate field formatted as "January" etc if possible.
Click to expand...
Click to expand...
Click to expand...
 
This was a Totals query.
What is in the Total row under this OrderMonth field?
Try "Group By".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David M C said:
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

When I use this Access returns an error:

You tried to execute a query that does not include the specified
expression
<name> as part of an aggregate function. (Error 3122)

Any ideas?

Thanks

Dave

Allen Browne said:
You don't need both Format() and Month():
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

The Month() function returns 1 for January.
The Format function assumes the value is a date if it is format it as a
month, and so it looks at the 1 as a date. Internally, Access stores
dates
as a number, and 1 happens to be December 1 1899, as you can show with:
? Format( 1 , "Short Date")
Hence, the date 1 is a December date, and so the entire expression
correctly
returns December, because you have converted the month number 1 into a
date
with a December value.

David M C said:
OK, thanks for your help.

I have this code working as expected:

SELECT Month([Order].[OrderDate]) AS OrderMonth,
Year([Order].[OrderDate])
AS OrderYear,
Sum(CCur([OrderDetails].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM [Order] INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
GROUP BY Year([Order].[OrderDate]), Month([Order].[OrderDate])
ORDER BY Year([Order].[OrderDate]), Month([Order].[OrderDate]);

Now I'm trying to format the OrderMonth field into "January" type
format.
From Johns post I tried:

SELECT Format(Month([Order].[OrderDate]), "mmmm") AS OrderMonth

This formats the date into the correct format, except the months are
one
behind where they should be. For example, 05/01/06 gets formatted as
December
and 12/02/06 gets formatted as January.

Any ideas?

Dave

:

Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order
Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.

I've searched for this and the answers aren't quite what I'm looking
for.

I have tables Order and OrderDetails. I use a query to total the
Cost
field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd
expect.
I'd now like to have a seperate query that sums all costs where the
OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can
do
this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS
[SumOfSum
Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON
Order.OrderID
=
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between
DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate)
Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every
month.
I'd
like the OrderDate field formatted as "January" etc if possible.
Click to expand...
Click to expand...
Click to expand...
 
Thanks, all sorted. It had "Expression" in the Total field. Changed it to
"Group By" and it works.

Allen Browne said:
This was a Totals query.
What is in the Total row under this OrderMonth field?
Try "Group By".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

David M C said:
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

When I use this Access returns an error:

You tried to execute a query that does not include the specified
expression
<name> as part of an aggregate function. (Error 3122)

Any ideas?

Thanks

Dave

Allen Browne said:
You don't need both Format() and Month():
SELECT Format([Order].[OrderDate], "mmmm") AS OrderMonth

The Month() function returns 1 for January.
The Format function assumes the value is a date if it is format it as a
month, and so it looks at the 1 as a date. Internally, Access stores
dates
as a number, and 1 happens to be December 1 1899, as you can show with:
? Format( 1 , "Short Date")
Hence, the date 1 is a December date, and so the entire expression
correctly
returns December, because you have converted the month number 1 into a
date
with a December value.

OK, thanks for your help.

I have this code working as expected:

SELECT Month([Order].[OrderDate]) AS OrderMonth,
Year([Order].[OrderDate])
AS OrderYear,
Sum(CCur([OrderDetails].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM [Order] INNER JOIN OrderDetails ON Order.OrderID =
OrderDetails.OrderID
GROUP BY Year([Order].[OrderDate]), Month([Order].[OrderDate])
ORDER BY Year([Order].[OrderDate]), Month([Order].[OrderDate]);

Now I'm trying to format the OrderMonth field into "January" type
format.
From Johns post I tried:

SELECT Format(Month([Order].[OrderDate]), "mmmm") AS OrderMonth

This formats the date into the correct format, except the months are
one
behind where they should be. For example, 05/01/06 gets formatted as
December
and 12/02/06 gets formatted as January.

Any ideas?

Dave

:

Something like this:

SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum(CCur([Order
Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100)
AS
MonthlyTotal
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate])
ORDER BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);

Test in northwind.mdb to see a graphical view.

I've searched for this and the answers aren't quite what I'm looking
for.

I have tables Order and OrderDetails. I use a query to total the
Cost
field
in OrderDetails:

SELECT DISTINCTROW [OrderDetails Query].OrderID, Sum([OrderDetails
Query].ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM [OrderDetails Query]
GROUP BY [OrderDetails Query].OrderID
ORDER BY [OrderDetails Query].OrderID;

This works fine. It sums all Costs with the same OrderID as you'd
expect.
I'd now like to have a seperate query that sums all costs where the
OrderDate
(stored in Order) are in the same month (ie a Monthly Total). I can
do
this
for the current month:

SELECT Sum([OrderDetails Subtotals].[Sum Of ExtendedPrice]) AS
[SumOfSum
Of
ExtendedPrice]
FROM ([Order] INNER JOIN [OrderDetails Subtotals] ON Order.OrderID =
[OrderDetails Subtotals].OrderID) INNER JOIN OrderDetails ON
Order.OrderID
=
OrderDetails.OrderID
WHERE (((Order.OrderDate) Between
DateSerial(Year(Date()),Month(Date()),1)
And DateSerial(Year(Date()),Month(Date())+1,0) Or (Order.OrderDate)
Between
DateSerial(Year(Date()),Month(Date()),1) And
DateSerial(Year(Date()),Month(Date())+1,0)));

but for some reason (call it brainfade), can't do it for every
month.
I'd
like the OrderDate field formatted as "January" etc if possible.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top