Counting 225 records per agent. 1

[Ration]

Hi,

I am having a problem where I need to count 225 records per agent from a pool of about 50 agents. This will be part of a monthly report. The current SQL code is as follows:

SELECT Top 225 [File Reps].FirstName, Audits.Errors, Audits.Financial, Audits.[Audit Date] FROM [File Reps] INNER JOIN Audits ON [File Reps].[File RepsID] = Audits.[Agent Name] WHERE (((Audits.[Audit Date]) Between [Start Date] And [End Date]));

This code only returns 225 records total. I need it to return 225 records per Agent Name. Can anyone help?

 

[Remou]

An advanced search of this forum using Top Group as keywords will return quite a few examples.

 

[Ration]

Thanks Remou. I tried that and even found a post that was similar to my problem. I haven't been able to manipulate the code to work with the above SQL Statement. I'm not very skilled in SQL and really could use some help with this.

 

[Remou]

Which post did you find?

 

[Ration]

I was looking at Thread 701-1090026 Select Top 5 for each Group by Value.

The SQL code in the forum reads:

SELECT A.countryOfIssue, A.Cusip, Sum(A.BorrowBalance) AS SumOfBorrowBalance
FROM Borrows_quniAll AS A
WHERE A.Cusip In (SELECT TOP 5 Cusip
FROM Borrows_quniAll WHERE countryOfIssue=A.countryOfIssue GROUP BY Cusip ORDER BY Sum(BorrowBalance) DESC)
GROUP BY A.countryOfIssue, A.Cusip
ORDER BY 1, 3 DESC;

I can't figure out how to reconfig this code for my needs. I tried adding the (Select Top 225 Audits.[Audit Date]from [File Reps] at several different places in the string. I keep getting syntax errors. I know I probably have the select Top 225 syntax wrong. I'm getting more confused with each attempt at resolving this. Any suggestions?

 

[Remou]

How about:
[tt]SELECT [File Reps].[File RepsID], [File Reps].Firstname, Audits.Errors, Audits.Financial, Audits.[Audit Date]
FROM [File Reps] INNER JOIN Audits ON [File Reps].[File RepsID] = Audits.[Agent Name]
WHERE ((([File Reps].[File RepsID]) In (SELECT TOP 255 [File RepsID] FROM [File Reps] INNER JOIN Audits ON [File Reps].[File RepsID] = Audits.[Agent Name]
WHERE (((Audits.[Audit Date]) Between [Start Date] And [End Date])))));[/tt]
You do not give an Order By. Did you have one in mind?

 

[Ration]

I would like it ordered by Agent Name ([File Reps].Firstname)ascending alphabetically if possible.

Thanks for all your help Remou!!!

 

[Remou]

Oops, misunderstanding, that is part 2! I mean Top 255 ordered by anything in particular? That is Top 255 starting at lowest date, at highest date, etc. Or just any 255?

 

[Ration]

Well that's a good question...Management would like to have 225 records selected at random during a months timeframe so any 225 would be fine.

I pasted the SQL and ran the query. The problem I'm having now is that I'm entering a date range of 4-1-06 and 4-30-06 but the query pulls records dated last year. I'm thinking I may need to use another field to count rather than date.

There's also a unique Customer Number for each record in the table. Do you think it would be better to count that as opposed to date?

 

[Remou]

Gosh, I made a complete mess of that! I hope this is better:

SELECT A.[File RepsID], A.Firstname, C.Errors, C.Financial, C.[Audit Date]
FROM [File Reps] AS A INNER JOIN Audits AS C ON A.[File RepsID] = C.[Agent Name]
WHERE (((A.[File RepsID]) In (Select top 255 [File RepsID] FROM [File Reps] B INNER JOIN Audits ON B.[File RepsID] = Audits.[Agent Name]  WHERE  B.[File RepsID]=A.[File RepsID] And Audit.AuditDate Between [Start Date] And [End Date])) AND ((C.[Audit Date]) Between [Start Date] And [End Date]));

What do you mean by count? If the date is not important there is no need to include it.

 

[Ration]

By count, I mean the 225 records that are accessed. I assumed you needed a count to get a specific number of records. I'm probably wrong about this too.

The importance of the date is to allow the entry of a date range. The query should only pull records that were entered within the specified dates. Having the date field on the records selected allows us to determine that Access has queried records for the dates we asked for.

Thanks again for everything Remou!

 

[Remou]

I will get this right ... eventually ... I hope.

SELECT C.ID, A.Firstname, C.Errors, C.Financial, C.[Audit Date]
FROM [File Reps] AS A INNER JOIN Audits AS C ON A.[File RepsID] = C.[Agent Name]
WHERE (((C.ID) In (Select top 2 Audits.[ID] FROM [File Reps] B INNER JOIN Audits ON B.[File RepsID] = Audits.[Agent Name]  
WHERE  B.[File RepsID]=A.[File RepsID] And Audits.[Audit Date] Between [Start Date] And [End Date])));

You need an ID from the Audits table for the whole thing to work properly. I have called the Audit table ID simply ID, this needs to be changed to the real name.

 

[Ration]

I replaced ID with Merchant Number (a unique number in the Audots table). Here's the code:

SELECT C.[Merchant Number], A.Firstname, C.Errors, C.Financial, C.[Audit Date]
FROM [File Reps] AS A INNER JOIN Audits AS C ON A.[File RepsID] = C.[Agent Name]
WHERE (((C.[Merchant Number]) In (Select top 225 Audits.[Merchant Number] FROM [File Reps] B INNER JOIN Audits ON B.[File RepsID] = Audits.[Agent Name]
WHERE B.[File RepsID]=A.[File RepsID] And Audits.[Audit Date] Between [Start Date] And [End Date])));

Unfortunately this hangs my system when I run it. I think it may have something to do with the "(Select top 225 Audits.[Merchant Number] FROM [File Reps] B INNER JOIN Audits ON B." The Merchant Number is in the Audits table and not the File Reps table.

I know we're getting close. Here's how my tables are setup:

Audits contains the following columns:

Audit ID - AutoNumber
Audit Date - Date/Time
Date Keyed - Date/Time
Auditor - Number (linked to Auditors table)
Agent Name - Number (linked to File Reps table)
Merchant Number - Text (This number is assigned by another system but is unique)
Merchant DBA - Text
Type of Maintenence - Number (linked to Maintenance Type table)
Merchant Type - Number (linked to Merchant Type table)
Errors - Yes/No
Financial - Yes/No
Description of Error - Memo
Action taken - Memo


File Reps contains the following columns:

File RepsID - AutoNumber
FirstName - Text (actually contains rep full name)
Auditor - Number (linked to Auditor table)


The way it works is each File Rep completes between 300 and 1000 financial and non-financial audited requests per month. The purpose of the query is to select the same number of audited requests (225) for each File Rep. These selected requests are used to generate an error ratio for both financial and non-financial requests. Anyone who exceeds a 2% error ratio is slated for remedial training.

Creating the error report is easy but getting the query to select the criteria is proving to be quite a challenge. Once again, thanks for your help with this!

 

[Remou]

I tried a rough mock-up and it seems to work albeit v..e..r..y slowly. Does Merchant Number always have an entry? How about trying with, say, 20 rather than 255 and Audit ID rather than Merchant Number, to test. I will see if I can think of something faster.

 

[Ration]

Merchant Number always has an entry but Audit ID can be used as well assuming an AutoNumber won't mess up the count. I'm going to try running this with top 20 and see if I can get my workstation to complete the query without hanging up. If so, I guess I'll bug IT for more memory and try it with the 225.

Again Remou, you've been an invaluable help with this! I may just learn this SQL thing yet.

 

[Remou]

I might learn, too!