Totals in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yude - 18 Jun 2007 21:49 GMT

Hi, I'm need help with a query. I've basically created a database to keep
track of expenses and my table consists of date, item, amount and category.

I currently want to create a query to help me find out in a particular
month, for a particular category, how much is the total expenses.

I basically tried what I could learn from the website, and I managed to do
is create a daily total within the particular month for a particular
category. I used the total button and did sum for the field "Amount".

Reply to this Message

Bill Mosca, MS Access MVP - 18 Jun 2007 22:10 GMT

Yude

You can group by the year and month like this query:
SELECT Year([OrderDate]) AS MyYear,
Month([orderdate]) AS MyMonth,
Sum(Orders.Freight) AS SumOfFreight
FROM Orders
GROUP BY Year([OrderDate]), Month([orderdate])
ORDER BY Year([OrderDate]), Month([orderdate]);

In your query you would also include the category both in the Select clause
and the Group By clause.

Signature

Bill Mosca, MS Access MVP
http://tech.groups.yahoo.com/group/MS_Access_Professionals

> Hi, I'm need help with a query. I've basically created a database to keep
> track of expenses and my table consists of date, item, amount and
[quoted text clipped - 6 lines]
> is create a daily total within the particular month for a particular
> category. I used the total button and did sum for the field "Amount".

Reply to this Message

Yude - 19 Jun 2007 17:04 GMT

I'm not very good with the Jet Base langauge syntax. :(
Basically I have Access 2003, and under the SQL view, I have this,

SELECT Expenses.Category, Sum(Expenses.Amount) AS SumOfAmount
FROM Expenses
GROUP BY Expenses.Date, Expenses.Category
HAVING (((Month([Date]))=[Enter Month, 1-12]) AND ((Year([Date]))=[Enter
Year, "YYYY"]) AND ((Expenses.Category)=[Category: Normal Meals, Special
Dining, Transportation, Entertainment, Bills, Essentials, Desires]));

Where do I change such that after I run the query, it shows the figure for
the whole month instead of daily totals.

Thanks

> Yude
>
[quoted text clipped - 19 lines]
> > is create a daily total within the particular month for a particular
> > category. I used the total button and did sum for the field "Amount".

Reply to this Message

Douglas J. Steele - 19 Jun 2007 17:49 GMT

Try

SELECT Format([Date], "mmm dd"), Category, Sum(Amount) AS SumOfAmount
FROM Expenses
WHERE Month([Date])=[Enter Month, 1-12]
AND (Year([Date])=[Enter Year, "YYYY"]
AND Category = [Category: Normal Meals, Special Dining, Transportation,
Entertainment, Bills, Essentials, Desires]
GROUP BY Format([Date], "mmm dd"), Category

Note that Date is not a good choice for a field name: it's a reserved word.
If you cannot (or will not) change it, at least put on square brackets like
I did. Note, too, that it's more efficient to use WHERE than HAVING. With
WHERE, data is eliminated before it's consolidated. With HAVING, it's
consolidated then deleted.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I'm not very good with the Jet Base langauge syntax. :(
> Basically I have Access 2003, and under the SQL view, I have this,
[quoted text clipped - 37 lines]
>> > is create a daily total within the particular month for a particular
>> > category. I used the total button and did sum for the field "Amount".

Reply to this Message

Yude - 20 Jun 2007 04:00 GMT

I did some trial and error on both suggestions. I tried using WHERE, but the
system kept saying there's a syntax error. And I took your advice to rename
the DATE field. So this is the current code that seems to work,

SELECT Month([Expense_Date]) AS ExpenseMonth, Year([Expense_Date]) AS
ExpenseYear, Expenses.Category, Sum(Expenses.Amount) AS SumOfAmount
FROM Expenses
GROUP BY Expenses.Category, Month([Expense_Date]), Year([Expense_Date])
HAVING (((Month([Expense_Date]))=[Enter Month, 1-12]) AND
((Year([Expense_Date]))=[Enter Year, "YYYY"]) AND
((Expenses.Category)=[Category: Normal Meals, Special Dining, Transportation,
Entertainment, Bills, Essentials, Desires]));

> Try
>
[quoted text clipped - 53 lines]
> >> > is create a daily total within the particular month for a particular
> >> > category. I used the total button and did sum for the field "Amount".

Reply to this Message