getting the product of column values

[alh1002]

I want to multiple all column values together given they before given date.

table A with values looks like
date/ value

and then there is a second table (B) with dates and values

I want to multiple all values in table A with dates earlier than a date in Table B, times the value of that given date in table B

I can't get anything to do what I want,

 

[CautionMP]

Sounds like the job for a custom function. You will need to update the [tt]Dates, Values, and tableA[/tt] in the following code to match the actual table/field names.

Function DProduct(TableBValue As Double, CutoffDate As Date) As Double
Dim conCurrent As ADODB.Connection
Dim rstLookup As New ADODB.Recordset
Dim dblOutput As Double
Dim sqlLookup As String

conCurrent = CurrentProject.Connection
sqlLookup = "SELECT Dates, Values FROM tableA WHERE Dates<#" & CDate(CutoffDate) & "#;"

rstLookup.Open sqlLookup, conCurrent, adOpenForwardOnly, adLockReadOnly
Do
  dblOutput = dblOutput * rstLookup.Fields("Values")
  rstLookup.MoveNext
Loop Until rstLookup.EOF
DProduct = dblOutput * TableBValue
CleanUp:
rstLookup.Close
Set rstLookup = Nothing
Set conCurrent = Nothing
End Function

Once this is placed in a code module in your database you can call it in a calculated field of a query like any other function.

A couple things to note:[ol][li]There is no error handling so if you get product that is too big you will get an overflow error.[/li][li]Depending on size of your tables this could take a while to run since Table (a) will be opened & closed for every record in Table (b)[/li][/ol]

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[alh1002]

I am trying this, but am obviously missing something basic
what should I be doing?
code

Function DProduct(TableBValue As Double, CutoffDate As Date) As Double
Dim conCurrent As ADODB.Connection
Dim rstLookup As New ADODB.Recordset
Dim dblOutput As Double
Dim sqlLookup As String

conCurrent = CurrentProject.Connection
'sqlLookup = "SELECT Dates, Values FROM tableA WHERE Dates<#" & CDate(CutoffDate) & "#;"

sqlLookup = "SELECT RT_2_withper.ladder_date, RT_2_withper.BOM, RT_2_withper.NAV, RT_2_withper.Base, " & _
"RT_2_withper.gMTDPER , RT_2_withper.gMTDRT, RT_2_withper.gTDRT, RT_2_withper.gTDCHANGEPER " & _
"FROM RT_2_withper;"


rstLookup.Open sqlLookup, conCurrent, adOpenForwardOnly, adLockReadOnly
Do
dblOutput = dblOutput * rstLookup.Fields("RT_2_withper.gTDCHANGEPER")
Debug.Print dblOutput

rstLookup.MoveNext
Loop Until rstLookup.EOF
DProduct = dblOutput * TableBValue
CleanUp:
rstLookup.Close
Set rstLookup = Nothing
Set conCurrent = Nothing
End Function

 

[CautionMP]

We only need date and value filtered where value is less than the date feed to the function and it's a simple query so the fields don't need to be fully qualified.


[tt]sqlLookup = "SELECT ladder_date, gTDCHANGEPER FROM RT_2_withper WHERE ladder_date<#" & CDate(CutoffDate) & "#;"[/tt]

(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

I want to multiple all values in table A with dates earlier than a date in Table B, times the value of that given date in table B
You may try something like this (SQL code):
SELECT B.Date, Exp(Log(B.Value)+Sum(Log(A.Value))) As Result
FROM TableB AS B INNER JOIN TableA AS A ON B.Date > A.Date
GROUP BY B.date, B.Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886