MS Excel Formulas to Access reports

Before you start thinking about queries and reports, you need to get your database design right. For example, you do not store inferable data in a database table. You therefore do not store the month and year if you have already stored the date. I also have my doubts about Category class, Top level category and Minor category. If the data is always stored down to the Minor category level, the other two are redundant. It should be clear in other tables to which Top level category and Category class a Minor category belongs. In that case only Minor category should be stored here.

Thank you very much, ill give this a try.

The idea was to have a major category, (Household, Utilities, upkeep, ...) i have more but for purposes these 3 major ones;

Then Top Level would be:

-Household (food, clothes, )

-Utilities (gas, electric, ...)

-Upkeep (Item, service, ...) as in upkeep to the house

Then only some would have a minor category

Upkeep (house, bedroom, bathroom, patio, ...)

All would have a store date and a cost

The idea was to slice it as big (major category) or as small (upkeep by room) without having to constantly go in and change data / filter in excel like I was doing before. also my wife said "oh that should be its own category", so now if it is set up correctly it is as simple as just adjusting or making a new category or sub category and changing a filter to show the data. I can do a cross section over time of how much we pay year over year like food costs, electric (or quickly switch to all utilities) year over year or from the same month in previous years. that i can do now but it is not as dynamic.

There is just 1 expense table and the Major category, Top level category, Minor category, Purchased from are separate tables that are linked with the index from those tables.

Does that make sense?

ok nice this works for sum, I did need to use the month () function but that was easy

TRANSFORM Sum(Expenses.[Expense Cost]) AS [SumOfExpense Cost]

SELECT month(Expenses.[Expense Month]), Sum(Expenses.[Expense Cost]) AS [Total Of Expense Cost]

FROM Expenses

WHERE [category class] = 2

GROUP BY month(Expenses.[Expense Month])

PIVOT Format([Expense Year],"yyyy");

I still have to figure out how to rename the 1 to January and I think I can do a union to get the yearly totals,

thanks for the help

You can use the MonthName() function. You should be able to year totals in the report header.

So have been able to get the query working well but i hard coded the utilities number in the query.

TRANSFORM sum(Expenses.[Expense Cost]) AS [Sum]

SELECT Expenses.[Category Class], Month(Expenses.[Expense Month]) & " - " & MonthName(Month(Expenses.[Expense Month])) AS [Month], Avg(Expenses.[Expense Cost]) AS [Monthly Average]

FROM Expenses

WHERE (((Expenses.[Category Class])=2))

GROUP BY Month(Expenses.[Expense Month]), Expenses.[Category Class]

PIVOT Format([Expense Year],"yyyy");

I also figured out what dhookom was talking about with 2023 ( i added a test item in 2023 so it would not fail) i saw a post here https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb243764(v=office.12)

but I want to have a drop down in the report, to select the category class and if i want drill down further. does anyone have any pointers as to a post that may show how to do that?

Thanks in advance

Crosstab queries require you to specify the data type of all parameters. Check the query properties for “parameters”.