Hi All,

Hoping you can help me with an issue I'm having with a query. I need to find the apr offered to 67% or more of cases in ascending order by our suppliers. The SQL is given below:

SELECT TOP 67 PERCENT lkpqryarname.arname, tblfileinspect.caseapr
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, tblfileinspect.caseapr;

The problem is that it is only bringing back a maximum of 15 of the 40 arnames that are actually there. The TOP 67 percent is working because when i put in the field caseapr it shows them correctly for less firms.

I have added max to the caseapr field and it still gives the same correct figure. I just need to get all the firms out of the query. That changes to the following and I only get 7 results:

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;

If I add ascending to the caseapr i get a totally different set of figures.

I've tried altering the date to cover a two year period and the only thing that changes is the value in the caseapr field. There are over 900 records to sort through and some do not have dates in as they were entered for other reasons.

In the last paragraph you state that some records do not have dates. Is that in this field: tblfileinspect.ffdate ?

If so, in the case where you are showing only 15 of 40 records, are the missing records also missing dates?



mmcdonal

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

Also, if you "need to find the apr offered to 67% or more of cases in ascending order," it seems to me you would want to take the total of your suppliers, and then multiply by .67, then do a

Select Count(Supplier) >= (.67 integer), apr, group by apr

and that would show you in every case where an apr was offered to .67% or more of your suppliers, and what that apr was.


mmcdonal

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

Thanks for your replies so far.

Ok to clarify things a little bit.

Each supplier (arname) submits a loan case for inspection (tblfileinspect) which contains an APR (caseapr). Now in order for the supplier to be able to advertise they need to show what 67% or more of their customers - loan cases - receive for an APR depending on other factors within a certain time period or ever. Therefore the fact that the dates are missing isn't important on older cases which are the ones that are missing.

Hence need to know for each supplier the 67th result e.g. 200 cases the APR required would be the 134th. The APRs need to be ordered ascending. It doesn't matter, using the example of the 134th case, if the preceeding 3 entries and 2 after are the same or different.

I need to find the 67% or more for various other figures the suppliers give in so need to figure out what I've done wrong.

The bizarre thing is this task is a doddle - less than a min job - to do with excel just a pain with access. Just need access for the scale and other analytics that need to be done.

I've realised what the code is doing, just can't think of the solution to it and tried numerous sub query permutations to get it to behave.

Its either sorting the top 67% results of all suppliers or its sorting the top 67% of aprs instead of grouping by supplier then sorting the apr for each.