Grand Totals

M

mistux

I have the following stored procedure that shows me the total pounds o
material that I need per record. Now I want to be able to also kno
what the Grand Total of punds is.

I have indicated the line that give me the individual total.

I am actually going to be displaying this at the bottom of a subform i
Access (in Access when I was using an MDB to hold the data, the subfor
worked, but when I moved it to SQL it stops working)

Do I need to create a new sp to sum this sp or what?


Code
-------------------

SELECT vOpenOrderWeightFeetProjection.Customer,
vOpenOrderWeightFeetProjection.[Shop Order],
vOpenOrderWeightFeetProjection.[Due Date],
vOpenOrderWeightFeetProjection.Revision,
vOpenOrderWeightFeetProjection.ComboCustPartNum,
==> Sum(vOpenOrderWeightFeetProjection.[Total Pounds]) AS [SumOfTotal_Pounds], <==**
Sum(vOpenOrderWeightFeetProjection.[Total Feet]) AS [SumOfTotal_Feet],
[T_SetupSheetHistoryMaterialDetail].RawMatComponentID,
[T_MaterialComponents].MatDesc,
[T_SetupSheetHistoryCombinationsDetail].LetDown,
([T_SetupSheetHistoryCombinationsDetail].[Letdown]/100)*Sum(vOpenOrderWeightFeetProjection.[Total Pounds]) AS ColorantLbs,
[T_SetupSheetHistoryCombinationsDetail].ComponentID,
[T_MaterialComponents_2].MatDesc as MatDesc2,
[T_SetupSheetHistoryMaterialDetail].AddLetDown,
([AddLetDown]/100)*Sum(vOpenOrderWeightFeetProjection.[Total Pounds]) AS AddtvLbs,
[T_SetupSheetHistoryMaterialDetail].AddMatComponentID,
[T_MaterialComponents_1].MatDesc as MatDesc1,
[T_SetupSheetHistoryMaterialDetail].PiecesPerCartn,
/* IIf([PiecesPerCartn]=0,0,[SumOfTotal Feet]/[PiecesPerCartn]) AS PkgFt, */
(
CASE
WHEN [PiecesPerCartn] =0
THEN 0
ELSE Sum(vOpenOrderWeightFeetProjection.[Total Feet])/[PiecesPerCartn]
END
) AS PkgFt,
[T_SetupSheetHistoryMaterialDetail].PackageID,
[T_MaterialComponents_4].MatDesc as MatDesc4
FROM (vOpenOrderWeightFeetProjection
INNER JOIN ((((([T_SetupSheetHistoryMaterialDetail]
INNER JOIN [T_SetupSheetHistoryCombinationsDetail]
ON ([T_SetupSheetHistoryMaterialDetail].WOIDSub = [T_SetupSheetHistoryCombinationsDetail].WOIDSub)
AND ([T_SetupSheetHistoryMaterialDetail].WOID = [T_SetupSheetHistoryCombinationsDetail].WOID))
LEFT JOIN [T_MaterialComponents]
ON [T_SetupSheetHistoryMaterialDetail].RawMatComponentID = [T_MaterialComponents].ComponentID)
LEFT JOIN [T_MaterialComponents] AS [T_MaterialComponents_1]
ON [T_SetupSheetHistoryMaterialDetail].AddMatComponentID = [T_MaterialComponents_1].ComponentID)
LEFT JOIN [T_MaterialComponents] AS [T_MaterialComponents_4]
ON [T_SetupSheetHistoryMaterialDetail].PackageID = [T_MaterialComponents_4].ComponentID)
LEFT JOIN [T_MaterialComponents] AS [T_MaterialComponents_2]
ON [T_SetupSheetHistoryCombinationsDetail].ComponentID = [T_MaterialComponents_2].ComponentID)
ON (vOpenOrderWeightFeetProjection.WOIDSub = [T_SetupSheetHistoryMaterialDetail].WOIDSub)
AND (vOpenOrderWeightFeetProjection.Revision = [T_SetupSheetHistoryMaterialDetail].Revision)
AND (vOpenOrderWeightFeetProjection.ComboCustPartNum = [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum)
AND (vOpenOrderWeightFeetProjection.[Shop Order] = [T_SetupSheetHistoryMaterialDetail].WOID))
LEFT JOIN [T_SetupSheetKitMaster]
ON [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = [T_SetupSheetKitMaster].KitItemPartNum
WHERE ((([T_SetupSheetKitMaster].KitItemPartNum) Is Null))
GROUP BY vOpenOrderWeightFeetProjection.Customer,
vOpenOrderWeightFeetProjection.[Shop Order],
vOpenOrderWeightFeetProjection.[Due Date],
vOpenOrderWeightFeetProjection.Revision,
vOpenOrderWeightFeetProjection.ComboCustPartNum,
[T_SetupSheetHistoryMaterialDetail].RawMatComponentID,
[T_MaterialComponents].MatDesc,
[T_SetupSheetHistoryCombinationsDetail].LetDown,
[T_SetupSheetHistoryCombinationsDetail].ComponentID,
[T_MaterialComponents_2].MatDesc,
[T_SetupSheetHistoryMaterialDetail].AddLetDown,
[T_SetupSheetHistoryMaterialDetail].AddMatComponentID,
[T_MaterialComponents_1].MatDesc,
[T_SetupSheetHistoryMaterialDetail].PiecesPerCartn,
[T_SetupSheetHistoryMaterialDetail].PackageID,
[T_MaterialComponents_4].MatDesc
HAVING ((([T_SetupSheetHistoryMaterialDetail].RawMatComponentID)=1044))
OR ((([T_SetupSheetHistoryCombinationsDetail].ComponentID)=1044))
OR ((([T_SetupSheetHistoryMaterialDetail].AddMatComponentID)=1044))
OR((([T_SetupSheetHistoryMaterialDetail].PackageID)=1044))


--------------------
 
G

Graham R Seach

You haven't shown us the procedure declaration line, so we don't know what's
being returned from this procedure. But to answer your question, just
declare a local variabe and assign its value during the SELECT statement.

DECLARE @MyTotal INT

SELECT vOpenOrderWeightFeetProjection.Customer,
vOpenOrderWeightFeetProjection.[Shop Order],
vOpenOrderWeightFeetProjection.[Due Date],
vOpenOrderWeightFeetProjection.Revision,
vOpenOrderWeightFeetProjection.ComboCustPartNum,
@MyTotal = Sum(vOpenOrderWeightFeetProjection.[Total Pounds]) AS
[SumOfTotal_Pounds],
...etc

You can return @MyTotal as the procedure return value, or as one of its
arguments.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

mistux said:
I have the following stored procedure that shows me the total pounds of
material that I need per record. Now I want to be able to also know
what the Grand Total of punds is.

I have indicated the line that give me the individual total.

I am actually going to be displaying this at the bottom of a subform in
Access (in Access when I was using an MDB to hold the data, the subform
worked, but when I moved it to SQL it stops working)

Do I need to create a new sp to sum this sp or what?


Code:
--------------------

SELECT vOpenOrderWeightFeetProjection.Customer,
vOpenOrderWeightFeetProjection.[Shop Order],
vOpenOrderWeightFeetProjection.[Due Date],
vOpenOrderWeightFeetProjection.Revision,
vOpenOrderWeightFeetProjection.ComboCustPartNum,
==> Sum(vOpenOrderWeightFeetProjection.[Total Pounds]) AS
[SumOfTotal_Pounds], <==**
Sum(vOpenOrderWeightFeetProjection.[Total Feet]) AS [SumOfTotal_Feet],
[T_SetupSheetHistoryMaterialDetail].RawMatComponentID,
[T_MaterialComponents].MatDesc,
[T_SetupSheetHistoryCombinationsDetail].LetDown,

([T_SetupSheetHistoryCombinationsDetail].[Letdown]/100)*Sum(vOpenOrderWeightFeetProjection.[Total
Pounds]) AS ColorantLbs,
[T_SetupSheetHistoryCombinationsDetail].ComponentID,
[T_MaterialComponents_2].MatDesc as MatDesc2,
[T_SetupSheetHistoryMaterialDetail].AddLetDown,
([AddLetDown]/100)*Sum(vOpenOrderWeightFeetProjection.[Total Pounds]) AS
AddtvLbs,
[T_SetupSheetHistoryMaterialDetail].AddMatComponentID,
[T_MaterialComponents_1].MatDesc as MatDesc1,
[T_SetupSheetHistoryMaterialDetail].PiecesPerCartn,
/* IIf([PiecesPerCartn]=0,0,[SumOfTotal Feet]/[PiecesPerCartn]) AS PkgFt,
*/
(
CASE
WHEN [PiecesPerCartn] =0
THEN 0
ELSE Sum(vOpenOrderWeightFeetProjection.[Total
Feet])/[PiecesPerCartn]
END
) AS PkgFt,
[T_SetupSheetHistoryMaterialDetail].PackageID,
[T_MaterialComponents_4].MatDesc as MatDesc4
FROM (vOpenOrderWeightFeetProjection
INNER JOIN ((((([T_SetupSheetHistoryMaterialDetail]
INNER JOIN [T_SetupSheetHistoryCombinationsDetail]
ON ([T_SetupSheetHistoryMaterialDetail].WOIDSub =
[T_SetupSheetHistoryCombinationsDetail].WOIDSub)
AND ([T_SetupSheetHistoryMaterialDetail].WOID =
[T_SetupSheetHistoryCombinationsDetail].WOID))
LEFT JOIN [T_MaterialComponents]
ON [T_SetupSheetHistoryMaterialDetail].RawMatComponentID =
[T_MaterialComponents].ComponentID)
LEFT JOIN [T_MaterialComponents] AS [T_MaterialComponents_1]
ON [T_SetupSheetHistoryMaterialDetail].AddMatComponentID =
[T_MaterialComponents_1].ComponentID)
LEFT JOIN [T_MaterialComponents] AS [T_MaterialComponents_4]
ON [T_SetupSheetHistoryMaterialDetail].PackageID =
[T_MaterialComponents_4].ComponentID)
LEFT JOIN [T_MaterialComponents] AS [T_MaterialComponents_2]
ON [T_SetupSheetHistoryCombinationsDetail].ComponentID =
[T_MaterialComponents_2].ComponentID)
ON (vOpenOrderWeightFeetProjection.WOIDSub =
[T_SetupSheetHistoryMaterialDetail].WOIDSub)
AND (vOpenOrderWeightFeetProjection.Revision =
[T_SetupSheetHistoryMaterialDetail].Revision)
AND (vOpenOrderWeightFeetProjection.ComboCustPartNum =
[T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum)
AND (vOpenOrderWeightFeetProjection.[Shop Order] =
[T_SetupSheetHistoryMaterialDetail].WOID))
LEFT JOIN [T_SetupSheetKitMaster]
ON [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum =
[T_SetupSheetKitMaster].KitItemPartNum
WHERE ((([T_SetupSheetKitMaster].KitItemPartNum) Is Null))
GROUP BY vOpenOrderWeightFeetProjection.Customer,
vOpenOrderWeightFeetProjection.[Shop Order],
vOpenOrderWeightFeetProjection.[Due Date],
vOpenOrderWeightFeetProjection.Revision,
vOpenOrderWeightFeetProjection.ComboCustPartNum,
[T_SetupSheetHistoryMaterialDetail].RawMatComponentID,
[T_MaterialComponents].MatDesc,
[T_SetupSheetHistoryCombinationsDetail].LetDown,
[T_SetupSheetHistoryCombinationsDetail].ComponentID,
[T_MaterialComponents_2].MatDesc,
[T_SetupSheetHistoryMaterialDetail].AddLetDown,
[T_SetupSheetHistoryMaterialDetail].AddMatComponentID,
[T_MaterialComponents_1].MatDesc,
[T_SetupSheetHistoryMaterialDetail].PiecesPerCartn,
[T_SetupSheetHistoryMaterialDetail].PackageID,
[T_MaterialComponents_4].MatDesc
HAVING ((([T_SetupSheetHistoryMaterialDetail].RawMatComponentID)=1044))
OR ((([T_SetupSheetHistoryCombinationsDetail].ComponentID)=1044))
OR ((([T_SetupSheetHistoryMaterialDetail].AddMatComponentID)=1044))
OR((([T_SetupSheetHistoryMaterialDetail].PackageID)=1044))


--------------------


--
mistux
------------------------------------------------------------------------
mistux's Profile: http://www.officehelp.in/member.php?userid=107
View this thread: http://www.officehelp.in/showthread.php?t=675503
Visit - http://www.officehelp.in/archive/index.php |
http://www.officehelp.in/index/index.php
Click to expand...
 
R

Razvan Socol

Graham said:
just declare a local variabe and assign its value during the SELECT statement.
DECLARE @MyTotal INT
SELECT vOpenOrderWeightFeetProjection­.Customer,
...
@MyTotal = Sum(vOpenOrderWeightFeetProjec­tion.[Total Pounds]) AS
[SumOfTotal_Pounds],
...etc
Click to expand...

Hello Graham,

You should know that "A SELECT statement that contains a variable
assignment cannot also be used to perform normal result set retrieval
operations." (quote from Books Online). So what you wrote should be in
two separate SELECT statements.

However, this solution will not work if the procedure is used as the
RecordSource for the subform (because you have no way of retrieving the
procedure return value or the value of the output parameters).

The solution that I normally use in this case would be to compute the
grand total in the footer of the subform with a TextBox that has a
ControlSource like this: "=Sum([Total Pounds])". This solution is used
in the "Quarterly Orders" form in Northwind.MDB. One problem in this
solution is that if there are no rows in the subform, the TextBox shows
"#Error", so you have to check the number of rows after setting the
subform's RecordSource and set the ControlSource of the TextBox to "=0"
(if the RecordCount=0). Another problem with this solution (that
happens only with ADP-s) is that if the user applies a filter or a sort
criteria (using right-click or the Records menu), the Textbox also
shows "#Error" (and I did not find a workaround for this issue).

It would be better if this query is written in a View on the server
(instead of a Stored Procedure). Normally, Stored Procedures should be
used only if you need to change something in the database; for simple
SELECT statements, Views should be used; for a SELECT statement with
parameters it's best to use In-Line UDF-s; for multiple statements that
do not change anything in the database, use Multi-Statement UDF-s,
replacing temporary tables with table variables.

In this case, we also have another solution: executing the query for
the second time and put the result in the TextBox directly, using
something like this:
Me.txtGrandTotal = CurrentProject.AccessConnection.Execute("SELECT
SUM(SumOfTotal_Pounds) AS GrandTotal FROM TheView")!GrandTotal

Razvan
 
M

mistux

I am actually usign a sp instead of a view since I am passing
paramter. in my example I replaced the paramter with a hard coade
value of 1044. Why is it best to use a UDF
 
M

mistux

I ended up makeing a seperate view then a sp that sumed that view the
on the form set my totals field = to the field in the sp like yo
suggested:

Me.txtGrandTotal = CurrentProject.AccessConnection.Execute("MyS
@MyParamter=" & me.MyPastToItParameter)!GrandTotal

Thanks
 
R

Razvan Socol

I am actually usign a sp instead of a view since I am passing
a paramter. [...] Why is it best to use a UDF?
Click to expand...

It's better to use a UDF instead of a SP with parameters (if the SP
doesn't modify data), because it's easier to reuse the code in another
queries, by using the UDF in the FROM clause (if it's an In-Line UDF,
it behaves just like a view with parameters).

Quote from Books Online:
"A user-defined function that returns a table can also replace stored
procedures that return a single result set. The table returned by a
user-defined function can be referenced in the FROM clause of a
Transact-SQL statement, whereas stored procedures that return result
sets cannot."

Note: If you want to reuse the results of a stored procedure, it is
difficult, but possible: you can create a (temporary) table with the
exact columns returned by the SP and use the "INSERT INTO #TheTable
EXEC StoredProcedure" syntax. However, this is akward and has some
limitations (you cannot do the same trick twice).

Observations:
1. When you want to use a condition (that involves the columns in the
original tables) you should put it in the WHERE clause, not in the
HAVING clause. The HAVING clause should be used when the condition
involves an aggregate (like SUM, or COUNT) that is calculated on each
group. In some cases, using the HAVING clause may perform worse than
using the WHERE clause (because the WHERE clause is evaluated before
the GROUP BY and the HAVING clause may be evaluated after the GROUP
BY). In your case, there is no performance difference, but you should
get used to this, for readability reasons.

2. Concatenating a value in a SQL statement is considered bad practice,
because it can lead to SQL Injection. For example, in your statement:
Me.txtGrandTotal = CurrentProject.AccessConnectio­n.Execute("MySP
@MyParamter=" & me.MyPastToItParameter)!GrandT­otal
If the me.MyPastToItParameter would contain the string value "1 DELETE
SomeTable" (instead of just an integer), then after executing the
procedure, SQL Server will also delete all the rows from SomeTable.

This can be avoided by:
a) checking (and double-checking) the variables that will be
concatenated to contain only "correct" values; in your case, this could
be done by converting me.MyPastToItParameter to a integer value, using
the CLong function or a variable "as Long". If the parameter would be a
string value, it's enough (in most cases) to double the single-quotes
(but you have to be careful about the length of the string).

b) avoid concatenation, by calling the procedure in some other way: by
using a ADODB.Command object or by using this syntax:

Call CurrentProject.AccessConnectio­n.ProcedureName(FirstParameter,
SecondParameter, ...)

This syntax is useful only if the procedure does not return any rows
(or if you do not need the returned recordset). If you need the
returned recordset, you can use an ADODB.Command object like this:

Dim cmd As New ADODB.Command, rst As ADODB.Recordset
cmd.ActiveConnection = CurrentProject.AccessConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ProcedureName"
cmd("@ParameterName") = ParameterValue
Set rst = cmd.Execute

Razvan
 
G

Graham R Seach

Oops, of course you're right. Thanks.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham said:
just declare a local variabe and assign its value during the SELECT
statement.
DECLARE @MyTotal INT
SELECT vOpenOrderWeightFeetProjection­.Customer,
...
@MyTotal = Sum(vOpenOrderWeightFeetProjec­tion.[Total Pounds]) AS
[SumOfTotal_Pounds],
...etc
Click to expand...

Hello Graham,

You should know that "A SELECT statement that contains a variable
assignment cannot also be used to perform normal result set retrieval
operations." (quote from Books Online). So what you wrote should be in
two separate SELECT statements.

However, this solution will not work if the procedure is used as the
RecordSource for the subform (because you have no way of retrieving the
procedure return value or the value of the output parameters).

The solution that I normally use in this case would be to compute the
grand total in the footer of the subform with a TextBox that has a
ControlSource like this: "=Sum([Total Pounds])". This solution is used
in the "Quarterly Orders" form in Northwind.MDB. One problem in this
solution is that if there are no rows in the subform, the TextBox shows
"#Error", so you have to check the number of rows after setting the
subform's RecordSource and set the ControlSource of the TextBox to "=0"
(if the RecordCount=0). Another problem with this solution (that
happens only with ADP-s) is that if the user applies a filter or a sort
criteria (using right-click or the Records menu), the Textbox also
shows "#Error" (and I did not find a workaround for this issue).

It would be better if this query is written in a View on the server
(instead of a Stored Procedure). Normally, Stored Procedures should be
used only if you need to change something in the database; for simple
SELECT statements, Views should be used; for a SELECT statement with
parameters it's best to use In-Line UDF-s; for multiple statements that
do not change anything in the database, use Multi-Statement UDF-s,
replacing temporary tables with table variables.

In this case, we also have another solution: executing the query for
the second time and put the result in the TextBox directly, using
something like this:
Me.txtGrandTotal = CurrentProject.AccessConnection.Execute("SELECT
SUM(SumOfTotal_Pounds) AS GrandTotal FROM TheView")!GrandTotal

Razvan
 
Top