Product Function

[aqif]

Hi

I am trying to search a function Like Product which will multiply all the values in a field. Like

ID Amount
1 2
2 3
3 4

Product=2*3*4=24

Is there any function like this which will do the above job ?

Cheers!
Aqif

 

[shacket]

How about using VBA to loop through your records and multiply each successive value?

 

[aqif]

Hi

Actually my original problem is to get smthng like that

ID Product
-- -------
1 1
2 2
3 6
4 24
n so on....

I tried to use query like
SELECT e.ID, e.Name, (Select CInt(Exp(Sum(Log(Id)))) From TblCount
where id<e.id ) AS Product FROM TblCount AS e;
But i didn't worked saying datatype mismatch in criteria expression... Although When i used a query to get running count it easily executed like

SELECT e.ID, e.Name, (Select count(*) From TblCount Where ID>e.Id)AS Left FROM TblCount AS e;


ID Name Left
1 A 5
2 B 4
3 C 3
4 D 2
5 E 1
6 F 0

any suggestions?

Cheers!
Aqif

 

[shacket]

What is your starting value? How many rows do you want? Are you filling in values for records that already exist, or creating new ones?

I have to admit I don't quite get why you would want to do something like that. Let me know and maybe something will come to me!

 

[Joey C]

Hi Aqif:

Try the following function:


Function Prod(FieldName As String, TableQuery As String, Criteria As String)

‘This function returns a running product of the values in column [FieldName] located in the source table or query [TableQuery]


Dim db As Database
Dim rst As Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT " & FieldName & " FROM " & TableQuery & " WHERE " & Criteria)

Prod = 1
Do While Not rst.EOF
Prod = Prod * rst.Fields(0)
rst.MoveNext
Loop

End Function


Use the function like this in your QBE grid:

Product: Prod("FieldName","TableQuery","Autonumber < " & [Autonumber]+1)

Note: the table or query you´re using as a source (in this case “TableQuery”) MUST contain a unique index, and this field must be included in your query, the function uses this index (in this case field “Autonumber”) to loop through the recordset

Hope this helps.

Joey C.

 

[aqif]

HI

I managed to debug my query n now it works fine:

SELECT e.ID, e.Name, (Select CInt(Exp(Sum(Log(Id)))) From TblCount
where id<=e.id ) AS Product FROM TblCount AS e;

Anyway thaxx everyone for thier help which made me able to do that thing.

Cheers!
Aqif