Divide Query

[TSO456]

Hi,

I have two columns in a Access table:

Amount1 Amount2
100 200
300
400
500
I would like to divide amount1 (100) by amount2(200), (300), (400) etc. How can I do that?
simply, I would like to divide values in amount1 by every value in amount2

Thank you
Jill

 

[MichaelRed]

Somewhat hazy on the real deal (details). I would be hard pressed to do this only in SQL land even in the simplistic instance (one and ONLY one in [Ammount1]. Using a procedure, however, makes it simplistic.

A Crude approximation of the function would be:

Public Function basDiv(Divd As Variant, Divr As Variant) As Long

    Static MyDivr As Long

    If (Divr = 0 And MyDivr <> 0) Then
        GoTo Calc
    End If

    If (IsMissing(Divr) And MyDivr = 0) Then
        MyDivr = 1
        GoTo Calc
     Else
        MyDivr = Divr
    End If

    If (Divr = 0) Then
        MyDivr = 1
    End If

    If (IsNull(Divr)) Then
        MyDivr = 1
    End If

    If (IsNull(MyDivr)) Then
        MyDivr = 1
    End If

Calc:

    basDiv = Divd / MyDivr

End Function

The Query:
SELECT tblDivAndCon.Ammount1, tblDivAndCon.Ammount2, basDiv([Ammount2],[Ammount1]) AS MyQuotient
FROM tblDivAndCon;


The Results (of the Query):
[/code]
Ammount1 Ammount2 MyQuotient
100 200 2
0 300 3
0 400 4
0 500 5
[/code]

Of course, this is (as noted) just an example/approximation. Your data may (is?) perhaps more elaborate (mixed values, representing real world (currency?) so the data types will not fit into this specific bit. Other erors / exceptions may also need to be acounted for in the actual data.

Finally, the sample data set -enev in the simplistic mode- suggests a poorly designed / maintained data set, reminicient of what might be more commonly found in spreadsheet than any professional database. A better option might (would) be to correct the data set to explicitly include the divisor ([Ammount1]) with each record. You need to realize that recordsets are not secessarily ordered in the 'expected' manner (order of entry) and (this ordering) may be changed without regard your specific use.




MichaelRed
mlred@verizon.net