Top 10 Percent / Top 10 Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 31 Oct 2006 18:59 GMT

Hi,

I have a query, and within the query I want to limit the result set by
adding criteria.

I have an if statement which says iif([debt-code]>100,True,False). What I
want to say is if the [debt-code] value is >100 then give me TOP 10 PERCENT
or TOP 10 VALUES. I have tried to add this to the if statement but it does
not work. Can anyone tell me how to do this in an if statement???

Thanks

Paul

Reply to this Message

KARL DEWEY - 31 Oct 2006 19:08 GMT

TOP 10 PERCENT is not the same as TOP 10 VALUES. TOP 10 PERCENT could be as
high 200 records or more and TOP 10 VALUES could be as low as 10 records.

I assume that [debt-code] is a field. To check if it is greater than 100
you have to pull individual records. This means all records so you can not
limit it to TOP anything. You will need one query setting parameters for a
second query.

Post you table structure, a sample of your data (3-6 records), and what you
expect the end results to look like.

> Hi,
>
[quoted text clipped - 9 lines]
>
> Paul

Reply to this Message

Paul - 31 Oct 2006 19:21 GMT

Karl,

Thanks for the response.

Yes I understand that the TOP 10 PERCENT and TOP 10 VALUES will retrieve
different data values. I will need to use one or the other depending on
amount of data per Client Name.

debt-code    dt-mtrdesc    CountOfdebt-code
4170285    BARCCARD017    312
4170287    BARCCARD017    312
4170288    BARCCARD017    312
4170290    BARCCARD017    312
4170291    BARCCARD017    312
4170292    BARCCARD017    312

I have already worked out how many records there are per client, as above.
So for example, if BARCCARD017 has more than 250 records then all I want the
data to show me is the TOP 10 PERCENT, else if it does not then show me TOP
50 VALUES.

Hope this now makes more sense.

Thanks

Paul

> TOP 10 PERCENT is not the same as TOP 10 VALUES. TOP 10 PERCENT could be as
> high 200 records or more and TOP 10 VALUES could be as low as 10 records.
[quoted text clipped - 20 lines]
> >
> > Paul

Reply to this Message

KARL DEWEY - 31 Oct 2006 20:30 GMT

You threw some more in to the mix. I think this will do what you want.
Paul_CountOfdebt-code --
SELECT Paul.[debt-code], Count(Paul.[debt-code]) AS [CountOfdebt-code]
FROM Paul
GROUP BY Paul.[debt-code];

Paul_CountOfdt-mtrdesc --
SELECT Paul.[dt-mtrdesc], Count(Paul.[dt-mtrdesc]) AS [CountOfdt-mtrdesc]
FROM Paul
GROUP BY Paul.[dt-mtrdesc];

SELECT T.*, (SELECT COUNT(*) FROM [Paul] T1 WHERE
T1.[debt-code] = T.[debt-code] AND T1.PaulID <= T.PaulID) AS Rank,
[Paul_CountOfdt-mtrdesc].[CountOfdt-mtrdesc]
FROM (Paul AS T INNER JOIN [Paul_CountOfdebt-code] ON T.[debt-code] =
[Paul_CountOfdebt-code].[debt-code]) INNER JOIN [Paul_CountOfdt-mtrdesc] ON
T.[dt-mtrdesc] = [Paul_CountOfdt-mtrdesc].[dt-mtrdesc]
WHERE ((((SELECT COUNT(*) FROM [Paul] T1 WHERE T1.[debt-code] =
T.[debt-code] AND T1.PaulID <=
T.PaulID))<=IIf([CountOfdebt-code]>100,[CountOfdebt-code]*0.1,10))) OR
((((SELECT COUNT(*) FROM [Paul] T1 WHERE T1.[debt-code] =
T.[debt-code] AND T1.PaulID <=
T.PaulID))<=IIf([CountOfdt-mtrdesc]>250,[CountOfdt-mtrdesc]*0.1,50)))
ORDER BY T.[debt-code], T.PaulID;

> Karl,
>
[quoted text clipped - 47 lines]
> > >
> > > Paul

Reply to this Message

Paul - 31 Oct 2006 21:12 GMT

Thanks very much for this.

If I create a new query would I need to write this code in a SQL view of the
query? and obviously subsitute Paul with the name of my table?

Thanks

Paul

> You threw some more in to the mix. I think this will do what you want.
> Paul_CountOfdebt-code --
[quoted text clipped - 72 lines]
> > > >
> > > > Paul

Reply to this Message

KARL DEWEY - 31 Oct 2006 21:22 GMT

What I posted was three queries. The first two just give you a count of the
[debt-code] and [dt-mtrdesc]. They are just totals queries. They may be
created in any number of ways but just make sure the names match what you use
in the third query.

The third has your criteria. Multiplying the count time 0.1 is 10 %. You
will need to copy and paste the post in the SQL view. What out for hard
returns that the posting may add to the statement.

Yes use your table name instead of Paul. Use your autonumber field for
PaulID.

> Thanks very much for this.
>
[quoted text clipped - 81 lines]
> > > > >
> > > > > Paul

Reply to this Message