Semi-Complicated Median Query

[June7]

If you can't make copy and remove sensitive info, not sure we can help. Because if we could make code work with our database that won't explain why it's not working for yours.

[gcgoode]

Ok, that makes sense. Ill work on making a copy with different information.

[June7]

I finally did a test of the code in link I found. Works perfectly with no modification.

[orange]

I've done a little searching and trial and error.

I found a function DMedian (code below)
I have a table of Animals

AnimalId
AName

1
Spot

2
Jim

3
Sam

4
David

5
BlueEyes

6
Capitan

7
Johnny

I have a table of AnimalLocations (actually AnimalLocs)

AnimalId
SightingDate
GPSLat
GPSLong
CaptureDateX

1
27/03/2009
47.3
77.9
21/02/2008

1
28/03/2009
46.8
77.34
21/02/2008

1
02/12/2009
45.32
78.44
21/02/2008

2
13/03/2009
48.9
76.33
27/02/2008

2
29/03/2009
48.8
77.45
27/02/2008

2
21/04/2010
47.54
78.53
27/02/2008

3
24/08/2009
49.4
76.2
03/12/2008

4
21/04/2010
47.52
78.23
29/05/2008

4
23/04/2010
47.66
78.666
29/05/2008

5
14/09/2010
49.3
77.7
04/02/2008

5
17/09/2010
48.9
77.3
04/02/2008

Note: There is no sighting data for AnimalID 6 and 7.

I set up a query to find the median of gpsLat for each AnimalId. (This approximates your
age groups). I have 0, 1 or more gpsLat values for each AnimalID.

SELECT Animal.AnimalId
, Animal.AName
, Dmedian("gpslat","animalLocs","animalId = " & [animal].[animalID]) AS Median
FROM Animal;

with result

AnimalId
AName
Median

1
Spot
46.8

2
Jim
48.8

3
Sam
49.4

4
David
47.59

5
BlueEyes
49.1

6
Capitan



7
Johnny

DMedian code

'---------------------------------------------------------------------------------------
' Procedure : DMedian
' Author    : mellon
' Date      : 17/08/2015
' Purpose   : from https://msdn.microsoft.com/en-us/library/dd789431%28v=office.12%29.aspx?f=255&MSPPError=-2147217396
'---------------------------------------------------------------------------------------
'
Public Function DMedian( _
 ByVal strField As String, ByVal strDomain As String, _
 Optional ByVal strCriteria As String) As Variant

    ' Purpose:
    '     To calculate the median value
    '     for a field in a table or query.
    ' In:
    '     strField: the field.
    '     strDomain: the table or query.
    '     strCriteria: an optional WHERE clause to
    '                  apply to the table or query.
    ' Out:
    '     Return value: the median, if successful;
    '                   Otherwise, an Error value.

    Dim db As DAO.Database
    Dim rstDomain As DAO.Recordset
    Dim strSQL As String
    Dim varMedian As Variant
    Dim intFieldType As Integer
    Dim intRecords As Integer
    
    Const errAppTypeError = 3169
    
    On Error GoTo HandleErr

    Set db = CurrentDb()
    
    ' Initialize return value.
    varMedian = Null
    
    ' Build SQL string for recordset.
    strSQL = "SELECT " & strField & " FROM " & strDomain
    
    ' Only use a WHERE clause if one is passed in.
    If Len(strCriteria) > 0 Then
        strSQL = strSQL & " WHERE " & strCriteria
    End If
    
    strSQL = strSQL & " ORDER BY " & strField
    
    Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Check the data type of the median field.
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, _
      dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field.
        If Not rstDomain.EOF Then
            rstDomain.MoveLast
            intRecords = rstDomain.RecordCount
            ' Start from the first record.
            rstDomain.MoveFirst
    
            If (intRecords Mod 2) = 0 Then
                ' Even number of records.
                ' No middle record, so move to the
                ' record right before the middle.
                rstDomain.Move ((intRecords \ 2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle.
                rstDomain.MoveNext
                ' And average the two values.
                varMedian = _
                  (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates.
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
            Else
                ' Odd number or records.
                ' Move to the middle record and return its value.
                rstDomain.Move ((intRecords \ 2))
                varMedian = rstDomain.Fields(strField)
            End If
        Else
            ' No records; return Null.
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error.
        Err.Raise errAppTypeError
    End Select

    DMedian = varMedian
    
ExitHere:
    On Error Resume Next
    rstDomain.Close
    Set rstDomain = Nothing
    Exit Function

HandleErr:
    ' Return an error value.
    DMedian = CVErr(Err.number)
    Resume ExitHere
End Function

OOOoops: After posting I saw that June had posted while I was doing some research/testing.
It seems I have found the same function (same link) and was busy testing away.
Anyway, gcgoode, I think you have a solution or at least a template for your solution.

[gcgoode]

Thanks for all the help Orange and June. I followed the new template with DMedian that Orange laid out in the last post (I wasn't adding that third criteria) and now I just get an #error under Median in my datasheet. Which is confusing because it doesn't give me any direction of what is wrong.


I made a modified copy of my database and deleted about 99% of the data, but this kinda shows what I'm working with. I have the query that you guys got to work that I'm getting the #error in under DMedianQ and the ideal query that I started out trying to do (but gave up on) under DMedianIDEAL. In this one I am trying to get the median of a query calculation, sorted by the same things.

If either of you see what I'm doing wrong I would appreciate any input.

[orange]

I have a problem with your table structure-- my guess is that your Rate table has a number of hidden entities or attributes that belong somewhere else.

Can you state clearly in 10-12 points exactly what this data base is about?
And, what your query is trying to do specifically? Sample data input and output.

[June7]

The DMedian() function call in DMedianIDEAL cannot reference calculated field in GROUP BY query. The calculation must be used in the function call:

DMedian("[TotalPremium]-[PolicyFee]","Rate","Age = " & [Driver].[AGE])

Age is not a field within the domain Rate, therefore the function errors.

Build a query object that joins the tables then build another query that references that query object in the function call.

Age should not be saved in table, it should be calculated when needed.

DMedianQ - Rate table is not joined to Policy table. Same issue with Age field applicable.

NOTE: spelling of DMedainIDEAL query name

[gcgoode]

I have a problem with your table structure-- my guess is that your Rate table has a number of hidden entities or attributes that belong somewhere else.

Can you state clearly in 10-12 points exactly what this data base is about?
And, what your query is trying to do specifically? Sample data input and output.

Sure! This database usually has more tables, fields, and a ton more records but I deleted them to attach the database. I deleted the same records through all the tables (by sorting by PK and FKs) so that shouldn't be a problem from my (limited) understanding.
Its a database that shows different peoples rates for what they paid for a product. In the real database there will be a bunch of records on, say, 32 year old married Males. Each will have paid a certain amount for the product (TotalPremium or TotalPremium-PolicyFee ideally). I want this query to get all these amounts and output the median. And do that for every single category of Age, sex, marital. I already have it completed for average but median would give a better estimate in my view. I'm a beginner in Access but didn't expect to be having this much trouble with it haha.

[gcgoode]

The DMedian() function call in DMedianIDEAL cannot reference calculated field in GROUP BY query. The calculation must be used in the function call:

DMedian("[TotalPremium]-[PolicyFee]","Rate","Age = " & [Driver].[AGE])

Ok thanks, that should help.

Age is not a field within the domain Rate, therefore the function errors.

Build a query object that joins the tables then build another query that references that query object in the function call.

Ok, I thought thats what .....

FROM (Policy INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID) INNER JOIN Rate ON Policy.RecordID = Rate.PolicyLinkID
GROUP BY Driver.Age, Driver.Sex, Driver.Marital;

This part of the code was doing. I'm not sure what you mean by "Build a query object that joins the tables then build another query that references that query object in the function call." Could you give me an example of what you mean?

The Rate table not being joined and the spelling issue were just boneheaded mistakes I made when making the new database copy. Thanks for pointing them out for me.

[June7]

Query1
SELECT Policy.*, Driver.*, Rate.* FROM (Policy INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID) INNER JOIN Rate ON Policy.RecordID = Rate.PolicyLinkID;

Query2
SELECT *, DMedian("[TotalPremium]-[PolicyFee]", "Query1", "Age = " & [AGE]) AS RateMedian FROM Query1;

[orange]

gcgoode,
Pay attention to what June has posted--it is very relevant to your situation/solution.
Once you get your query(s) set up, and tested, it doesn't matter how many records you have.

This database usually has more tables, fields, and a ton more records but I deleted them to attach the database. I deleted the same records through all the tables (by sorting by PK and FKs) so that shouldn't be a problem from my (limited) understanding.

Don't confuse the issue. Identify what you are trying to do. Focus and solve the general case.

Good luck with your project.

[gcgoode]

Query1
SELECT Policy.*, Driver.*, Rate.* FROM (Policy INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID) INNER JOIN Rate ON Policy.RecordID = Rate.PolicyLinkID;

Query2
SELECT *, DMedian("[TotalPremium]-[PolicyFee]", "Query1", "Age = " & [AGE]) AS RateMedian FROM Query1;

Ok, that makes sense. Thanks for posting this.

I set it up that way and my new code:

 SELECT DMedian("[TotalPremium]-[PolicyFee]","Query1",  "Age = " & [AGE]) AS RateMedian, Query1.Age, Query1.Sex, Query1.Marital
FROM Query1
GROUP BY Query1.Age, Query1.Sex, Query1.Marital;

Gives me the same output as my old code of:

SELECT Driver.Age, Driver.Sex, Driver.Marital, DMedian("TotalPremium","Rate","Age = " & [Driver].[AGE]) AS Median
FROM (Policy INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID) INNER JOIN Rate ON Policy.RecordID = Rate.PolicyLinkID
GROUP BY Driver.Age, Driver.Sex, Driver.Marital;

Just the #error under Median


So I messed around with it for awhile and changed different things that would come to my mind with no luck. Sometimes I would get new errors (data type mismatch, not included in aggregate function) and sometimes it would just give me the same #error.
One thing that came to mind is that the TotalPremium and PolicyFee are fields under both the Policy and Rate tables. So I tried changing it to specify rate as [Rate.TotalPremium]-[Rate.PolicyFee], but that didn't change much.

[June7]

Why don't you try without the GROUP BY clause?

As noted earlier, the function works perfectly with my database. If we can't examine and test with your database (even with dummy data or simply with confidential info removed), don't know that we can be of further help.

[gcgoode]

Why don't you try without the GROUP BY clause?

As noted earlier, the function works perfectly with my database. If we can't examine and test with your database (even with dummy data or simply with confidential info removed), don't know that we can be of further help.

Well, Ive tried it every which way I can think of. I use the group by clause because if I dont I still get the #error, but I also have it by every single field in the three tables I have inter joined. I want the output to look like this:

Sex Age Marital Median
M 35 S 400
M 36 S 450
F 21 M 390

etc.

So I assumed I need it grouped by my three categories so Access knows to get the median of all Age, sex and marital combinations.

I posted a sample database in post #20, is that not what you mean? Because thats what im working in now and I'm getting these errors.

[June7]

Ooops, forgot that and I had already downloaded the file and reviewed. Will do some more.

First thing I did was test the function by calling from the VBA immediate window:

?DMedian("Age","Driver")

That returns 41.

So the function basically works.

Why are TotalPremium and PolicyFee in both tables?

Then I built Query1:
SELECT Driver.Age, Driver.Sex, Rate.TotalPremium, Rate.PolicyFee, [rate].[TotalPremium]-[rate].[policyfee] AS PolRate
FROM (Policy INNER JOIN Rate ON Policy.RecordID = Rate.PolicyLinkID) INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID;

And again from the immediate window:

?DMedian("PolRate","Query1")

That returns 353.5.

Another test:

?DMedian("PolRate","Query1","Age=34")
306

Now I built another query:
SELECT Query1.Age, DMedian("PolRate","Query1","Age=" & [Age]) AS MedRate FROM Query1;

Howevever, what I should have realized long ago, this errors: DMedian("[TotalPremium]-[PolicyFee]","Query1","Age=" & [Age])