SUM PROBLEM

[Guest]

I have a Query consisting of two table where I have created two additional
columns. One column is a normal Price*Qty and the Other one I Discount that
column by a number that is filled into the discount field. ( With an Else Iif
Statement to keep the Original price if No Discount is Given. In the Query it
works perfectly. Both my Columns are set to a Currency property and the Other
Fileds are Number fields ( Discount & Qty) When I tru to create a REPORT it
refuses to Sum any of these Fields. I have tried the Wizard and it doesn't
even give me the Option of Summing certain Fields. And If I create the Report
and do it Manually it also doesn;t do it. I have tried putting it everywhere
, Header Footers Details NOthing is working

 

[Guest]

Have you tried exporting the calculated data to a table and then building a
report off of that? I have a "decent" knowledge of Access and there's lots of
times that if I sum something under a query and try and get another query to
divide that sum, it won't work unless I've made a table out of it.

(e-mail address removed)

 

[John W. Vinson]

I have a Query consisting of two table where I have created two additional
columns. One column is a normal Price*Qty and the Other one I Discount that
column by a number that is filled into the discount field. ( With an Else Iif
Statement to keep the Original price if No Discount is Given. In the Query it
works perfectly. Both my Columns are set to a Currency property and the Other
Fileds are Number fields ( Discount & Qty) When I tru to create a REPORT it
refuses to Sum any of these Fields. I have tried the Wizard and it doesn't
even give me the Option of Summing certain Fields. And If I create the Report
and do it Manually it also doesn;t do it. I have tried putting it everywhere
, Header Footers Details NOthing is working

Please post the SQL of the Query. There is no such thing as an "Else Iif
Statement" in a query; it's not clear to me just what you're doing!

To display the individual records and also the sum, you need to put a textbox
in the Report Footer - or else use sorting and grouping to group by some
appropriate field, such as by CustomerID, and put the textbox in the group
footer. The textbox's Control Source would be

=Sum([fieldname])

where fieldname is the alias defined in the query for the value you want to
sum. The Page Footer will NOT let you do this, nor can you sum in the detail
section - it needs to be in a group footer or a form footer.

John W. Vinson [MVP]

 

[Guest]

No sorry seemed like a good idea , but for some bizarre reason the system is
seeing this as a text field. I have even now taken out the two additioal
fields that I have created ( Thinking that I would Calculate in the Report
itself ) .but it doesn't even want to sum my Discount or Qty Fields although
it is number fields. Not from the Query or New Table. I thought it might have
something to do with the Realtionships ,but the new Table would have
countered that problems. Still even with the wizard it doesn't give me the
option for the System to calculate these fields.

 

[John W. Vinson]

No sorry seemed like a good idea , but for some bizarre reason the system is
seeing this as a text field.

Again... *PLEASE* post the SQL.

There are many ways you can coerce a number field to text (misuse of IIF, any
use of the Format() function, others). I'd guess you're doing so in some way.
If you'll let us help you by helping us help you - by posting the SQL - we
will.

John W. Vinson [MVP]

 

[Guest]

LINE TOTAL INCL: IIf([DISCOUNT]>0,[TOTAL INCL]-([TOTAL
INCL]/[DISCOUNT]),[TOTAL INCL]). But I eventually found the problem with the
Wizard. Without a Grouping Option It didnlt want to give me a Summary option.
It still doesn't explain why I was unable to Sum in the Report itself by
using Text Box , but I have now Grouped my Subform (I then used the Sum
Option given by the Wizard and transferred it to the Main Form with the
following
=IIf([ITEMS SUBFORM].Report.HasData,[ITEMS SUBFORM].Report.[LINE TOTAL INCL
Grand Total Sum],0) and that somehow solved my problem. ( Got this on another
Access Webpage )

I have a Query consisting of two table where I have created two additional
columns. One column is a normal Price*Qty and the Other one I Discount that
column by a number that is filled into the discount field. ( With an Else Iif
Statement to keep the Original price if No Discount is Given. In the Query it
works perfectly. Both my Columns are set to a Currency property and the Other
Fileds are Number fields ( Discount & Qty) When I tru to create a REPORT it
refuses to Sum any of these Fields. I have tried the Wizard and it doesn't
even give me the Option of Summing certain Fields. And If I create the Report
and do it Manually it also doesn;t do it. I have tried putting it everywhere
, Header Footers Details NOthing is working

Please post the SQL of the Query. There is no such thing as an "Else Iif
Statement" in a query; it's not clear to me just what you're doing!

To display the individual records and also the sum, you need to put a textbox
in the Report Footer - or else use sorting and grouping to group by some
appropriate field, such as by CustomerID, and put the textbox in the group
footer. The textbox's Control Source would be

=Sum([fieldname])

where fieldname is the alias defined in the query for the value you want to
sum. The Page Footer will NOT let you do this, nor can you sum in the detail
section - it needs to be in a group footer or a form footer.

John W. Vinson [MVP]