Trouble with Sum on Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that is generated based on criteria chosen in a multi-select
list box form. I’m trying to summarize the form by displaying currency
totals on the form, but one field/control is not calculating accurate totals.

Here is how the form is structured:
The report returns only one “ProjectNameâ€, which is accounted for in the
report header.
Under that, there can be more than one “BidNumberâ€, based on what is
selected in the multi-select list box. The data related to this is grouped
together, and within the “Bid†table is a field called “SalesTaxAmountâ€.
Under that is the “ItemLabel†portion of the report, which contains the
detail.

The SalesTaxAmount field in the Bid table contains a fixed currency amount
that users enter, so there is only one value per BidNumber. I created a
control in the report named SalesTaxTotal, which is intended to add the
SalesTaxAmount for BidNumber 01, 02, 03, etc. In the control source I have
the expression: =Sum([SalesTaxAmount]). The problem I’m having is that it
is adding one SalesTaxAmount value for each record in the detail. For
example, if the value in SalesTaxAmount is $100.00, and there are 20 records,
the control SalesTaxTotal returns $2,000.00.

I suspect it may have something to do with the code in the multi-select list
box form, so I have pasted a portion that at the end of this post. I’m no VB
expert, but noticed that “Bid.SalesTaxAmount†exists in a couple locations.
Any help with this would be greatly appreciated! Thanks!

Here is that code:
strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax, Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType" & _
" FROM (Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode) INNER
JOIN (Bid INNER JOIN (Customer INNER JOIN ((ItemDetail INNER JOIN Project ON
ItemDetail.ProjectName = Project.ProjectName) INNER JOIN Item ON
(Item.ItemNumber = ItemDetail.ItemNumber) AND (Item.RoomNumber =
ItemDetail.RoomNumber) AND (Item.BidNumber = ItemDetail.BidNumber) AND
(Item.ProjectName = ItemDetail.ProjectName) AND (Project.ProjectName =
Item.ProjectName)) ON (Customer.GCName = Project.GCName) AND (Customer.GCName
= Project.GCName)) ON (Project.ProjectName = Bid.ProjectName) AND
(Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.ProjectName)) ON
Product.ProductDescription = ItemDetail.ProductDescription" & _
" WHERE Project.ProjectName = '" & Me.cboProjectName & "' And
(Bid.BidNumber) in (" & strCriteria & ")" & _
" ORDER BY Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" - "" &
Item.ItemNumber, Item.RoomNumber, Item.ItemNumber, Product.LibraryReference,
Product.ProductCode;"
 
You can create a text box in your BidNumber header or footer section:
Name: txtTotSalesTax
Control Source:[SalesTaxAmount]
Running Sum: Over All
Visible: No
Then add a text box to your report footer section:
Control Source: =txtTotSalesTax

--
Duane Hookom
MS Access MVP


Kevin said:
I have a report that is generated based on criteria chosen in a
multi-select
list box form. I'm trying to summarize the form by displaying currency
totals on the form, but one field/control is not calculating accurate
totals.

Here is how the form is structured:
The report returns only one "ProjectName", which is accounted for in the
report header.
Under that, there can be more than one "BidNumber", based on what is
selected in the multi-select list box. The data related to this is
grouped
together, and within the "Bid" table is a field called "SalesTaxAmount".
Under that is the "ItemLabel" portion of the report, which contains the
detail.

The SalesTaxAmount field in the Bid table contains a fixed currency amount
that users enter, so there is only one value per BidNumber. I created a
control in the report named SalesTaxTotal, which is intended to add the
SalesTaxAmount for BidNumber 01, 02, 03, etc. In the control source I
have
the expression: =Sum([SalesTaxAmount]). The problem I'm having is that
it
is adding one SalesTaxAmount value for each record in the detail. For
example, if the value in SalesTaxAmount is $100.00, and there are 20
records,
the control SalesTaxTotal returns $2,000.00.

I suspect it may have something to do with the code in the multi-select
list
box form, so I have pasted a portion that at the end of this post. I'm no
VB
expert, but noticed that "Bid.SalesTaxAmount" exists in a couple
locations.
Any help with this would be greatly appreciated! Thanks!

Here is that code:
strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType" & _
" FROM (Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode) INNER
JOIN (Bid INNER JOIN (Customer INNER JOIN ((ItemDetail INNER JOIN Project
ON
ItemDetail.ProjectName = Project.ProjectName) INNER JOIN Item ON
(Item.ItemNumber = ItemDetail.ItemNumber) AND (Item.RoomNumber =
ItemDetail.RoomNumber) AND (Item.BidNumber = ItemDetail.BidNumber) AND
(Item.ProjectName = ItemDetail.ProjectName) AND (Project.ProjectName =
Item.ProjectName)) ON (Customer.GCName = Project.GCName) AND
(Customer.GCName
= Project.GCName)) ON (Project.ProjectName = Bid.ProjectName) AND
(Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.ProjectName))
ON
Product.ProductDescription = ItemDetail.ProductDescription" & _
" WHERE Project.ProjectName = '" & Me.cboProjectName & "' And
(Bid.BidNumber) in (" & strCriteria & ")" & _
" ORDER BY Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" - "" &
Item.ItemNumber, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference,
Product.ProductCode;"
Click to expand...
 
Thanks for the help! This works as desired. One additional question: Is
there a way to put the total in the report header, rather than the footer? I
did try it, but it returns the total of the first BidNumber.
Thanks again!
Kevin

Duane Hookom said:
You can create a text box in your BidNumber header or footer section:
Name: txtTotSalesTax
Control Source:[SalesTaxAmount]
Running Sum: Over All
Visible: No
Then add a text box to your report footer section:
Control Source: =txtTotSalesTax

--
Duane Hookom
MS Access MVP


Kevin said:
I have a report that is generated based on criteria chosen in a
multi-select
list box form. I'm trying to summarize the form by displaying currency
totals on the form, but one field/control is not calculating accurate
totals.

Here is how the form is structured:
The report returns only one "ProjectName", which is accounted for in the
report header.
Under that, there can be more than one "BidNumber", based on what is
selected in the multi-select list box. The data related to this is
grouped
together, and within the "Bid" table is a field called "SalesTaxAmount".
Under that is the "ItemLabel" portion of the report, which contains the
detail.

The SalesTaxAmount field in the Bid table contains a fixed currency amount
that users enter, so there is only one value per BidNumber. I created a
control in the report named SalesTaxTotal, which is intended to add the
SalesTaxAmount for BidNumber 01, 02, 03, etc. In the control source I
have
the expression: =Sum([SalesTaxAmount]). The problem I'm having is that
it
is adding one SalesTaxAmount value for each record in the detail. For
example, if the value in SalesTaxAmount is $100.00, and there are 20
records,
the control SalesTaxTotal returns $2,000.00.

I suspect it may have something to do with the code in the multi-select
list
box form, so I have pasted a portion that at the end of this post. I'm no
VB
expert, but noticed that "Bid.SalesTaxAmount" exists in a couple
locations.
Any help with this would be greatly appreciated! Thanks!

Here is that code:
strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity, ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost, ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType" & _
" FROM (Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode) INNER
JOIN (Bid INNER JOIN (Customer INNER JOIN ((ItemDetail INNER JOIN Project
ON
ItemDetail.ProjectName = Project.ProjectName) INNER JOIN Item ON
(Item.ItemNumber = ItemDetail.ItemNumber) AND (Item.RoomNumber =
ItemDetail.RoomNumber) AND (Item.BidNumber = ItemDetail.BidNumber) AND
(Item.ProjectName = ItemDetail.ProjectName) AND (Project.ProjectName =
Item.ProjectName)) ON (Customer.GCName = Project.GCName) AND
(Customer.GCName
= Project.GCName)) ON (Project.ProjectName = Bid.ProjectName) AND
(Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.ProjectName))
ON
Product.ProductDescription = ItemDetail.ProductDescription" & _
" WHERE Project.ProjectName = '" & Me.cboProjectName & "' And
(Bid.BidNumber) in (" & strCriteria & ")" & _
" ORDER BY Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" - "" &
Item.ItemNumber, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference,
Product.ProductCode;"
Click to expand...
Click to expand...
 
You could try place a text box in the Report Header with a control source
of:
Control Source: =txtNameOfControlInReportFooter

--
Duane Hookom
MS Access MVP
--

Kevin said:
Thanks for the help! This works as desired. One additional question: Is
there a way to put the total in the report header, rather than the footer?
I
did try it, but it returns the total of the first BidNumber.
Thanks again!
Kevin

Duane Hookom said:
You can create a text box in your BidNumber header or footer section:
Name: txtTotSalesTax
Control Source:[SalesTaxAmount]
Running Sum: Over All
Visible: No
Then add a text box to your report footer section:
Control Source: =txtTotSalesTax

--
Duane Hookom
MS Access MVP


Kevin said:
I have a report that is generated based on criteria chosen in a
multi-select
list box form. I'm trying to summarize the form by displaying currency
totals on the form, but one field/control is not calculating accurate
totals.

Here is how the form is structured:
The report returns only one "ProjectName", which is accounted for in
the
report header.
Under that, there can be more than one "BidNumber", based on what is
selected in the multi-select list box. The data related to this is
grouped
together, and within the "Bid" table is a field called
"SalesTaxAmount".
Under that is the "ItemLabel" portion of the report, which contains the
detail.

The SalesTaxAmount field in the Bid table contains a fixed currency
amount
that users enter, so there is only one value per BidNumber. I created
a
control in the report named SalesTaxTotal, which is intended to add the
SalesTaxAmount for BidNumber 01, 02, 03, etc. In the control source I
have
the expression: =Sum([SalesTaxAmount]). The problem I'm having is
that
it
is adding one SalesTaxAmount value for each record in the detail. For
example, if the value in SalesTaxAmount is $100.00, and there are 20
records,
the control SalesTaxTotal returns $2,000.00.

I suspect it may have something to do with the code in the multi-select
list
box form, so I have pasted a portion that at the end of this post. I'm
no
VB
expert, but noticed that "Bid.SalesTaxAmount" exists in a couple
locations.
Any help with this would be greatly appreciated! Thanks!

Here is that code:
strSQL = "SELECT Project.ProjectName, Bid.BidNumber, Item.RoomNumber &
"" -
"" & Item.ItemNumber AS ItemLabel, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference, Item.RoomName, Item.ElevationReference,
Item.ProductSummary, ItemDetail.Quantity,
ItemDetail.ProductDescription,
Product.UnitCost, ItemDetail.QuoteCost, Product.UOM,
[Quantity]*([UnitCost]+[QuoteCost]) AS LineTotalCost,
ItemDetail.Markup,
[LineTotalCost]*[Markup] AS SellPrice, Project.SalesTax,
Bid.[Engineering%],
Bid.SalesTaxAmount, Bid.BidDate, Bid.Estimator, Project.GCName,
ItemDetail.ItemDetailNotes, Bid.SalesTaxAmount, Project.GCContact,
Bid.PriceIncludes, Bid.PriceDoesNotInclude, Customer.GCStreetAddress,
[GCCity] & "" , "" & [GCState] & "" "" & [GCZip] AS CityState,
Bid.ScopeNotes, Bid.BidType" & _
" FROM (Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode)
INNER
JOIN (Bid INNER JOIN (Customer INNER JOIN ((ItemDetail INNER JOIN
Project
ON
ItemDetail.ProjectName = Project.ProjectName) INNER JOIN Item ON
(Item.ItemNumber = ItemDetail.ItemNumber) AND (Item.RoomNumber =
ItemDetail.RoomNumber) AND (Item.BidNumber = ItemDetail.BidNumber) AND
(Item.ProjectName = ItemDetail.ProjectName) AND (Project.ProjectName =
Item.ProjectName)) ON (Customer.GCName = Project.GCName) AND
(Customer.GCName
= Project.GCName)) ON (Project.ProjectName = Bid.ProjectName) AND
(Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName =
Item.ProjectName))
ON
Product.ProductDescription = ItemDetail.ProductDescription" & _
" WHERE Project.ProjectName = '" & Me.cboProjectName & "' And
(Bid.BidNumber) in (" & strCriteria & ")" & _
" ORDER BY Project.ProjectName, Bid.BidNumber, Item.RoomNumber & "" -
"" &
Item.ItemNumber, Item.RoomNumber, Item.ItemNumber,
Product.LibraryReference,
Product.ProductCode;"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top