troubleshooting Question

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

Avatar of cjinsocal581
cjinsocal581Flag for United States of America asked on
2 Comments1 Solution264 ViewsLast Modified:
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!

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros