Sum certain items in a list

[Les Coover]

There is a table named tblBasis that has
the number of shares purchased for each investment
over a period of time. Some investments
may contains several entries in the
Number_Shares field since more than
one purchase of the same stock occurs.

I want to extract the total sum of shares
for each investment.

It tried the following but it is not correct.
I can easily do this with a query, but my
objective is to create a from that has
totals for each investment with a subform
that lists the specific investments over
a time period. When I tried to make
a form and subform based on the query
it did not work. Any advice welcomed.

Dim Number_Shares As Double

'Calculate market value for each investment

Function CMV(Market_Symbol As String, Current_Mkt_Price As Currency) As
Currency
Dim db As Database, rec As Recordset, myAcum As Currency
Set db = CurrentDb()
Set rec = db.OpenRecordset("tblBasis", dbOpenDynaset)
rec.MoveFirst
myAcum = 0
Do Until rec.EOF
If Market_Symbol = Market_Symbol Then
myAcum = Number_Shares + myAcum
End If
rec.MoveNext
Loop
CMV = myAcum
End Function

 

[Marshall Barton]

There is a table named tblBasis that has
the number of shares purchased for each investment
over a period of time. Some investments
may contains several entries in the
Number_Shares field since more than
one purchase of the same stock occurs.

I want to extract the total sum of shares
for each investment.

It tried the following but it is not correct.
I can easily do this with a query, but my
objective is to create a from that has
totals for each investment with a subform
that lists the specific investments over
a time period. When I tried to make
a form and subform based on the query
it did not work. Any advice welcomed.

Dim Number_Shares As Double

'Calculate market value for each investment

Function CMV(Market_Symbol As String, Current_Mkt_Price As Currency) As
Currency
Dim db As Database, rec As Recordset, myAcum As Currency
Set db = CurrentDb()
Set rec = db.OpenRecordset("tblBasis", dbOpenDynaset)
rec.MoveFirst
myAcum = 0
Do Until rec.EOF
If Market_Symbol = Market_Symbol Then
myAcum = Number_Shares + myAcum
End If
rec.MoveNext
Loop
CMV = myAcum
End Function

To refer to a field in a recordset you need to reference the
recordset and the field name. E.g. rec!Number_Shares

BUT, why are you using code for this? A Totals type query
should be the starting point for this:

SELECT Market_Symbol, Sum(Number_Shares) AS TotalShares
FROM tblBasis
GROUP BY Market_Symbol

 

[Les Coover]

To refer to a field in a recordset you need to reference the
recordset and the field name. E.g. rec!Number_Shares

BUT, why are you using code for this? A Totals type query
should be the starting point for this:

SELECT Market_Symbol, Sum(Number_Shares) AS TotalShares
FROM tblBasis
GROUP BY Market_Symbol

Marsh, thanks for help.

frmBasis and subfrmBasis records have a
one-to-many relationship based on the common
field Market_Symbol. On the subfrmBasis
form footer I sum the number of shares.

=Sum([Number_Shares]) then on frmBasis
I include a calculation field

=[subfrmBasis].Form!TotalShares

This all works okay.
--------
Now the problem.

frmBuySell and subfrmBuySell also have
one-to-many relationship based on their common
field Market_Symbol. subfrmBuySell
is based on a long query that tracks Date_Purchased,
and Purchase_Price. Using "auxiliary total queries" I
calculate SumOfPurchase_Price, then calculate
Portfolio_Proportion. Other things are also
calculated, but you get the idea.

The problem is I can't use =Sum([Number_Shares]) as
I did in subfrmBasis because this operation must be done in
an auxiliary query in order to complete the calculations
necessary in the query (the query subfrmBuySell is based on).

The sum I want is right there in subfrmBuySell ( it is
called Current_Mkt_Val , in Datasheet view it repeats
for each instance of Market_Symbol), but I
want it on frmBuySell. I tried placing a calculated field
in the Form Footer of subfrmBuySell, naming it
CMV with control source =[Current_Mkt_Val]

Then on frmBuySell I placed a field named myCMV with
Control Sorce =[Forms]![subfrmBuySell]!CMV

I get a #Name? error. I tried several other things too, but I
always end up with #Name? error.

Any help greatly appreciated, thanks again, Les

 

[Les Coover]

To refer to a field in a recordset you need to reference the
recordset and the field name. E.g. rec!Number_Shares

BUT, why are you using code for this? A Totals type query
should be the starting point for this:

SELECT Market_Symbol, Sum(Number_Shares) AS TotalShares
FROM tblBasis
GROUP BY Market_Symbol

Marsh, thanks for help.

frmBasis and subfrmBasis records have a
one-to-many relationship based on the common
field Market_Symbol. On the subfrmBasis
form footer I sum the number of shares.

=Sum([Number_Shares]) then on frmBasis
I include a calculation field

=[subfrmBasis].Form!TotalShares

This all works okay.
--------
Now the problem.

frmBuySell and subfrmBuySell also have
one-to-many relationship based on their common
field Market_Symbol. subfrmBuySell
is based on a long query that tracks Date_Purchased,
and Purchase_Price. Using "auxiliary total queries" I
calculate SumOfPurchase_Price, then calculate
Portfolio_Proportion. Other things are also
calculated, but you get the idea.

The problem is I can't use =Sum([Number_Shares]) as
I did in subfrmBasis because this operation must be done in
an auxiliary query in order to complete the calculations
necessary in the query (the query subfrmBuySell is based on).

The sum I want is right there in subfrmBuySell ( it is
called Current_Mkt_Val , in Datasheet view it repeats
for each instance of Market_Symbol), but I
want it on frmBuySell. I tried placing a calculated field
in the Form Footer of subfrmBuySell, naming it
CMV with control source =[Current_Mkt_Val]

Then on frmBuySell I placed a field named myCMV with
Control Sorce =[Forms]![subfrmBuySell]!CMV

I get a #Name? error. I tried several other things too, but I
always end up with #Name? error.

Any help greatly appreciated, thanks again, Les

Marsh

The solution was very simple, I just made another totals query
and put it in the form as a second subform linked to the main form.

MS Access is very simple to use if one does not complicate it.

Thanks again for help.

Les