Query all records and sum only records from this year.

[rmurry]

I have created a query from two tables with the following SQL statement

SELECT Transactions.ID, Transactions.CheckDate, Transactions.TransactionDescription, Transactions.Category, Transactions.Amount, Transactions.Cleared, Transactions.CheckNumber, Transactions.Memo, Categories.[Income/Expense]
FROM Categories INNER JOIN Transactions ON Categories.Catagory_ID = Transactions.Category
WHERE (((Categories.[Income/Expense])="Expense"));

I would like to query that query to return every category and sum amount values that occur Year-to-date, and there is no transaction year-to-date, it returns a $0.00 for the amount. I think I need the Nz function, but I'm not doing something right because I can sum those Amounts, but not return the $0.00 value when that transaction has not occurred yet this year:

SELECT DISTINCT Expenses.Category, Year([CheckDate]) AS [Year], Nz(Sum([Amount]),0) AS Total
FROM Expenses
GROUP BY Expenses.Category, Year([CheckDate])
HAVING (((Year([CheckDate]))=Year(Date())));

Thank you for your help.

 

[plog]

I would like to query that query to return every category and sum amount values that occur Year-to-date, and there is no transaction year-to-date, it returns a $0.00 for the amount.

Your writing and use of commas is hard to follow. Are you saying it returns $0.00 or are you saying you want it to return $0.00?

Actually, the best way to communicate your intentions is via sample data. Provide 2 sets:

A. Sample data of your first query. Show us some sample datat that the first query returns--include field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect the second query to return when you feed it the data from A.

 

[rmurry]

Sorry about awkward wording. I want the new query to return $0.00 if and expense in that category has not occurred YTD.

So the query I am taking data from looks something like:

ID| CheckDate | Category | Amount |Income/Expense|
53| 12/8/2017 | PO Box | $54.70 |Expense
54| 12/8/2017 | Hurricane Relief | $500.00 |Expense
48| 12/8/2017 | Scholarship | $2500.00 |Expense
64| 10/7/2018 | Scholarship | $2500.00 |Expense
63| 10/17/2018| Scholarship | $2000.00 |Expense
62| 10/22/2018| Hurricane Relief | $500.00 |Expense

And the query I would like to make would look something like this

Year | Category | Total
2018 | PO Box | $0.00
2018 | Scholarship | $4500.00
2018 | Hurricane Relief | $500.00

I hope that makes it more clear.

 

[plog]

First, you've got some bad field names that make coding and querying more difficult. You should replace them.

'Year' is a reserved word (https://support.office.com/en-us/ar...-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2).

'Income/Expense' contains special characters. Instead of a slash (or any other special character), use an underscore.

With that said, this SQL will produce the results you want:

SELECT Year(Date()) AS TotalYear, YourQueryNameHere.Category, Sum(IIf([CheckDate]<Date() And Year([CheckDate])=Year(Date()),[Amount],0)) AS Total
FROM YourQueryNameHere
GROUP BY Year(Date()), YourQueryNameHere.Category;

 

[rmurry]

Works perfectly.

The "Year" heading was just for the example. I knew better than that. I'll change the other column heading too.

Thanks.