How to display a value from an unrelated query in a report

[Mmmel]

HELP! I'm about to have a total meltdown!

I've been trying all sorts of things to do the following. I have a financial database that contains incomes and expenses. I have it set up so that each entry contains the date deposited, amount, from who, cheque #, etc. Each entry is either an IN (for income) or EX (for expense). I've just created the General Ledger report that shows a chronologic accounting of all entries in a given month. I now want to sum up the incomes and then sum up the expenses. I then want to add the incomes to the start balance, and subtract the expenses. Sound simple? I thought so too!

I've made two queries that output the correct numbers; neither one of them is the underlying query for the report. That query MUST contain all the entries in the given date range and then display them chronologically, as I said. If I could simply group the incomes and expenses, I would have it. But, I cannot. They are all mixed.

So, how do I plunk in a number from the queries I made? I've tried simply using the Expression builder to get to them, but Access doesn't know what they are. I've tried Dsum, DLookup, and can't get them to display the right numbers (always outputs the first entry amount only).

What I REALLY want, while I'm at it, to maintain a running total on the right, that adds things that are incomes and subtracts things that are expenses. That would do it! However, the Running Sum feature in reports only adds. I cannot see how to do it based on a criteria.

Any insights? THANKS! : )

[NeoPa]

DSum() should work for the first part of the question. You don't give examples of how you tried this, so I cannot tell you where you went wrong.

As for a running sum, you can create hidden controls on your report which sum (Running Sum) the calculated values determined by whether or not it is INcome or EXpense. This can be prepared in your bound query.

If you want a single running total, then it is also very easy to produce a value which is negated if the type is an EXpense. Summing these values gives you what you require I think.

[Mmmel]

Thanks, NeoPa!

I'm trying Dsum again; still not working. Can you help me figure out why?

Here are some (better!) details:

The report is based on a Query called "Q: Financials - Date filtered for Reporting", which takes as parameters start and end dates (so I can total in a given month). Each record consists of the Amount as well as whether it is an IN or EX. This is working properly.

The Dsum looks like this:

=Dsum([Amount], "Q:  Financials - Date filtered for Reporting","[Income or Expense?]='IN'")

which, I think, should sum up everything in the Amount field from the Query "Q: Financials - Date filtered for Reporting" for the records whose criteria matches the "IN" (that I have specified as such). What is wrong? It outputs a total that is massively more than it should be (more than 4 times too big). I cannot even work out HOW it came up with that number. There's no mathematical reasoning that I can see.

Any ideas?

[NeoPa]

If your query filters by start and end date, then your DSum() call certainly doesn't. I expect that is why you are getting a greater value than anticipated.

Why not try the Running Sum option. That will always work for the data within the confines of any relevant filtering of the report (or query).

[Melody Heise]

THANKS! That's exactly what I did. I actually did TWO, with the control source of each specifying to sum ONLY if it was an IN (and then only if it was an EX). Worked BEAUTIFULLY! Yaye! I didn't know you could do that until I tried it! THANKS so much for your help! I'm all set now!

Happy Canadian Thanksgiving!

[NeoPa]

Very happy to hear it Melody :-)

Happy Canadian Thanksgiving to you too and Welcome to Bytes!

[NeoPa]

Melody. I moved your new question to a new thread (Totals Not Correct When Printing Report). I'll do what I can for you from there.