Trying to pull the top number of vists AND the assocaited user's nameby client

A

adamctaylor

Hello everyone-

I am trying to pull the name and number of visits for the top visitor
to a website by client and I am having trouble.

Each client can have more than one user. My goal end result is:

ClientAdminID UserName Visits
-------------------------------------------------------
1 Doe, John 9999
2 Doe, Sue 9999

etc...

I am using the following:

SELECT tblLBEUsers.ClientAdminID, tblLBEUsers.UserNamr, tblLBELogons.
[2007]
FROM tblLBEUsers INNER JOIN tblLBELogons ON tblLBEUsers.UserID =
tblLBELogons.UserID
WHERE ( tblLBELogons.[2007]=(SELECT a.ClientAdminID, Max(b.[2007]) AS
MaxOf2007
FROM tblLBEUsers AS a INNER JOIN tblLBELogons AS b ON a.UserID =
b.UserID
GROUP BY a.ClientAdminID ))
GROUP BY tblLBEUsers.ClientAdminID;

And now I am getting some message about needing an EXISTS clause in
the main query's FROM clause. From other threads it sounds like this
might not really be what is going on. Can anyone help me out?

TIA,

Adam
 
J

Jeanette Cunningham

Adam,
I made the 2 tables you mentioned and used this query:

SELECT tblLBEUsers.ClientAdminID, tblLBEUsers.UserName,
Sum(tblLBELogons.[2007]) AS SumOf2007
FROM tblLBEUsers INNER JOIN tblLBELogons ON tblLBEUsers.ClientAdminID =
tblLBELogons.UserID
GROUP BY tblLBEUsers.ClientAdminID, tblLBEUsers.UserName;

the results were:
ClientAdminID UserName 2007
1 aaa 261
2 bbb 128
3 ccc 99

Hope this is what you want

Jeanette Cunningham
 
A

adamctaylor

Adam,
I made the 2 tables you mentioned and used this query:

SELECT tblLBEUsers.ClientAdminID, tblLBEUsers.UserName,
Sum(tblLBELogons.[2007]) AS SumOf2007
FROM tblLBEUsers INNER JOIN tblLBELogons ON tblLBEUsers.ClientAdminID =
tblLBELogons.UserID
GROUP BY tblLBEUsers.ClientAdminID, tblLBEUsers.UserName;

the results were:
ClientAdminID UserName 2007
1 aaa 261
2 bbb 128
3 ccc 99

Hope this is what you want

Jeanette Cunningham


Hello everyone-
Click to expand...
I am trying to pull the name and number of visits for the top visitor
to a website by client and I am having trouble.
Click to expand...
Each client can have more than one user. My goal end result is:
Click to expand...
ClientAdminID UserName Visits
-------------------------------------------------------
1 Doe, John 9999
2 Doe, Sue 9999

I am using the following:
Click to expand...
SELECT tblLBEUsers.ClientAdminID, tblLBEUsers.UserNamr, tblLBELogons.
[2007]
FROM tblLBEUsers INNER JOIN tblLBELogons ON tblLBEUsers.UserID =
tblLBELogons.UserID
WHERE ( tblLBELogons.[2007]=(SELECT a.ClientAdminID, Max(b.[2007]) AS
MaxOf2007
FROM tblLBEUsers AS a INNER JOIN tblLBELogons AS b ON a.UserID =
b.UserID
GROUP BY a.ClientAdminID ))
GROUP BY tblLBEUsers.ClientAdminID;
Click to expand...
And now I am getting some message about needing an EXISTS clause in
the main query's FROM clause. From other threads it sounds like this
might not really be what is going on. Can anyone help me out?

Adam
Click to expand...
Click to expand...

Jeanette, thanks for the help, there is one wrinkle. A Client can
have 1 to many Users - This query pulls ALL the users under a client I
need to only pull the top user....

Any thoughts out there?
 
Top