Top 10 Percent / Top 10 Values

G

Guest

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
 
G

Guest

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.
 
G

Guest

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





KARL DEWEY said:
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.

Paul said:
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
Click to expand...
Click to expand...
 
G

Guest

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;


Paul said:
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





KARL DEWEY said:
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.

Paul said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

KARL DEWEY said:
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;


Paul said:
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





KARL DEWEY said:
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,

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.

Paul said:
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

KARL DEWEY said:
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;


Paul said:
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.

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,

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Top 10 records for each ID 4
Top 10 in Crosstab query 3
Single Query for Top 10 of Multiple Items 1
Union Query 0
Multiple Top Values 2
Access Test if value is less than percent 0
Getting Past Integer Only Requirement for Top XX PERCENT 1
Top 10 Invoice Values Per Customer & Site - ?? 2

Top