Prev: Need Help
Next: vertical line between columns in report
From: Rich Stone on 6 May 2010 09:53
Sorry the subject is a bit vaugue!

Basically, I have a spreadsheet listing clients and their visits to my
workplace. I need to find the top 10% for times visited. To start with, I
have inported the spreadsheet into Access 2003 and produced a report that
groups each client using the sorting/grouping method and so I am able to get
a list with each client listed once and the footer of each group shows a
count of their visits. However, I am now stuck as to how to sort these
clients into the order of most visited.

Any ideas would be extremely helpful! Thanks in advance...

Richard
From: KenSheridan via AccessMonster.com on 6 May 2010 12:47
Think in terms of the reports underlying query rather than the report. If
the tables were correctly normalized into Clients and Visits tables then you
could return the top 10% with:

SELECT *
FROM Clients
WHERE ClientID IN
(SELECT TOP 10 PERCENT ClientID
FROM
(SELECT ClientID, COUNT(*)
FROM Visits
GROUP BY ClientID
ORDER BY COUNT(*) DESC));

But as you presumably have just the one table derived from the Excel
worksheet it would be:

SELECT *
FROM YourTable
WHERE ClientID IN
(SELECT TOP 10 PERCENT ClientID
FROM
(SELECT ClientID, COUNT(*)
FROM YourTable
ORDER BY COUNT(*) DESC));

To include the number of visits per client add a subquery:

SELECT *,
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.ClientID = T1.ClientID) AS NumberOfVisits
FROM YourTable AS T1
WHERE ClientID IN
(SELECT TOP 10 PERCENT ClientID
FROM
(SELECT ClientID, COUNT(*)
FROM YourTable
ORDER BY COUNT(*) DESC));

You can then order the report first on NumberOfVisits in descending order,
then group on ClientID to show the clients who have made most visits first.

You might find the above queries quite slow, but that will depend on the
number of rows in the table.

Ken Sheridan
Stafford, England

Rich Stone wrote:
>Sorry the subject is a bit vaugue!
>
>Basically, I have a spreadsheet listing clients and their visits to my
>workplace. I need to find the top 10% for times visited. To start with, I
>have inported the spreadsheet into Access 2003 and produced a report that
>groups each client using the sorting/grouping method and so I am able to get
>a list with each client listed once and the footer of each group shows a
>count of their visits. However, I am now stuck as to how to sort these
>clients into the order of most visited.
>
>Any ideas would be extremely helpful! Thanks in advance...
>
>Richard

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1

From: a a r o n . k e m p f on 6 May 2010 13:08
SQL Server Reporting Services allows you to have 'interactive sorting'
in your reports

it doesn't take any coding.. it doesn't require another call to the
database server

Thanks

-Aaron




On May 6, 6:53 am, Rich Stone <RichSt...(a)discussions.microsoft.com>
wrote:
> Sorry the subject is a bit vaugue!
>
> Basically, I have a spreadsheet listing clients and their visits to my
> workplace. I need to find the top 10% for times visited. To start with, I
> have inported the spreadsheet into Access 2003 and produced a report that
> groups each client using the sorting/grouping method and so I am able to get
> a list with each client listed once and the footer of each group shows a
> count of their visits. However, I am now stuck as to how to sort these
> clients into the order of most visited.
>
> Any ideas would be extremely helpful! Thanks in advance...
>
> Richard

 | 
Pages: 1
Prev: Need Help
Next: vertical line between columns in report