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

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 UserCode...so

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!
LVL 6
cjinsocal581Asked:
Who is Participating?
 
sameer2010Connect With a Mentor Commented:
Try this
INSERT INTO TARGET_TABLE
(
SELECT UserName, UserCode, SUM(TimeLogged)
FROM SOURCE_TABLE
GROUP BY UserName, UserCode
)

Open in new window

0
 
cjinsocal581Author Commented:
Worked thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.