Results 1 to 10 of 10

Thread: Access Help

  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Angry Unanswered: Access Help

    I currently have a table with 3 fields, ComputerName, Date, and UserName. The purpose is to keep a count of how many users log into the same computer, and after finding the max, I put that data (username and computer name) into a new table. I have about 3,000 records in this table as well.

    I'm trying different methods in the sql view but have no idea. Any suggestions?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not trivial.

    Create a query (User_Computer_Counts)
    Code:
    SELECT    UserName
            , ComputerName
            , COUNT(*) AS daCount
    FROM myTable
    GROUP BY username, computername
    A second query:
    Code:
    SELECT    User_Computer_Counts.UserName
            , User_Computer_Counts.ComputerName
    FROM    
            (
                SELECT    UserName
                        , ComputerName
                        , MAX(daCount) AS daMaxCount
                FROM    User_Computer_Counts
                GROUP BY  username
                        , computername
            ) AS MaxCounts
    INNER JOIN
            User_Computer_Counts
    ON  User_Computer_Counts.UserName       = MaxCounts.UserName
    AND User_Computer_Counts.ComputerName   = MaxCounts.ComputerName
    EDIT - error in second query
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    39

    Thanks

    Thanks. I didn't think it was a trivial problem but things are trivial when you're brand new to the matter. Thanks a lot.

  4. #4
    Join Date
    Jul 2009
    Posts
    39

    Tie Situations

    Dealing with counting how many users logged into the same machine, and then finding the maximum, I'd like to know how to edit the MaxCount query for tie situations. Basically, if 4 different users log into the same machine (each having a count of 1 of course) all of them should be displayed in the MaxCount query table.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok. You've actually picked up an error in my code. It finds the computer a person logs on to the most.
    Code:
    SELECT    User_Computer_Counts.UserName
            , User_Computer_Counts.ComputerName
    FROM    
            (
                SELECT    ComputerName
                        , MAX(daCount) AS daMaxCount
                FROM    User_Computer_Counts
                GROUP BY  computername
            ) AS MaxCounts
    INNER JOIN
            User_Computer_Counts
    ON User_Computer_Counts.ComputerName   = MaxCounts.ComputerName
    I think this is correct.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2009
    Posts
    39
    The previous code you gave me did work, it was just situations with an equal amount of different users on the same computer.

    Also, I find that sometimes, a user has logged onto another machine, this new code won't interfere with that will it? Meaning, it won't look that user "John" logged into "Dell" 4 times and into "Mac" 2 times, and then just throw out "Mac" right?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I think that's what the original code did.

    This (I think - I am tired, hungry and writing C# so my brain's a bit frazzled) will find the top user(s) of each machine and show them. If pootle used two machines more than any other user, pootle will appear twice. Lucky you
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2009
    Posts
    39
    Thank you very much sir, it works perfect now. And also thanks for the clutch responses.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No idea what clutch responses are but glad you liked them
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2009
    Posts
    39
    lol clutch, means to pull through in an emergency faster than usual, like how Michael Jordan was a clutch ball player when his team is down by 2 points with 10 seconds left in the 4th quarter.

    One last thing, can you explain the JOIN, cause JOINS are usually when comparing 2 or more tables for something common and then creating a new table from that. This has me a little confused.
    Last edited by queenzNYdude; 07-31-09 at 12:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •