Total Query

[JMS]

I am trying to setup a very basic report with a Query from the Order Entry
database template. I am trying to get my report to show a gross total for
each order including freight and sales tax. I have the net amount being
generated by: Net Order Total:
Sum(CLng(nz([Quantity]*[UnitPrice]*(1-[Discount]))*100)/100) and the sales
tax amount being generated by: Sales Tax Total:
Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*(1-[Discount])*100)/100)
which seems to be working okay. I am not sure what is needed to add those
two dollar amounts and display them in a Gross Order Total. The Total Sale
field in the Orders by Customer form has the amount that I am trying to get
myreport to generate. Any help you might be able to provide would be very
much appreciated. Thanks in advance.

 

[bluedragonsbreath]

I am trying to setup a very basic report with a Query from the Order Entry
database template. I am trying to get my report to show a gross total for
each order including freight and sales tax. I have the net amount being
generated by: Net Order Total:
Sum(CLng(nz([Quantity]*[UnitPrice]*(1-[Discount]))*100)/100) and the sales
tax amount being generated by: Sales Tax Total:
Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*(1-[Discount])*100)/100)
which seems to be working okay. I am not sure what is needed to add those
two dollar amounts and display them in a Gross Order Total. The Total Sale
field in the Orders by Customer form has the amount that I am trying to get
myreport to generate. Any help you might be able to provide would be very
much appreciated. Thanks in advance.

The easiest, albeit not elegant way to accomplish this is to add those
two expressions together:

SELECT Sum(CLng(nz([Quantity]*[UnitPrice]*(1-[Discount]))*100)/100) +
Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*(1-[Discount])*100)/
100) as "Gross Total"

 

[JMS]

Seems to work okay though. Not sure when the record count grows if it will
be a problem or not. If you think of a more streamlined way of working it
out let me know. Either way, thanks for taking the time to help...

I am trying to setup a very basic report with a Query from the Order
Entry
database template. I am trying to get my report to show a gross total for
each order including freight and sales tax. I have the net amount being
generated by: Net Order Total:
Sum(CLng(nz([Quantity]*[UnitPrice]*(1-[Discount]))*100)/100) and the
sales
tax amount being generated by: Sales Tax Total:
Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*(1-[Discount])*100)/100)
which seems to be working okay. I am not sure what is needed to add those
two dollar amounts and display them in a Gross Order Total. The Total
Sale
field in the Orders by Customer form has the amount that I am trying to
get
myreport to generate. Any help you might be able to provide would be very
much appreciated. Thanks in advance.

The easiest, albeit not elegant way to accomplish this is to add those
two expressions together:

SELECT Sum(CLng(nz([Quantity]*[UnitPrice]*(1-[Discount]))*100)/100) +
Sum(CLng(nz([Quantity]*[UnitPrice]*[SalesTaxRate])*(1-[Discount])*100)/
100) as "Gross Total"