I think I gave up on the SQL route..
I am trying to calculate percentile values by using your VBA code:
Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
Dim strSQL As String
Dim N As Integer
Dim nSubk As Double
Dim vSubk As...
I just realized that I did not explain why I have multiple queries instead of one query holding multiple calculated fields. It is because each calculated field has null values (which I can't count because it messes up my percentile calculations) and I need to set criteria "is not null" for me to...
I changed all my names to have no spacing and no special characters. My SQL is now working but is there a way to exclude null values in my SQL code? Right now I have my SQL looking like
SELECT Max(STEP2_FactorVariables.MaintenanceCostsPerSF) AS MaxOfMaintenanceCostsPerSF, FROM...
Hi all,
I am in the midst of moving an Excel model and database into Access and have come up against the very annoying problem of my query taking wayyyyyyyy to long to run.
First to provide a better understanding of my situation, here is how my model in Access is structured:
First I have a...
then i don't understand why when i try to run my query it says that in the SELECT state ment theres a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect :(
the sql i wrote is SELECT [Percentiles].[percentile], DpercentileExcel("operating...
SELECT
tbPercentiles.percentile,
DpercentileExcel("costPerSize","qryCostPerSize",[percentile]) AS PercentileValueExcel,
Dpercentile("costPerSize","qryCostPerSize",[percentile]) AS PercentileValue
FROM
tbPercentiles
ORDER BY
tbPercentiles.percentile;
what is the Dpercentile in ur sql...
Ok thanks MajP,
I've created a new module and first put in
Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
Dim strSQL As String
Dim N As Integer
Dim nSubk As Double
Dim vSubk As Variant
Dim vSubkPlus1 As Variant
Dim...
Wow these VBA codes look really promising, I'm going to try them out now. Because I am a complete noob at this, do I just copy n paste the first part into a module, then copy and paste the second part underneath it with my table name and field?
Hi,
I've been searching up how to calculate percentile values all day and I still do not seem to be able to get the values that I want. Can someone please help me?
Heres the overview of my situation:
I have a table called Raw Data and it holds fields including 'operating costs' and 'property...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.