Sub totaling in a query or report

[n2mee]

Hello, Scott here. I am not very savy in programming but I have done very well using the expression builder, but now I hit a wall. I am using Access 2007 and what I want can be done easily in a spreadsheet, but I like using access and like to experiment.



Anyway I have 2 fields in a query, "Debit" and "Credit". I have no problem getting the totals of each and the grand total of "Debit" - "Credit". What I want to do is create a 3rd virtual field giving me a sub total after each entry, (Sub total of Debit - Subtotal of Credit). I have read that you can add a additional field or control in a query, but I dont seem to have any success on figuring that out.

I'm sure this can be done, if you can help, I would appreciate it. The only thing I seem to be able to enter formulas on is the "Expression builder" and with great difficulty. If there is another way, I would need a step by step on how to do it.

Thank you,

Scott

[ItsMe]

Add a new field to your query, any random one. In front of the text within the field paste

TestName:

The key here is to use the semicolon between the name you give and the original text that access placed in the field.

That should get you started anyway...

[n2mee]

Thanks ItsMe for replying. Yes I have tried that and the query will run but it yields blank data.

I've tried, Sub: [Credit] - [Debit]
= [Credit] - [Debit]
Sum [Credit] - [Debit]
and =Sum [Credit - [Debit]

I've tried it with and without the table name, always no data.

Scott

[n2mee]

I've tried...

Sub: [Credit] - [Debit]
Sub: = [Credit] - [Debit]
Sub: Sum [Credit] - [Debit]
Sub: = Sum [Credit] - [Debit]

I've also tried it with & without the table name. All will run, but yield no data.

Just a sidebar, "Credit" and "Debit" as you are aware by now are the 2 fields I want to get the difference of and they are on the same table/query.

Thanks again,

Scott

[ItsMe]

Rather than entering a statement, try just creating an alias. This is what post # 2 describes.
Are you comfortable making an alias?

[n2mee]

Rather than entering a statement, try just creating an alias. This is what post # 2 describes.
Are you comfortable making an alias?

If you are referring to the field named Sub: then yes. I think I am just putting my formula in the wrong location, if my formula is correct that is.

[ItsMe]

I think the trouble you are encountering is trying to create an aggregate function within your join.

Where do you want to display the differencr between debit and credit? You may be better off doing all of the calculations there, in a form or a report.

You can build a seperate group by querry that creates a sum and save it. The results will be limited to where it is joined.

Are you trying to build a report or are do you want to display this in a form?

[n2mee]

Well, I figured it out. The problem is there is null data & the formulas wouldnt calculate with null data. I changed my calculation to Sub: Nz([Credit])-Nz([Debit]) and it worked.

I thank you for all of your help, you have a great weekend.

Scott

[ItsMe]

Glad you got it resolved and sorry I was not more help to you.