A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Subtotals



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2010, 01:29 AM posted to microsoft.public.access.reports
Lori LeRoy
external usenet poster
 
Posts: 4
Default Subtotals

I have a sales order report that I want to display the units ordered subtotal
if the ship quantity is zero and/or the line item is open; and the units
shipped if the quantity shipped is greater than zero and/or the line item is
closed. (if any quantity is shipped that line item is closed). currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the line
item is open or quanities have been shipped, creating a larger total for the
entire report. I'm sure this is a simple if statement, but I'm at a loss.
Thanks.
  #2  
Old March 4th, 2010, 07:02 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Subtotals

Can you provide some field names, data types, sample records, and desired
display in the report?


--
Duane Hookom
MS Access MVP


"Lori LeRoy" wrote in message
news
I have a sales order report that I want to display the units ordered
subtotal
if the ship quantity is zero and/or the line item is open; and the units
shipped if the quantity shipped is greater than zero and/or the line item
is
closed. (if any quantity is shipped that line item is closed). currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the line
item is open or quanities have been shipped, creating a larger total for
the
entire report. I'm sure this is a simple if statement, but I'm at a loss.
Thanks.


  #3  
Old May 24th, 2010, 09:19 PM posted to microsoft.public.access.reports
Lori LeRoy
external usenet poster
 
Posts: 4
Default Subtotals

I just found this posting - I apologize for the late response.

The sorting & grouping in the report is as follows:
SalesOrderID
Openlineitem
ItemNo
ProductID

The detail in the report is:
itemorder
SOTransactionID
Shipdate
UnitsOrdered
UnitsShipped
OpenLineItem
price
SubtotalSO

and the footer is
=Sum[(SubtotalSO)]

Thank you,
Lori

"Duane Hookom" wrote:

Can you provide some field names, data types, sample records, and desired
display in the report?


--
Duane Hookom
MS Access MVP


"Lori LeRoy" wrote in message
news
I have a sales order report that I want to display the units ordered
subtotal
if the ship quantity is zero and/or the line item is open; and the units
shipped if the quantity shipped is greater than zero and/or the line item
is
closed. (if any quantity is shipped that line item is closed). currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the line
item is open or quanities have been shipped, creating a larger total for
the
entire report. I'm sure this is a simple if statement, but I'm at a loss.
Thanks.


  #4  
Old May 25th, 2010, 03:27 AM posted to microsoft.public.access.reports
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Subtotals

We don't know what defines a line item as "open". I guess that "ship
quantity" is "UnitsShipped"? There are no fields named UnitsOrderedSO and
UnitPriceSO.

You need to talk using field names or at least define the fields. Also,
field types might be important or possible values.

--
Duane Hookom
MS Access MVP


"Lori LeRoy" wrote in message
...
I just found this posting - I apologize for the late response.

The sorting & grouping in the report is as follows:
SalesOrderID
Openlineitem
ItemNo
ProductID

The detail in the report is:
itemorder
SOTransactionID
Shipdate
UnitsOrdered
UnitsShipped
OpenLineItem
price
SubtotalSO

and the footer is
=Sum[(SubtotalSO)]

Thank you,
Lori

"Duane Hookom" wrote:

Can you provide some field names, data types, sample records, and desired
display in the report?


--
Duane Hookom
MS Access MVP


"Lori LeRoy" wrote in message
news
I have a sales order report that I want to display the units ordered
subtotal
if the ship quantity is zero and/or the line item is open; and the
units
shipped if the quantity shipped is greater than zero and/or the line
item
is
closed. (if any quantity is shipped that line item is closed).
currently I
have this for the line item subtotal:
SubtotalSO: CCur(tblSOTransactions.UnitPriceSO*[UnitsOrderedSO])

And it displays the units ordered subtotal regardless of whether the
line
item is open or quanities have been shipped, creating a larger total
for
the
entire report. I'm sure this is a simple if statement, but I'm at a
loss.
Thanks.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.