?
Solved

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

Posted on 2009-12-16
2
Medium Priority
?
242 Views
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 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!
0
Comment
Question by:cjinsocal581
2 Comments
 
LVL 13

Accepted Solution

by:
sameer2010 earned 2000 total points
ID: 26063874
Try this
INSERT INTO TARGET_TABLE
(
SELECT UserName, UserCode, SUM(TimeLogged)
FROM SOURCE_TABLE
GROUP BY UserName, UserCode
)

Open in new window

0
 
LVL 6

Author Closing Comment

by:cjinsocal581
ID: 31666840
Worked thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

749 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