Top Ten Filter

[fapa via AccessMonster.com]

Hey Everyone

Wondering if someone can help...

I've got a form that shows a list of employees in the company, and the number
of shares each employee has been issued. Each employee is even a certificate
(share_cert_number) with the number of shares their allowed.

I'm trying to create a sort of Top 10 employees list which is defined as
employees with the most shares. At present, i can create a top 10 list
however many employees names reappear on it as they have been issued lots of
share certificates with many shares. What i'd like is a list which shows 10
individual employees with the TOTAL value of their shares (hence total value
of all share certs issued to them) can someone help on how this can be done?

the fields on the form are:
Employee_number
first_name
Last_name
no_of_shares

thanks

 

[Al Campagna]

fapa,
Using the query design grid, create a Totals query to sum each employees shares, and
sorts that result by SumofShares Descending. When that works OK, then simply use the Top
combo (10) to see the top ten.
EmpNo Last First Shares
GroupBy GroupBy GroupBy Sum
Desc
How you decide to "display" this data to the user is up to you. The above query could
feed a pop-up form, report, list box,... etc...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[fapa via AccessMonster.com]

Thanks Al

I edited the query however when I put "sum" for Shares - it didnt change the
results. The same employees are still appearing on the list.

Can you suggest anything else?

fapa,
Using the query design grid, create a Totals query to sum each employees shares, and
sorts that result by SumofShares Descending. When that works OK, then simply use the Top
combo (10) to see the top ten.
EmpNo Last First Shares
GroupBy GroupBy GroupBy Sum
Desc
How you decide to "display" this data to the user is up to you. The above query could
feed a pop-up form, report, list box,... etc...

Hey Everyone

[quoted text clipped - 18 lines]

thanks

 

[Al Campagna]

fapa,
Please be specific. "I edited the query" doesn't really tell me what you did...
What I suggested should work (I tested)
Did you select 10 for a TOP value?

*For now, just work with EmployeeID, and Shares...
Without any TOP value at all, does your query deliver one line (and one line only) for
every employee, and show the total shares properly...
Data
-------
Smith 100
Davis 50
Jones 100
Smith 100

Total Query
Query results (descending share sum)
------------
Smith 200
Jones 100
Davis 50

Selecting a TOP of 2 would yield...
Smith 200
Jones 100

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thanks Al

I edited the query however when I put "sum" for Shares - it didnt change the
results. The same employees are still appearing on the list.

Can you suggest anything else?

fapa,
Using the query design grid, create a Totals query to sum each employees shares, and
sorts that result by SumofShares Descending. When that works OK, then simply use the
Top
combo (10) to see the top ten.
EmpNo Last First Shares
GroupBy GroupBy GroupBy Sum
Desc
How you decide to "display" this data to the user is up to you. The above query
could
feed a pop-up form, report, list box,... etc...

Hey Everyone

[quoted text clipped - 18 lines]

thanks