MS Access SQL to calculate the logarithmic average of dBµV

user_1808369 0
Viewed 91

Unfortunately my SQL skills are very basic. I hope someone can help me. I would like to calculate the logarithmic average over several dBµV values. Unfortunately I cannot use the standard average function as the result are not correct.

Today I have the data in Excel and my calculation works fine. Unfortunately, Excel is now reaching its performance limit and therefore I've imported the data into MS Access (I'm using MS Access Ver Office 365).

I have a table with an ID, frequency and the dBµV values. I would like to get the logarithmic average of all dBµV samples over a certain range of frequencies for a particular ID. The frequency starts at 122 and can go up to 1802. Not every ID has a value for all frequencies.

Frequency ranges can be as follows and never change (means the beginning and the end of the frequency range are constant):

  • 122 to 170
  • 178 to 226
  • 234 to 530
  • and so on

In Excel I used the following formula for the frequency range F122_170 (B1:H1 contains the dBµV values):

{=10*LOG(SUM(10^(B1:H1/10))/COUNTA(B1:H1))}

Now I am completely lost with the SQL statement to get the desired result.

Below is an example of my table "tblDev".

ID freq dBuV
3977739907 122 32.44
3977739907 130 32.24
3977739907 138 31.93
3977739907 146 30.79
3977739907 154 31.31
3977739907 162 32.39
3977739907 170 32.43
3977739907 178 32.81
3977739907 186 32.21
3977739907 194 31.23
… … …

The output I would like to have is as follows:

ID F122_170 F178_226 F234_530 F770_up
3977739907 31.97 31.23 37.03 0.00
3845056609 32.24 23.40 41.09 38.18
3882949203 23.10 16.98 31.03 32.26
... ... ... ... ...

Would be great if someone could help me with the SQL statement.

Best regards Patrik

ms-access logarithm average sql
3 Answers

Consider:

Query1: Log() returns natural log (base e) so to get log in base 10, divide Log(x) by Log(10)

SELECT tblDev.ID, 10*(Log(Avg(10^([dBuV]/10)))/Log(10)) AS dB, 
Switch([freq]<=170,"F122_170",[freq]<=226,"F178_226",[freq]<=530,"F234_530",True,"F770_up") AS Grp
FROM tblDev
GROUP BY tblDev.ID, Switch([freq]<=170,"F122_170",[freq]<=226,"F178_226",[freq]<=530,"F234_530",True,"F770_up");

Query2: to pivot results of query1

TRANSFORM First(dB)
SELECT Query1.ID
FROM Query1
GROUP BY Query1.ID
PIVOT Query1.Grp IN (F122_170,F178_226,F234_530,F770_up);
user_76071900

Hmmm . . . I think you can use:

select id,
       10 * log(avg(iif(freq between 122 and 170, 10^dBuV), null)) / log(10)
       10 * log(avg(iif(freq between 178 and 256, 10^dBuV), null)) / log(10)
from t
group by id;

Note: This replaces the SUM()/COUNT() with AVG() . . . which I think is the same thing.

user_11440350

First you need a Log10 function:

Public Function Log10( _
    ByVal Value As Double) _
    As Double

' Returns Log 10 of Value.
' 2015-08-17. Gustav Brock, Cactus Data ApS, CPH.

    Const Base10    As Double = 10

    ' No error handling as this should be handled
    ' outside this function.
    '
    ' Example:
    '
    '     If MyValue > 0 then
    '         LogMyValue = Log10(MyValue)
    '     Else
    '         ' Do something else ...
    '     End If
    
    Log10 = Log(Value) / Log(Base10)

End Function

Then you can run this query:

Select 
    ID, 
    Sum(AF122_170) As F122_170,
    Sum(AF178_226) As F178_226,
    Sum(AF234_530) As F234_530,
    Sum(AF770_up) As F770_up
From

    (Select 
        tblDev.ID, 
        IIf(freq Between 122 And 170, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF122_170,
        IIf(freq Between 178 And 226, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF178_226,
        IIf(freq Between 234 And 530, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF234_530,
        IIf(freq >= 770, Log10(Avg(10 ^ (tblDev.dBuV / 10))) * 10, 0) As AF770_up    From 
        tblDev
    Group By 
        ID,
        freq Between 122 And 170,
        freq Between 178 And 226,
        freq Between 234 And 530,
        freq >= 770) As T

Group By ID
user_35272970
Related