Calculating a Balance

[ledgerr.rob]

So I think I have reached the point where I am happy with my data entry. But now i'm trying to get the data out. My goal is to create a query that will give me a balance from my projected expenses and my actual costs. I was able to create three queries of interest here:

1) qryProjectedExpensesGrouped and 2)qryActualCostsGrouped

where i have summed the entries for each the projected and actual entries.

Now i would like to create a balance. The only caviat is that i would like to see all the available category-item pairings even if they are blank. The third query:
3) qryTest

where i've been playing around with trying to get this to work. I've hit a dead end of frustration so i thought i'd post here and take a break from this for a couple hours or until tomorrow. I think this should be able to be done in one query so in the future i remove the excess work but just trying to step my way through this and prove to myself that what i'm pulling out of here is actually the right data. Right now qryTest is producing entries that I think are considered a "cartesian join" but I might be off on this...

This might not make a lot of sense, and for that matter i might be approaching this objective in the wrong manner so i've attached a copy of the db in 03 format for those who would like to take a look.

Thanks
rob

 

[bob fitz]

1) qryProjectedExpensesGrouped and 2)qryActualCostsGrouped

I can't see either of these queries in your db.

 

[ledgerr.rob]

I just saved my uploaded file and opened it. I see both of the querries but the first one: qry ProjectedExpensesGrouped is spelled qryProjectedExpenseGrouped (missing the second 's'). I assume this is the problem. If you still can't see it i can re-upload the zip file if you would like.

 

[bob fitz]

I see five queries:
qryActual
qryCategoryItemCombined
qrycboCategorySelection
qryProjected
qryStoreSelection

 

[ledgerr.rob]

I'm sorry for the confusion. I've gone back to my latest working version of the database on my desktop, verified that the information is correct, re-zipped, and posted this-see attached. Hopefully this works.

there should be 6 querries included:
1) qryActualCostsEveryEntry
2)qryActualCostsGrouped
3) qryCategoryItems
4) qryProjectedExpenseGrouped
5) qryProjectedExpensesEveryEntry
6) qryTest

My initial post should still apply to this DB.

rob

 

[bob fitz]

Is this what you want:

SELECT tblCategory.Category, CCur(nz([qryProjectedExpenseGrouped].[SumOfAmount],0)) AS Projected, CCur(nz([qryActualCostsGrouped].[SumOfAmount],0)) AS Actual, CCur(nz([qryProjectedExpenseGrouped].[SumOfAmount],0))-CCur(nz([qryActualCostsGrouped].[SumOfAmount],0)) AS Balance
FROM (tblCategory LEFT JOIN qryProjectedExpenseGrouped ON tblCategory.Category = qryProjectedExpenseGrouped.Category) LEFT JOIN qryActualCostsGrouped ON tblCategory.Category = qryActualCostsGrouped.Category
GROUP BY tblCategory.Category, CCur(nz([qryProjectedExpenseGrouped].[SumOfAmount],0)), CCur(nz([qryActualCostsGrouped].[SumOfAmount],0)), CCur(nz([qryProjectedExpenseGrouped].[SumOfAmount],0))-CCur(nz([qryActualCostsGrouped].[SumOfAmount],0));

 

[ledgerr.rob]

This is very close to what i'm looking for.

I would like to add the field items to the mix. Attached is a spreadsheet of what i'm hoping to accomplish with the query. also is a newer version of the db...

I think i mentioned before that i want every category listed even if no entries exist for the category/item pairing. After some thought, this is just going to create a lot of extra 'nothing' space.

 

[bob fitz]

Perhaps this is what you require:

SELECT qryProjectedExpenseGrouped.Account, qryProjectedExpenseGrouped.Date, tblCategory.Category, qryProjectedExpenseGrouped.Item, CCur(nz(qryProjectedExpenseGrouped.SumOfAmount,0)) AS Projected, CCur(nz(qryActualCostsGrouped.SumOfAmount,0)) AS Actual, CCur(nz(qryProjectedExpenseGrouped.SumOfAmount,0))-CCur(nz(qryActualCostsGrouped.SumOfAmount,0)) AS Balance
FROM (tblCategory INNER JOIN qryProjectedExpenseGrouped ON tblCategory.Category = qryProjectedExpenseGrouped.Category) INNER JOIN qryActualCostsGrouped ON tblCategory.Category = qryActualCostsGrouped.Category
GROUP BY qryProjectedExpenseGrouped.Account, qryProjectedExpenseGrouped.Date, tblCategory.Category, qryProjectedExpenseGrouped.Item, CCur(nz(qryProjectedExpenseGrouped.SumOfAmount,0)), CCur(nz(qryActualCostsGrouped.SumOfAmount,0)), CCur(nz(qryProjectedExpenseGrouped.SumOfAmount,0))-CCur(nz(qryActualCostsGrouped.SumOfAmount,0));

 

[ledgerr.rob]

You have it nailed. The only change i made was to sum the projected, actual, and balance fields. It is what i've been seeking for so long now.

I can't express how much Linds and I appreciate this. It's a functional monthly budget tracker that we can use. I'll develop some reports for the accounts based on what you've provided.

I have some additional plans for tracking savings t-accounts and saving goals. Eventually i want to develop a switchboard for this but this is the piece i was hoping for.

Thank you so much
rob

 

[bob fitz]

If this helped, please click the scales at the bottom left of this posting.

Many thanks