Query Help

[Guest]

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

 

[Guest]

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]);

 

[Guest]

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:

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