If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: NEED HELP! with sql query

Hi Everyone,
I am working on a database and need to work out a query that would display the most used applications in my company per month. These need to be ranked by the top 10 most used applications.

I am not very good with sql as it is a new language to me and need your help.

Here are the details:

Entities:
ApplicationTbl:

AppID as an AutoNumber(PK)
AppName as a Text

SessionTbl:

SessionID as an AutoNumber(PK)
SessionStart as a Date/Time
SessionEnd as a Date/Time
TotalTimeSum as a number
ActiveTimeSum as a number
AppID as number (FK)

From these two tables I have worked out how to count the usage of each application, but don't know how to rank them.

Also any application name after the 10th row needs to be named as 'other'

You need more than the top ten then. You need all of them. The complication here is for 11 plus to be marked "other". If you could get rid of this requirement then it is trivial (see my post #2). If you cannot then it is more complicated when using Access SQL. Other SQL dialects make this a reasonably easy task.

Please could you also tell me what you want to have happen in the event of a tie for 10th place?
EDIT - you posted before I had finished typing.

The 11 plus row is compulsary, allthough its very unlikely that there will be a duplicate value due to the size of the application usage numbers, however if it does occur then the first selected application could be chosen.