First of all, it is a bit strange that you have a separate table for categories, but use the categoryName in table transactions. I would say:
tbl_transactions : TransID, TransDate, CategoryID,TransAmount
tbl_Category: CategoryID, CategoryName
This way you link the two tables on the CategoryID, probably an autonumber in tbl_Category, so a number filed - long integer in tbl_transactions.
Than: if you do the sum by month, preceed it with a group by year. You don't want to make the sum of Jan 2019 + Jan 2020 + ...
The following query should work after changing the table links:
Code:
SELECT tbl_Category.CategoryName, Year([transDate]) AS YearTrans, Month([transDate]) AS MonthTrans, Sum(tbl_Transaction.TransAmount) AS SumOfTransAmount
FROM tbl_Category INNER JOIN tbl_Transaction ON tbl_Category.CategoryID = tbl_Transaction.CategoryID
GROUP BY tbl_Category.CategoryName, Year([transDate]), Month([transDate]);