SQL Help with dates and sums

[JazzBass]

Hi,
I'm trying to get the totals for any given number of months and years and show the total as 1 row.

Hopefully I can explain a little better later in my post

I have the date stored as a date ie. 3/1/1999

For example:
3/1/1999: 4
3/5/1999: 5

12/1/2000: 4
12/4/2000: 5

What I would like to do is let the user choose a date range (Month and Year) and show them the totals by month.

So the results would be:

Date Amount
March 1999: 9
December 2000: 9

Can anyone help me with this. I know it would be easier to add the month and year fields to the table, but I really don't want to do that if I don't have to.

The goal is to display this in a datareport.

Please help.

Thanks,
JazzBass

[Dr_Evil]

Are you trying to total the number of records with that date in them?

If so try this.

SELECT Date, COUNT(*) AS Amount FROM Table1
GROUP BY Date

This would give you the following results.

Date      Amount
4/5/01   10

If this isn't what you are looking for let me know...

[JazzBass]

Hi,
Thanks for the response.

Not quite. I'm trying to find the total amount of 'things', not the number of records.

Like my example stated:
(I changed the field headings)

Date Items
3/1/1999: 4
3/5/1999: 5

Date Items
12/1/2000: 4
12/4/2000: 5

What I would like to do is let the user choose a date range (Month and Year) and show them the total items by month.

So the results would be:

Date TotalItems
March 1999: 9
December 2000: 9

Thanks again Dr.Evil.

I have a query that works, pretty much.

SELECT DISTINCTROW Format([Tech].[ServiceDate],"mmmm") AS sMonth,
format([Tech].[ServiceDate],"yyyy") AS sYear,
Format(Sum([Tech].[ServiceTime]),"##.00") AS TotalTime INTO Temp1
FROM Tech WHERE [Tech].[ServiceDate] Between #1/1/2001# And #3/30/2001#
GROUP BY Format([Tech].[ServiceDate],"mmmm"),
format([Tech].[ServiceDate],"yyyy");

This creates a table called Temp1

Now, I have another table called ForwardedTime that has any time that could not be counted to the whole hour. I want to add this time to the TotalTime field in temp1.

I'm trying to do an UPDATE query, but I'm failing.

I'm willing to try whatever anyone has in mind.

Thanks,
JazzBass

[JazzBass]

Hi,
Nevermind about my question.

I changed some things in my database and it now works great.

Thanks for the help,
JazzBass

[shragel]

Thanks for the SQL statement above. I learned how to use the group method.
But i have a problem. I cant add a order by clause.
Here is my sql code.
It works fine without the order by clause

"Select Format([Date],""mmmm"") As Month, Sum(Quantity) As TQuantity from ItemsOrder Inner Join OrderEntry On ItemsOrder.InvNum = OrderEntry.InvNum Where ItemNum = " & Chr(34) & Combo1(1).Text & Chr(34) & " Group By Format([Date],""mmmm"") Order by TQuantity ;"

Please help

[JazzBass]

Glad the SQL statement works for you.

For your question, have you tried using the actual expression, not the field alias?

So instead of saying "order by TQuanity", try "order by Sum(Quanity)"

Give the a whirl.

JazzBass

[shragel]

Yes it works

[JazzBass]

Great!