Random selections (1 Viewer)

[Rich_Lovina]

Okay Ive been away from Access for a while, and tried to reslove this problem some time ago. Consider 50,000 employees, in 650 office locations, where I want to select top (by salary field) 5 employees from each office; but some offices have 7500 at one address and others <5; perhaps only one record.

Is this a query on a query, as Group By throws me every time on this one.
Any help gratefully appreciated

 

[dcx693]

How about a top values query? Set the Top Values option in the query properties to 5.

 

[indyaries]

Here is the SQL to put into a query to return random records every time. The Rnd() function uses the EmployeeID minus the figure returned by the Timer() function as the seed, so that every time the DB is opened, the Rnd() function starts with a different seed. And so different XX random records will be returned every time the DB is opened.

SELECT TOP 80 *
FROM tTEMP_Current_Employees
ORDER BY Rnd(([EmployeeID])-Timer());


The eBizID number can be used as below:
SELECT TOP 80 *
FROM tTEMP_Current_Employees
ORDER BY Rnd(Right([bUserID],3)-Timer());


In the above example, the EmployeeID field is the autonumber field. The eBizID is a text field where the last three characters are always numbers.

Adjust the SQL as needed. HTH !!

P.S. I fould this example on this forum. Credit the original poster.

 

[Rich_Lovina]

Thanks for suggestions, but neither right when MAIN table has NAME_ID (Autonr), POSCODE (Links to table POSITIONS and field to sort on SALARY_TO) and the office addresses are all in DEPTNAMES (linked by DEPTCODE) and in MAIN.DEPT_ID.

Fields to display are: DEPTNAMES.DEPTNAME, DEPTNAMES.CITY, POSCODE.TITLE,POSCODE.SALARY_TO, MAIN.NAME_ID.

Using sigma (Group By) displays all, and Sigma (Top 5) shows only top 5 from all records. I want Top 5 by Poscode.Salary_To in each Deptnames.City.

Thanks any suggestions

 

[normajean]

Hey Rich_Lovina:
Here is a zipped up 2000 Access database (SampleTop5.zip) that has a solution to your problem. I've had to do these myself, and the cleanest way I found was to create a Temp table from a Make table query, where the last column is created by calling a function (which I've included in the zipped database). The query groups by city, and descending salary, and then the function marks the top 5 within each city. You have to save to a temp table, because you can't use query criteria against the results of the function.
1. Run query: QryHighest5SalariesbyCity2
2. use query: QryTopSalariesReport for your report
Hope this will help you out, Normajean

 

[Rich_Lovina]

Thanks greatly, I think this will do the job perfectly...

 

[normajean]

Hi Rich:
Glad it helped you out. You know you might want to change the function to return an integer with the actual ordinal position (1....n) - instead of a string with 'Yes' or 'No', and then change your final query's criteria for that field to 'between 1 and 5'
I had it that way at first, but couldn't apply criteria against it the field (I had forgotten that you can't apply criteria against a function generated field). I changed the integer returned to a 'yes'/'no' just to experiment. Then I remembered that I had to do a make tble first. Its probably a better design to save the actual ordinal number – then you could just change report criteria if the need changed.
Here is function code:
Function AssignNumbers(deptcity As String) As Integer
Static strDeptCity As String
Static intNumber As Integer
If strDeptCity <> deptcity Then 'first time through or new deptcity
intNumber = 1
strDeptCity = deptcity
Else 'still on the same deptcity, increment counter
intNumber = intNumber + 1
End If
AssignNumbers = intNumber
End Function
RE: the problem with running the function in your database: When you finished copying the function over to your database, did you compile it? (Option under Debug menu). I think thats happened to me when I hadn't compliled in the past??
Normajean

 

[Rich_Lovina]

I still keep getting on this field in my table UNDEFINED FUNCTION on AssignNumbers

deptCitySalaryNumber: AssignNumbers([deptcity])

 

[normajean]

Did you compile the vb code (Option under Debug menu in the vb code window). I think thats happened to me when I hadn't compliled in the past?? Until you compile it, vb doesn't know its there, even if you save the code, it can't run it.
Normajean