Quartile Function in Access

[June7]

What is the name of the table? Do you want it hard-coded or passed into the function by variable?

Why would you have the table name in the sql statement twice? Because that is what your last version is doing.

Do this:

Set RstOrig = CurrentDb.OpenRecordset("SELECT [FldSales] FROM [Test 2] WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

Or this:

Set RstOrig = CurrentDb.OpenRecordset("SELECT [FldSales] FROM [" & RstName & "] WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

[tennisbuck]

Here's a screen shot of the error.

[tennisbuck]

Here's the error.

[tennisbuck]

Here's the latest DB. I feel so close!!

[ItsMe]

I was looking at your error screenshot and noticed your declaration for your Recordset objects ar not of the DAO type.

Perhaps,
dim RstOrig as dao.recordset

and the other recordset maybe a recordset2 type declaration or a recordsetclone of the first recordset. Not sure exactly what you are after though. Didn't comb through the whole thing.

[tennisbuck]

I want it to pass through as a variable to keep it flexible.

[June7]

1. the field name in the table is FldSum not FldSales so change the code

2. the second Set RstOrig at the end of the procedure should be (look at the original code from source thread)

Set RstOrig = Nothing

3. this line needs to be two lines, do a carriage return after MoveFirst (it is correct in post 23 but not in the posted db)

RstSorted.MoveFirstxVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1

After those edits the code ran correctly.

4. the query does not need to be an aggregate - GROUP BY not necessary


Not sure how flexible that allows the procedure to be since field references are hard coded.

[ItsMe]

Nice work June and the others too. It seems to correctly return Q1. I believe the OP may be interrested in an aggregate "Sold To Cust" sum to then calculate the quartile. So the totals query may be appropriate.

[June7]

The GROUP BY doesn't do anything for the sample data provided. Results are the same except for the blank row because of all the empty records.

[tennisbuck]

Thanks June7. This is amazing.

Here's the formula for DMIN and DMAX

Lowest: DMin("FldSum","Test 2","[Marketing View Description]=" & Chr(34) & [Marketing View Description] & Chr(34))