Find Duplicates in one column and sum other column

[Boss]

Hi,

I have a master table with Five columns. I would import multiple files of
excel into one master table in access. The field in master table are
mentioned below. Case ID is not the primary key.

CaseID, Name, empID, activity, time taken

What i need is-

If case ID,Name & Activity is similar in two or more records then I need to
merge all of those records into one record with the sum of time taken.

Idea is if an employee has worked on the same activity & same case twice or
"n" times then i need to grab the total time in the table.

I can so this on excel using concatenate & sumif function, please help me do
this in access. Thanks as always!

Thx!
Boss

 

[John Spencer MVP]

If the three fields have equal values then this is simple. If the fields have
SIMILAR value then this is complex.

SELECT [Case Id], [Name], [Activity], Sum([Time Taken]) as TotalTime
FROM [Some Table]
GROUP BY [Case Id], [Name], [Activity]

In query design view
-- Add the table
-- Add the four fields
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under Time Taken field

If you want to do this with SIMILAR values - define how you determine
similarity and give some examples.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer MVP]

If the three fields have equal values then this is simple. If the fields have
SIMILAR value then this is complex.

SELECT [Case Id], [Name], [Activity], Sum([Time Taken]) as TotalTime
FROM [Some Table]
GROUP BY [Case Id], [Name], [Activity]

In query design view
-- Add the table
-- Add the four fields
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under Time Taken field

If you want to do this with SIMILAR values - define how you determine
similarity and give some examples.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Boss]

Thanks John... you made my day...


Thx! as always...
Boss

If the three fields have equal values then this is simple. If the fields have
SIMILAR value then this is complex.

SELECT [Case Id], [Name], [Activity], Sum([Time Taken]) as TotalTime
FROM [Some Table]
GROUP BY [Case Id], [Name], [Activity]

In query design view
-- Add the table
-- Add the four fields
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under Time Taken field

If you want to do this with SIMILAR values - define how you determine
similarity and give some examples.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a master table with Five columns. I would import multiple files of
excel into one master table in access. The field in master table are
mentioned below. Case ID is not the primary key.

CaseID, Name, empID, activity, time taken

What i need is-

If case ID,Name & Activity is similar in two or more records then I need to
merge all of those records into one record with the sum of time taken.

Idea is if an employee has worked on the same activity & same case twice or
"n" times then i need to grab the total time in the table.

I can so this on excel using concatenate & sumif function, please help me do
this in access. Thanks as always!

Thx!
Boss

 

[Boss]

Thanks John... you made my day...


Thx! as always...
Boss

If the three fields have equal values then this is simple. If the fields have
SIMILAR value then this is complex.

SELECT [Case Id], [Name], [Activity], Sum([Time Taken]) as TotalTime
FROM [Some Table]
GROUP BY [Case Id], [Name], [Activity]

In query design view
-- Add the table
-- Add the four fields
-- Select View: Totals from the menu
-- Change GROUP BY to SUM under Time Taken field

If you want to do this with SIMILAR values - define how you determine
similarity and give some examples.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a master table with Five columns. I would import multiple files of
excel into one master table in access. The field in master table are
mentioned below. Case ID is not the primary key.

CaseID, Name, empID, activity, time taken

What i need is-

If case ID,Name & Activity is similar in two or more records then I need to
merge all of those records into one record with the sum of time taken.

Idea is if an employee has worked on the same activity & same case twice or
"n" times then i need to grab the total time in the table.

I can so this on excel using concatenate & sumif function, please help me do
this in access. Thanks as always!

Thx!
Boss