Proper SQL in Access 2000 for combining duplicate items in a single Table

Posted on 2009-12-16
Last Modified: 2012-05-08
I have a table that has everytime a user logged into a specified account. the table is structured this way:

ID (Primary key) - AutoNumber
UserName - Text
UserCode - Text
DateTimeLogged - Text
TimeLogged (qty of mins) - Number

I need to create a SQL statement that will go through the data, combine the information based on userID and UserName and make a new table with the information that includes all the columns except for DateTimeLogged.

So, lets say user JohnDoe logged in three times on three different dates and I want to create a new table that JUST shows information about how long John was logged in for totally. Now stay with me here as I am going to throw a curve ball... We have multiple John Doe's in this example the key is name but the ultimate key is their

The data would be:
UserName      DateTimeLogged      TimeLogged      UserCode
John Doe        12/14/2009               14                     1459923
John Doe        12/15/2009               29                     1459923
John Doe        12/16/2009               39                     1459923
John Doe        12/16/2009               44                     0988888

I would like the new table to have the following data:
UserName      UserCode     TimeLogged
John Doe        1459923         82    
John Doe        0988888         44              

It is important that I can search on both the name and usercode to be sure it is pulling to correct information as I have multiple John Doe's :-)

Thanks for any help you can provide!
Question by:cjinsocal581
    LVL 13

    Accepted Solution

    Try this
    SELECT UserName, UserCode, SUM(TimeLogged)
    GROUP BY UserName, UserCode

    Open in new window

    LVL 6

    Author Closing Comment

    Worked thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now