Median average doesn't change with any parameters

[tezread]

ahah - i think its working. What a guru you are sir

 

[vbaInet]

Just give it some more testing and let me know if it truly works.

By the way, why are you calling it in a query?

 

[tezread]

Just what I am used to doing when running reports. When I create reports I have grown up getting used to using queries as the source. Am I rioght in thinking that technically the whole thing could be built via VBA coding?

 

[vbaInet]

What I mean is, how many records is your query returning?

 

[tezread]

OK the results are not returning the correct median values. I test some parameters using Excel and doing the analysis manually and it doesn't work for sure.

for referral to treatment January 2011 should be 61 and it is 18 according to the report

 

[vbaInet]

Ignoring my previous question?

What I mean is, how many records is your query returning?

 

[tezread]

Ignoring my previous question?

Sorry.

The queries return depends on the parameters chosen. Monthly reports will return about 50 records

 

[vbaInet]

So does the query return the same median 50 times?

 

[tezread]

No not quite

Awkward to explain so please see attached as demo

Logon as andy and password andy

The report is in 'auditing' and 'waiting time averages'

query is qryWaitingTimesAverages - thats where functions are called to create the medians for the columns ReferalToTreatment and ReferralToClinicDate

 

[tezread]

forgigve me - new attachment see above

 

[tezread]

Just done some more tests using the report as well as testing manually in the query and it appears not to work if there are a odd number of records in the query

 

[tezread]

here are the test results

 

[vbaInet]

If you are using different criteria in the query and not applying that same criteria in the Median function, it won't work.

So the db above is the updated one?

 

[tezread]

If you are using different criteria in the query and not applying that same criteria in the Median function, it won't work.

So the db above is the updated one?

definitely applying the same start and end date criterias in query and in median function

try entering 01/01/2011 start date
and

01/02/2011 end date

you will see - referral to treatment is wrong

last db attachment is updated one yes

 

[vbaInet]

All you uploaded is a spreadsheet.

 

[tezread]

All you uploaded is a spreadsheet.

post no. 49 has db attached

 

[tezread]

i tell you - i wish i was working outside today!!! its lovely

 

[vbaInet]

What button should I be clicking on the form?

Yea, I know. I was out an hour ago

 

[tezread]

when the db loads enter andy for username and andy for password
click the auditing button on the home page

enetr start date 01/01/2011 and end date 01/02/2011 as a test then click 'preview report' next to 'waiting times'

 

[tezread]

I have tested it again by picking the top 50 percent of the records.

PARAMETERS [Forms]![frmReport]![startdate] DateTime, [Forms]![frmReport]![enddate] DateTime;
SELECT TOP 50 PERCENT qryEpisode.Patient_Name, qryEpisode.EpisodeID, qryEpisode.ReferralToTreatment
FROM qryEpisode
WHERE (((qryEpisode.ReferralToTreatment) Is Not Null) And ((qryEpisode.Date_Referred) Between Forms!frmReport!startdate And Forms!frmReport!enddate))
ORDER BY qryEpisode.ReferralToTreatment;

for the date range 01/01/2011 to 01/02/2011 this is the bottom calue - 61 BUT the report says 18!!!