Suppress printing detail lines

[vbaInet]

More than one way to skin a cat. The approach I've used now is a bit more complex.

The sum in Red is without "Tax" and the other in black is the full total.

I've only done it for the fullname section so if you want accurate totals for the others, just follow what I've done. See attached.

Questions welcome.

NB: I use Access 2007 too.

 

[proulxr]

More than one way to skin a cat. The approach I've used now is a bit more complex.

The sum in Red is without "Tax" and the other in black is the full total.

I've only done it for the fullname section so if you want accurate totals for the others, just follow what I've done. See attached.

Questions welcome.

NB: I use Access 2007 too.

Nice...built the query to run the report from excluding Tax trans type...

In this statement -
=Sum([total])+Nz(DSum("[total]","qryReportDetails","[fullname] = '" & [FullName] & "' AND [InsName] = '" & [InsName] & "' AND [transtype] = 'Tax'"),0)...

please explain the NZ(DSUM... looks like some concatination....

to get just Tax total what is to be removed ? the ([total] for sure but not being familar with NZ(DSUM...

you help has been great...I am now exploring new areas...thank you...I will await your response...

 

[vbaInet]

I will put them in code tags so you can see clearly:

=Sum([total])+[B][COLOR=Blue]Nz[/COLOR][COLOR=Blue]([/COLOR][/B]DSum("[total]","qryReportDetails","[fullname] = [COLOR=Red][B]'[/B][/COLOR]" & [FullName] & "[COLOR=Red][B]'[/B][/COLOR] AND [InsName] = [COLOR=Red][B]'[/B][/COLOR]" & [InsName] & "[COLOR=Red][B]'[/B][/COLOR] AND [transtype] = [COLOR=Red][B]'[/B][/COLOR]Tax[COLOR=Red][B]'[/B][/COLOR]")[B][COLOR=Blue],0)[/COLOR][/B]

The ampersand (&): Yes you were right, concatenation.

The quotes in red: You need to surround text with those when equating it to a String data type. If it were using numbers you don't include them.

DSum: This will explain better
http://www.techonthenet.com/access/functions/domain/dsum.php

Nz: The bits in blue indicate where the Nz arguments are entered. If DSum function returned Null, you can't Null to a number so we need to convert Null to 0. Have a look at this:
http://www.techonthenet.com/access/functions/advanced/nz.php

 

[proulxr]

Thanks for all the Help, figured out the subtotaling and totaling...I have attached the file for your review and final comments...take a look at my final report if you would...

Once again...thanks...:>)

 

[vbaInet]

A quick pointer. Remember that as you begin to go up the grouping hierarchy you need to relax the DSum criteria a bit. For example for the InsName section is a sum of ALL InsName. It doesn't depend on the FullName. A similar change needs to be made for the Report section total. Does that give you an idea what needs to be changed?

Lastly, your database has no primary keys and indexes. Besides that it is seriously lacking normalization. Lookup that term.

 

[proulxr]

A quick pointer. Remember that as you begin to go up the grouping hierarchy you need to relax the DSum criteria a bit. For example for the InsName section is a sum of ALL InsName. It doesn't depend on the FullName. A similar change needs to be made for the Report section total. Does that give you an idea what needs to be changed?

Lastly, your database has no primary keys and indexes. Besides that it is seriously lacking normalization. Lookup that term.

thanks for the input...I have changed the summig and all appears well...I will look into keys and normalization...your input has been incredible and educational....

Best Regards...

 

[vbaInet]

You're most welcome

Glad to have helped and happy you learnt something from this. Good luck with the rest of the db.