|
Home > Archive > MS Access project with SQL Server > June 2005 > Grand Totals
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| mistux 2005-06-18, 8:24 pm |
|
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 vOpenOrderWeightFeet
Projection.Customer,
vOpenOrderWeightFeet
Projection.[Shop Order],
vOpenOrderWeightFeet
Projection.[Due Date],
vOpenOrderWeightFeet
Projection.Revision,
vOpenOrderWeightFeet
Projection.ComboCustPartNum,
==> Sum(vOpenOrderWeight
FeetProjection.[Total Pounds]) AS & #91;SumOfTotal_Pound
s], <==**
Sum(vOpenOrderWeight
FeetProjection.[Total Feet]) AS & #91;SumOfTotal_Feet]
,
& #91;T_SetupSheetHist
oryMaterialDetail].RawMatComponentID,
& #91;T_MaterialCompon
ents].MatDesc,
& #91;T_SetupSheetHist
oryCombinationsDetai
l].LetDown,
(& #91;T_SetupSheetHist
oryCombinationsDetai
l].[Letdown]/ 100)*Sum(vOpenOrderW
eightFeetProjection.[Total Pounds]) AS ColorantLbs,
& #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID,
& #91;T_MaterialCompon
ents_2].MatDesc as MatDesc2,
& #91;T_SetupSheetHist
oryMaterialDetail].AddLetDown,
([AddLetDown]/ 100)*Sum(vOpenOrderW
eightFeetProjection.[Total Pounds]) AS AddtvLbs,
& #91;T_SetupSheetHist
oryMaterialDetail].AddMatComponentID,
& #91;T_MaterialCompon
ents_1].MatDesc as MatDesc1,
& #91;T_SetupSheetHist
oryMaterialDetail].PiecesPerCartn,
/* IIf(& #91;PiecesPerCartn]=
0,0,[SumOfTotal Feet]/& #91;PiecesPerCartn])
AS PkgFt, */
(
CASE
WHEN [PiecesPerCartn] =0
THEN 0
ELSE Sum(vOpenOrderWeight
FeetProjection.[Total Feet])/[PiecesPerCartn]
END
) AS PkgFt,
& #91;T_SetupSheetHist
oryMaterialDetail].PackageID,
& #91;T_MaterialCompon
ents_4].MatDesc as MatDesc4
FROM (vOpenOrderWeightFee
tProjection
INNER JOIN (((((& #91;T_SetupSheetHist
oryMaterialDetail]
INNER JOIN & #91;T_SetupSheetHist
oryCombinationsDetai
l]
ON (& #91;T_SetupSheetHist
oryMaterialDetail].WOIDSub = & #91;T_SetupSheetHist
oryCombinationsDetai
l].WOIDSub)
AND (& #91;T_SetupSheetHist
oryMaterialDetail].WOID = & #91;T_SetupSheetHist
oryCombinationsDetai
l].WOID))
LEFT JOIN & #91;T_MaterialCompon
ents]
ON & #91;T_SetupSheetHist
oryMaterialDetail].RawMatComponentID = & #91;T_MaterialCompon
ents].ComponentID)
LEFT JOIN & #91;T_MaterialCompon
ents] AS & #91;T_MaterialCompon
ents_1]
ON & #91;T_SetupSheetHist
oryMaterialDetail].AddMatComponentID = & #91;T_MaterialCompon
ents_1].ComponentID)
LEFT JOIN & #91;T_MaterialCompon
ents] AS & #91;T_MaterialCompon
ents_4]
ON & #91;T_SetupSheetHist
oryMaterialDetail].PackageID = & #91;T_MaterialCompon
ents_4].ComponentID)
LEFT JOIN & #91;T_MaterialCompon
ents] AS & #91;T_MaterialCompon
ents_2]
ON & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID = & #91;T_MaterialCompon
ents_2].ComponentID)
ON (vOpenOrderWeightFee
tProjection.WOIDSub = & #91;T_SetupSheetHist
oryMaterialDetail].WOIDSub)
AND (vOpenOrderWeightFee
tProjection.Revision = & #91;T_SetupSheetHist
oryMaterialDetail].Revision)
AND (vOpenOrderWeightFee
tProjection.ComboCustPartNum = & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComboCustPartNum)
AND (vOpenOrderWeightFee
tProjection.[Shop Order] = & #91;T_SetupSheetHist
oryMaterialDetail].WOID))
LEFT JOIN & #91;T_SetupSheetKitM
aster]
ON & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComboCustPartNum = & #91;T_SetupSheetKitM
aster].KitItemPartNum
WHERE (((& #91;T_SetupSheetKitM
aster].KitItemPartNum) Is Null))
GROUP BY vOpenOrderWeightFeet
Projection.Customer,
vOpenOrderWeightFeet
Projection.[Shop Order],
vOpenOrderWeightFeet
Projection.[Due Date],
vOpenOrderWeightFeet
Projection.Revision,
vOpenOrderWeightFeet
Projection.ComboCustPartNum,
& #91;T_SetupSheetHist
oryMaterialDetail].RawMatComponentID,
& #91;T_MaterialCompon
ents].MatDesc,
& #91;T_SetupSheetHist
oryCombinationsDetai
l].LetDown,
& #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID,
& #91;T_MaterialCompon
ents_2].MatDesc,
& #91;T_SetupSheetHist
oryMaterialDetail].AddLetDown,
& #91;T_SetupSheetHist
oryMaterialDetail].AddMatComponentID,
& #91;T_MaterialCompon
ents_1].MatDesc,
& #91;T_SetupSheetHist
oryMaterialDetail].PiecesPerCartn,
& #91;T_SetupSheetHist
oryMaterialDetail].PackageID,
& #91;T_MaterialCompon
ents_4].MatDesc
HAVING (((& #91;T_SetupSheetHist
oryMaterialDetail]. RawMatComponentID)=1
044))
OR (((& #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID)=1044))
OR (((& #91;T_SetupSheetHist
oryMaterialDetail]. AddMatComponentID)=1
044))
OR(((& #91;T_SetupSheetHist
oryMaterialDetail].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
| |
| Graham R Seach 2005-06-18, 8:24 pm |
| 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 vOpenOrderWeightFeet
Projection.Customer,
vOpenOrderWeightFeet
Projection.[Shop Order],
vOpenOrderWeightFeet
Projection.[Due Date],
vOpenOrderWeightFeet
Projection.Revision,
vOpenOrderWeightFeet
Projection.ComboCustPartNum,
@MyTotal = Sum(vOpenOrderWeight
FeetProjection.[Total Pounds]) AS
& #91;SumOfTotal_Pound
s],
...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" <mistux.1qudyt@NoSpamPleaze.com> wrote in message
news:mistux.1qudyt@NoSpamPleaze.com...
>
> 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 vOpenOrderWeightFeet
Projection.Customer,
> vOpenOrderWeightFeet
Projection.[Shop Order],
> vOpenOrderWeightFeet
Projection.[Due Date],
> vOpenOrderWeightFeet
Projection.Revision,
> vOpenOrderWeightFeet
Projection.ComboCustPartNum,
> ==> Sum(vOpenOrderWeight
FeetProjection.[Total Pounds]) AS
> & #91;SumOfTotal_Pound
s], <==**
> Sum(vOpenOrderWeight
FeetProjection.[Total Feet]) AS & #91;SumOfTotal_Feet]
,
> & #91;T_SetupSheetHist
oryMaterialDetail].RawMatComponentID,
> & #91;T_MaterialCompon
ents].MatDesc,
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].LetDown,
>
> (& #91;T_SetupSheetHist
oryCombinationsDetai
l].[Letdown]/ 100)*Sum(vOpenOrderW
eightFeetProjection.[Total
> Pounds]) AS ColorantLbs,
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID,
> & #91;T_MaterialCompon
ents_2].MatDesc as MatDesc2,
> & #91;T_SetupSheetHist
oryMaterialDetail].AddLetDown,
> ([AddLetDown]/ 100)*Sum(vOpenOrderW
eightFeetProjection.[Total Pounds]) AS
> AddtvLbs,
> & #91;T_SetupSheetHist
oryMaterialDetail].AddMatComponentID,
> & #91;T_MaterialCompon
ents_1].MatDesc as MatDesc1,
> & #91;T_SetupSheetHist
oryMaterialDetail].PiecesPerCartn,
> /* IIf(& #91;PiecesPerCartn]=
0,0,[SumOfTotal Feet]/& #91;PiecesPerCartn])
AS PkgFt,
> */
> (
> CASE
> WHEN [PiecesPerCartn] =0
> THEN 0
> ELSE Sum(vOpenOrderWeight
FeetProjection.[Total
> Feet])/[PiecesPerCartn]
> END
> ) AS PkgFt,
> & #91;T_SetupSheetHist
oryMaterialDetail].PackageID,
> & #91;T_MaterialCompon
ents_4].MatDesc as MatDesc4
> FROM (vOpenOrderWeightFee
tProjection
> INNER JOIN (((((& #91;T_SetupSheetHist
oryMaterialDetail]
> INNER JOIN & #91;T_SetupSheetHist
oryCombinationsDetai
l]
> ON (& #91;T_SetupSheetHist
oryMaterialDetail].WOIDSub =
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].WOIDSub)
> AND (& #91;T_SetupSheetHist
oryMaterialDetail].WOID =
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].WOID))
> LEFT JOIN & #91;T_MaterialCompon
ents]
> ON & #91;T_SetupSheetHist
oryMaterialDetail].RawMatComponentID =
> & #91;T_MaterialCompon
ents].ComponentID)
> LEFT JOIN & #91;T_MaterialCompon
ents] AS & #91;T_MaterialCompon
ents_1]
> ON & #91;T_SetupSheetHist
oryMaterialDetail].AddMatComponentID =
> & #91;T_MaterialCompon
ents_1].ComponentID)
> LEFT JOIN & #91;T_MaterialCompon
ents] AS & #91;T_MaterialCompon
ents_4]
> ON & #91;T_SetupSheetHist
oryMaterialDetail].PackageID =
> & #91;T_MaterialCompon
ents_4].ComponentID)
> LEFT JOIN & #91;T_MaterialCompon
ents] AS & #91;T_MaterialCompon
ents_2]
> ON & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID =
> & #91;T_MaterialCompon
ents_2].ComponentID)
> ON (vOpenOrderWeightFee
tProjection.WOIDSub =
> & #91;T_SetupSheetHist
oryMaterialDetail].WOIDSub)
> AND (vOpenOrderWeightFee
tProjection.Revision =
> & #91;T_SetupSheetHist
oryMaterialDetail].Revision)
> AND (vOpenOrderWeightFee
tProjection.ComboCustPartNum =
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComboCustPartNum)
> AND (vOpenOrderWeightFee
tProjection.[Shop Order] =
> & #91;T_SetupSheetHist
oryMaterialDetail].WOID))
> LEFT JOIN & #91;T_SetupSheetKitM
aster]
> ON & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComboCustPartNum =
> & #91;T_SetupSheetKitM
aster].KitItemPartNum
> WHERE (((& #91;T_SetupSheetKitM
aster].KitItemPartNum) Is Null))
> GROUP BY vOpenOrderWeightFeet
Projection.Customer,
> vOpenOrderWeightFeet
Projection.[Shop Order],
> vOpenOrderWeightFeet
Projection.[Due Date],
> vOpenOrderWeightFeet
Projection.Revision,
> vOpenOrderWeightFeet
Projection.ComboCustPartNum,
> & #91;T_SetupSheetHist
oryMaterialDetail].RawMatComponentID,
> & #91;T_MaterialCompon
ents].MatDesc,
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].LetDown,
> & #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID,
> & #91;T_MaterialCompon
ents_2].MatDesc,
> & #91;T_SetupSheetHist
oryMaterialDetail].AddLetDown,
> & #91;T_SetupSheetHist
oryMaterialDetail].AddMatComponentID,
> & #91;T_MaterialCompon
ents_1].MatDesc,
> & #91;T_SetupSheetHist
oryMaterialDetail].PiecesPerCartn,
> & #91;T_SetupSheetHist
oryMaterialDetail].PackageID,
> & #91;T_MaterialCompon
ents_4].MatDesc
> HAVING (((& #91;T_SetupSheetHist
oryMaterialDetail]. RawMatComponentID)=1
044))
> OR (((& #91;T_SetupSheetHist
oryCombinationsDetai
l].ComponentID)=1044))
> OR (((& #91;T_SetupSheetHist
oryMaterialDetail]. AddMatComponentID)=1
044))
> OR(((& #91;T_SetupSheetHist
oryMaterialDetail].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
>
| |
| Razvan Socol 2005-06-21, 7:25 am |
| Graham R Seach wrote:
> just declare a local variabe and assign its value during the SELECT state=
ment.
> DECLARE @MyTotal INT
> SELECT vOpenOrderWeightFeet
Projection=AD.Customer,
> ...
> @MyTotal =3D Sum(vOpenOrderWeight
FeetProjec=ADtion.[Total Pounds]) AS
> & #91;SumOfTotal_Pound
s],
> ...etc
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: "=3DSum([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 "=3D0"
(if the RecordCount=3D0). 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 =3D CurrentProject.AccessConnection.Execute("SELECT
SUM(SumOfTotal_Pound
s) AS GrandTotal FROM TheView")!GrandTotal
Razvan
| |
|
|
|
|
| Razvan Socol 2005-06-23, 3:24 am |
| > I am actually usign a sp instead of a view since I am passing
> a paramter. [...] Why is it best to use a UDF?
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=2E 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=2E 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 =3D CurrentProject.AccessConnectio=ADn.Execute("MySP
@MyParamter=3D" & me. MyPastToItParameter)
!GrandT=ADotal
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=ADn. ProcedureName(FirstP
arameter,
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 =3D CurrentProject.AccessConnection
cmd.CommandType =3D adCmdStoredProc
cmd.CommandText =3D "ProcedureName"
cmd("@ParameterName") =3D ParameterValue
Set rst =3D cmd.Execute
Razvan
| |
| Graham R Seach 2005-06-27, 9:24 am |
| Oops, of course you're right. Thanks.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
"Razvan Socol" <rsocol@gmail.com> wrote in message
news:1119349990.185724.253590@g14g2000cwa.googlegroups.com...
Graham R Seach wrote:
> just declare a local variabe and assign its value during the SELECT
> statement.
> DECLARE @MyTotal INT
> SELECT vOpenOrderWeightFeet
Projection_.Customer,
> ...
> @MyTotal = Sum(vOpenOrderWeight
FeetProjec_tion.[Total Pounds]) AS
> & #91;SumOfTotal_Pound
s],
> ...etc
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_Pound
s) AS GrandTotal FROM TheView")!GrandTotal
Razvan
|
|
|
|
|