Group By Totals in Regular Query?

L

Leslie Coover

Given the following table called tblAlpha:

ID = A,A,B,B,C
Num = 1,2,4,3,5

Where the Data type for ID is text and the Data type for Num is Number;
Field size = Double

qryRegular is based on tblAlpha, first column contains ID, next column
contains Num.

In order to do other calculations I want to place the sum of all As in the
third column adjacent to each "A" ID and sum of all Bs adjacent to each "B"
ID etc.

I create a Totals Query:

SELECT [tblAlpha].[ID], Sum([tblAlpha].[Num]) As SumOfNum FROM tblAlpha
GROUP BY [tblAlpha].[ID];

But how can I put the results into qryRegular?

Here is my attempt:

3rd column qryRegular Field = SumNum_:SumNum([ID])

VBA (in module)

Function SumNum(ID As String)
Dim db As Database, rec As Recordset
Dim strSQL As String
Dim Value1 As Double
strSQL = "SELECT * FROM tblAlpha {what goes here??}

Do Until EOF

{what goes here ?}

Loop
SumNum = Value1
rec.Close
End Function

Perhaps there is an easier way than to use VBA, but I have to do some other
stuff, including finding yield to maturity on bonds given price and coupon,
so I thought if I get a general pattern of the VBA code for this simple
routine it might help later on.

Any help appreciated.
 
M

Marshall Barton

Leslie said:
Given the following table called tblAlpha:

ID = A,A,B,B,C
Num = 1,2,4,3,5

Where the Data type for ID is text and the Data type for Num is Number;
Field size = Double

qryRegular is based on tblAlpha, first column contains ID, next column
contains Num.

In order to do other calculations I want to place the sum of all As in the
third column adjacent to each "A" ID and sum of all Bs adjacent to each "B"
ID etc.

I create a Totals Query:

SELECT [tblAlpha].[ID], Sum([tblAlpha].[Num]) As SumOfNum FROM tblAlpha
GROUP BY [tblAlpha].[ID];

But how can I put the results into qryRegular?

Here is my attempt:

3rd column qryRegular Field = SumNum_:SumNum([ID])

VBA (in module)

Function SumNum(ID As String)
Dim db As Database, rec As Recordset
Dim strSQL As String
Dim Value1 As Double
strSQL = "SELECT * FROM tblAlpha {what goes here??}

Do Until EOF

{what goes here ?}

Loop
SumNum = Value1
rec.Close
End Function

Perhaps there is an easier way than to use VBA, but I have to do some other
stuff, including finding yield to maturity on bonds given price and coupon,
so I thought if I get a general pattern of the VBA code for this simple
routine it might help later on.
Click to expand...


There are several common ways to approach this. One is to
use a subquery:

SELECT *,
(SELECT Sum(X.Num
FROM tblAlpha As X
WHERE X.ID = tblAlpha.ID
) As SumOfNum
FROM tblAlpha

But that may cause trouble in a report where subqueries can
become entangled with the report's grouping and
aggregations. So the simple modification to the above is to
use a Domain Aggregate function instead of a subquery:

SELECT *,
DSum("Num", "tblAlpha",
"ID = " & tblAlpha.ID) As SumOfNum
FROM tblAlpha

But, that may have significant performance drawbacks.

A third way is to join your totals query back to the base
query:

SELECT tblAlpha.*,
qryTotals.SumOfNum
FROM tblAlpha INNER JOIN qryTotals
ON qryTotals.ID = tblAlpha.ID

I strongly suspect that this will provide the best
performance and the most flexibility for dealing with your
follow-on issues.

It is not at all clear to me why you would want to do any of
this in a VBA procedure so I can't comment on that part of
your question.
 
L

Leslie Coover

Marshall Barton said:
Leslie said:
Given the following table called tblAlpha:

ID = A,A,B,B,C
Num = 1,2,4,3,5

Where the Data type for ID is text and the Data type for Num is Number;
Field size = Double

qryRegular is based on tblAlpha, first column contains ID, next column
contains Num.

In order to do other calculations I want to place the sum of all As in the
third column adjacent to each "A" ID and sum of all Bs adjacent to each
"B"
ID etc.

I create a Totals Query:

SELECT [tblAlpha].[ID], Sum([tblAlpha].[Num]) As SumOfNum FROM tblAlpha
GROUP BY [tblAlpha].[ID];

But how can I put the results into qryRegular?

Here is my attempt:

3rd column qryRegular Field = SumNum_:SumNum([ID])

VBA (in module)

Function SumNum(ID As String)
Dim db As Database, rec As Recordset
Dim strSQL As String
Dim Value1 As Double
strSQL = "SELECT * FROM tblAlpha {what goes here??}

Do Until EOF

{what goes here ?}

Loop
SumNum = Value1
rec.Close
End Function

Perhaps there is an easier way than to use VBA, but I have to do some
other
stuff, including finding yield to maturity on bonds given price and
coupon,
so I thought if I get a general pattern of the VBA code for this simple
routine it might help later on.
Click to expand...


There are several common ways to approach this. One is to
use a subquery:

SELECT *,
(SELECT Sum(X.Num
FROM tblAlpha As X
WHERE X.ID = tblAlpha.ID
) As SumOfNum
FROM tblAlpha

But that may cause trouble in a report where subqueries can
become entangled with the report's grouping and
aggregations. So the simple modification to the above is to
use a Domain Aggregate function instead of a subquery:

SELECT *,
DSum("Num", "tblAlpha",
"ID = " & tblAlpha.ID) As SumOfNum
FROM tblAlpha

But, that may have significant performance drawbacks.

A third way is to join your totals query back to the base
query:

SELECT tblAlpha.*,
qryTotals.SumOfNum
FROM tblAlpha INNER JOIN qryTotals
ON qryTotals.ID = tblAlpha.ID

I strongly suspect that this will provide the best
performance and the most flexibility for dealing with your
follow-on issues.

It is not at all clear to me why you would want to do any of
this in a VBA procedure so I can't comment on that part of
your question.
Click to expand...

Thanks March, I'll give your third option a try.

I want to understand more about the VBA routine becasue I need to calculate
yield to maturity on bonds (as part of my query). The only way I know how
to do this is to construct a loop so that it increments until the price
(given some discount rate) is very close to the actual price, then extract
the discount rate. There are Excel functions that do this very easily, but
they won't work in Access. Any suggestions?
 
M

Marshall Barton

Leslie said:
Leslie said:
Given the following table called tblAlpha:

ID = A,A,B,B,C
Num = 1,2,4,3,5

Where the Data type for ID is text and the Data type for Num is Number;
Field size = Double

qryRegular is based on tblAlpha, first column contains ID, next column
contains Num.

In order to do other calculations I want to place the sum of all As in the
third column adjacent to each "A" ID and sum of all Bs adjacent to each
"B"
ID etc.

I create a Totals Query:

SELECT [tblAlpha].[ID], Sum([tblAlpha].[Num]) As SumOfNum FROM tblAlpha
GROUP BY [tblAlpha].[ID];

But how can I put the results into qryRegular?

Here is my attempt:

3rd column qryRegular Field = SumNum_:SumNum([ID])

VBA (in module)

Function SumNum(ID As String)
Dim db As Database, rec As Recordset
Dim strSQL As String
Dim Value1 As Double
strSQL = "SELECT * FROM tblAlpha {what goes here??}

Do Until EOF

{what goes here ?}

Loop
SumNum = Value1
rec.Close
End Function

Perhaps there is an easier way than to use VBA, but I have to do some
other
stuff, including finding yield to maturity on bonds given price and
coupon,
so I thought if I get a general pattern of the VBA code for this simple
routine it might help later on.
Click to expand...


There are several common ways to approach this. One is to
use a subquery:

SELECT *,
(SELECT Sum(X.Num
FROM tblAlpha As X
WHERE X.ID = tblAlpha.ID
) As SumOfNum
FROM tblAlpha

But that may cause trouble in a report where subqueries can
become entangled with the report's grouping and
aggregations. So the simple modification to the above is to
use a Domain Aggregate function instead of a subquery:

SELECT *,
DSum("Num", "tblAlpha",
"ID = " & tblAlpha.ID) As SumOfNum
FROM tblAlpha

But, that may have significant performance drawbacks.

A third way is to join your totals query back to the base
query:

SELECT tblAlpha.*,
qryTotals.SumOfNum
FROM tblAlpha INNER JOIN qryTotals
ON qryTotals.ID = tblAlpha.ID

I strongly suspect that this will provide the best
performance and the most flexibility for dealing with your
follow-on issues.

It is not at all clear to me why you would want to do any of
this in a VBA procedure so I can't comment on that part of
your question.
Click to expand...

Thanks March, I'll give your third option a try.

I want to understand more about the VBA routine becasue I need to calculate
yield to maturity on bonds (as part of my query). The only way I know how
to do this is to construct a loop so that it increments until the price
(given some discount rate) is very close to the actual price, then extract
the discount rate. There are Excel functions that do this very easily, but
they won't work in Access.
Click to expand...


I'm not a financial kind of guy, so I don't have any real
ideas ;-) Since looping through records is almost never
necessary, I would hope that there would be a mathematical
expression for that kind of thing, but I have no idea what
it would be.

If you really must loop through the records, be sure to use
an appropriate WHERE clause in the SQL statement. Then open
a recordset:

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
Do Until rs.EOF
' do your calculations here
Loop
rs.Close : Set rs = Nothing
Set db = Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Column Totals in CrossTab Query 1
Group by SUM 1
Group by SUM 3
Update Query; Find Correct ID and Update Value in Correct Field 1
Median Calculation, Dates, & Group By 1
Crosstab Query by Week with Dates as Headers 5
VBA with userform in excel 5
Using a Public Variable in a Query - Passing Through a Function 3

Top