By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,156 Members | 1,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,156 IT Pros & Developers. It's quick & easy.

Query to pull top n% of records that include specific criteria

jonnycakes
P: 24
I've been trying to do this for a long time, and i'm at the point where my head may explode.. Please take caution when reviewing my code as it may cause more confusion than it is intended to.

I've got a table named Test that includes multiple fields:

[Management]|[Group]|[Employee]|[Amount]|[Variance]|[DateTime]

I'm trying to pull 25% of the records for each of the Managers listed in the "Management" field, but not just any 25%..
  • I'm needing to ensure that I dont exclude any given employee in the "Employee" field. I need a sample of all employees.
  • I want the 25% to be based off of the TOP of "Variance" in DESC order.
  • I also need to include all records that have "0" in the "Amount" field.

This set of code was my sad attempt to pull 25% of each of the employee's records not including any records that contain "0" in the "Amount" field.

Expand|Select|Wrap|Line Numbers
  1. SELECT Managment, Employee, Max(DateTime) AS MAXDateTime, Amount, Variance
  2. FROM Test
  3. GROUP BY Employee, Variance, Management
  4. HAVING Amount IN
  5. (SELECT TOP 25 PERCENT Variance
  6. FROM Test AS DUPE
  7. WHERE Test.Employee=DUPE.Employee
  8. AND Amount<>0
  9. ORDER BY DUPE.Variance DESC, DUPE.DateTime DESC)
  10. ORDER BY Employee, Variance DESC
  11.  
Then my thought was that I would use a UNION ALL to tie in all of the records that have "0" in the "Amount" field. I didnt have any success with this thought and even if i did, it wouldnt have given me the target 25%.

::Edit:: I have Access 2000.

Any suggestions? Thank you in advance for any help you may be able to offer!
Jul 2 '12 #1

✓ answered by Rabbit

It seems I've misunderstood one of your requirements. If you need to include all 0 records, then you will use the method you laid out before. Union them to the variance query.

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,366
As far as your bullet points are concerned:

1) If you're not explicitly excluding any employees, then they'll be included. So I don't know what you mean by that bullet point.

2) No notes on that. What you have is fine.

3) You say you want to include 0's and yet you exclude them in your code. I don't know what's going on there.

As for your code:

1) You have an amount field in your aggregate query and yet you don't group by it or put it in an aggregate function.

2) You're using the HAVING clause when you're not using it on an aggregation. That should be moved to the WHERE clause.

3) In your filter, why are you filtering on Amount when the subquery is returning variance? They're not the same field. They're not going to match each other.
Jul 2 '12 #2

jonnycakes
P: 24
@Rabbit
As you can see i've struggled with this immensely.. I am terribly sorry for taking up your time with this haggered code. I hope that after I review this and respond back, that you have it in you to provide further assistance.

Thank you!

btw, you are an absolute beast(same goes for NeoPa) and I can only hope to gain half of the knoweldge that you have.
Jul 2 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
jonnycakes:
btw, you are an absolute beast
I'm reading that as a compliment - however it was intended :-D

I'm sure that, between us, we can help you forward here Jonny. Have a look at the points Rabbit has brought up first though, then you may like to peruse Aggregate Query Woes for some further insight.
Jul 2 '12 #4

jonnycakes
P: 24
@Rabbit
Bullet 1: Your comment makes sense.

Bullet 3: Yep, I failed to explain my thoughts completely.. it didn't make sense for me to filter out the zeros. I originally thought it would be better to filter out the zeros and build a query to capture only records that contained zeros in that column, and then use a union all to tie them togther.

If i'm not mistaken, I believe my code has been corrected to comply with points 1 & 3, but when I change the HAVING clause to a WHERE, I revieve a "missing syntax error. Here's my code:

Expand|Select|Wrap|Line Numbers
  1. SELECT Max(DateTime) AS MAXDATETIME, Employee, Management, Account, Variance, Amount
  2. FROM ADI
  3. GROUP BY Employee, Variance, Management, Amount, Account
  4. HAVING Variance IN
  5. (SELECT TOP 60 PERCENT Variance
  6. FROM ADI AS DUPE
  7. WHERE ADI.Employee=DUPE.Employee
  8. ORDER BY DUPE.Variance DESC, DUPE.DATETIME DESC, Amount ASC)
  9. ORDER BY Employee, Variance DESC;
With this code, I'm getting more than 60% of the records per Employee(IE Employee one may have 4 unique records, this query will return 3)


@NeoPa
:) It was intended to be a compliment. I'm very greatful for the topic you shared with me! It made a lot of sense to me the first time through, but i'm going to continue to study it to ensure I fully understand. Thanks again.
Jul 3 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
I thought you wanted 25%? And if you have 4 records, then yes, you will get 3 records. Two records is only 50%. If it can't get the exact percent, it will round up.

If you absolutely need it to not round up, you will need to use a ranking query and calculate your percentages there.
Jul 3 '12 #6

jonnycakes
P: 24
Well the % may change, but it's very helpful for me to know that the top function will round up. Given that this function acts with such behavior, my assumption is that the result of this query will yield a much larger % of management's records.

My goal was to have each manager to review n%(25, 60, etc) of records. It's important that each manager reviews at least 1 from every employee. Equally as important these records should include all of the records that contain 0 in the amount field and the highest variance records.

Any suggestions on how I can ensure to include all records that have 0 in the amount field? My query currently only focuses on top variance. I'll be sure to research ranking; thank you for your guidance.
Jul 3 '12 #7

Rabbit
Expert Mod 10K+
P: 12,366
It seems I've misunderstood one of your requirements. If you need to include all 0 records, then you will use the method you laid out before. Union them to the variance query.
Jul 3 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.