I've messed around with this for a while and still no joys. I am trying to get access 2000 to bring out the right results

For each employee select the 67th percentile value of ascending ordered field of orders placed between certain dates.

the date field criteria is normally "BETWEEEN Date() and Date()-365" or "BETWEEN Date() and Date()-180"

the fields are

intcaseid this field only appeared as a check on what access was doing
intemployeeid
dtsubdate
txtapr

I only want 1 result for each employee so the resultant would look like

J Bloggs 8.9%
A Smith 10.1%
A Other 7.5%
S Butts 6.9%

If I run the query with a specified employeeid e.g. "WHERE employeeid=5", I get a very different result if I group on employeeid.

E.g. I get grouped "J Bloggs 7.5%" and specific "J. Bloggs 8.9%"

What does the whole SQL string look like in both cases? What code are you using to launch event query?

mmcdonal

Look it up at: http://wrox.books24x7.com

SELECT TOP 67 PERCENT lkpqryarname.arname, Max(tblfileinspect.caseapr) AS MaxOfcaseapr
FROM tblfileinspect INNER JOIN lkpqryarname ON tblfileinspect.arfirm = lkpqryarname.arid
WHERE (((tblfileinspect.caseapr) Is Not Null And (tblfileinspect.caseapr)>1) AND ((tblfileinspect.ffdate) Between Date() And Date()-90))
GROUP BY lkpqryarname.arname;

That's the latest attempt at it.

It's hard to tell without seeing your table structure. A couple of comments.

You have

WHERE (((tblfileinspect.caseapr) Is Not Null And (tblfileinspect.caseapr)>1) AND ((tblfileinspect.ffdate) Between Date() And Date()-90))

Well, if you have

tblfileinspect.caseapr > 1

you don't need

tblfileinspect.caseapr Is Not Null

because the first requirement will ensure that the field is not null. Also, you used a BETWEEN in your criterion but your dates are backwards. I wonder what Access will do if your end date is AFTER your start date. Swap those.

tblfileinspect.ffdate Between DateAdd("d", -90, Date()) And Date()

(aside)
Say, there are balloons and confetti coming off the ceiling. Oh, this is my 1000th post.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

yeah i tried it without the null a few months ago. Unfortunately found I was getting numerous blank entries without the "is not null" in there. This then throws out the result

the table structure consists mainly of yes no fields (around 100 with a total of 160 fields in all at the moment and ever expanding as the law changes) as its for checking the contents of a case and the quality & nature of information recorded.

The other fields of the table aren't involved in the query. I realise that normalisation would regularly dictate making this into seperate tables however it just doesn't really seem to work.