Totals in Query

[Guest]

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".

 

[Bill Mosca, MS Access MVP]

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.

 

[Guest]

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

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.

--
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
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".

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(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,

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

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.

--
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
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".

 

[Guest]

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

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.

--
Doug Steele, Microsoft Access MVP

(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,

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

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.

--
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
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".