Query Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregw - 07 Nov 2005 22:25 GMT

I am using the following query:

SELECT Year([ClosingDate]) AS [Year], Month([ClosingDate]) AS [Month],
Count(File_Tracking.FileNumber) AS Files
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY Year([ClosingDate]), Month([ClosingDate]);

Which will give us the number of files that closed for each specific month.
We have an additional field in our table called "Gross". Is there an easy
way to sum up the gross amount for each file (that closed), thereby getting a
Gross amount for all files closed for each month? (I was trying to use the
Sum function but couldn't get it to work correclty with my query). Any help
would be greatly appreciated.

Thanks

Reply to this Message

Ofer - 07 Nov 2005 22:41 GMT

To sum the Gross for all the files using the existed SQL, try this

SELECT Year([ClosingDate]) AS [Year], Month([ClosingDate]) AS [Month],
Count(File_Tracking.FileNumber) AS Files, Sum([Gross]) as SumOfGross
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY Year([ClosingDate]), Month([ClosingDate]);

To sum per file, add the file number to the SQL

SELECT FileFieldName , Year([ClosingDate]) AS [Year], Month([ClosingDate])
AS [Month],
Count(File_Tracking.FileNumber) AS Files, Sum([Gross]) as SumOfGross
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY FileFieldName, Year([ClosingDate]), Month([ClosingDate]);

Signature

The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

> I am using the following query:
>
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

'69 Camaro - 07 Nov 2005 23:08 GMT

> Is there an easy
> way to sum up the gross amount for each file (that closed), thereby getting a
> Gross amount for all files closed for each month?

Try:

SELECT Year([ClosingDate]) AS [Year], Month([ClosingDate]) AS [Month],
Count(File_Tracking.FileNumber) AS Files, SUM(Gross) AS Total
FROM File_Tracking
WHERE ([ClosingDate]) Is Not Null
GROUP BY Year([ClosingDate]), Month([ClosingDate]);

> (I was trying to use the
> Sum function but couldn't get it to work correclty with my query).

Open the query in Design View and select the "Totals" button on the built-in
toolbar. It looks like a Greek sigma (backwards E). When you do this, it
adds a new row (Total:) to the query grid for each field. The default is
"Group by" in this cell for every column, but you can change this by
selecting any other value in the Total: combo box. In this case, "Sum" would
be the one you want. And to give this calculated value a name (called an
alias), change the Field: cell for the Gross column to:

Total: Gross

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

> I am using the following query:
>
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message