1 Table 1 Query 1 simple answer

[ktariske]

Ok, quick and to the point. I have one table. It has 3 fields. It has: employee name, visitor name, and date.

All i want is to run a report with a list of employee names, sorted by the highest number of visitors down to lowest, with employee name.

I can add the sum in the report footer for this, and it will group the visitor for employee but it sorts it by employee. I need it sorted by # of visitors from high to low.

Why does this seem so complicated? Do I need to create a query object, and if so, how do i run it so it groups the employee with his/her visitors/?

I used to love Access, my brain is frozen today.

Thanks.

 

[WayneRyan]

k,

It's possible in one query (sort of), but here's the 2 query version.

Query1:

Select [employee name] As Employee,
       Count(*) As NumberOfVisitors
From   YourTable
Group By [employee name]

Query2:

Select A.Employee, 
       A.NumberOfVisitors,
       B.[Visitor name],
       B.[Date]
From   Query1 As A Inner Join YourTable As B On
       A.Employee = B.[employee name]
Order By A.NumberOfVisitors Desc, [employee name]

I hope those aren't the real names, Date is a reserved word and the others
have spaces.

hth,
Wayne