Merging & removing duplicates

[Johin.b]

Hi Guys,



I have a table with few Employee ID re-appearing over and over again for two different job status.

Data Example

EMP ID Job Code Emp Name Country Job Status Time Spent (In Hrs)
222 9250 XYZ ABC Complete 6
222 1059 XYZ ABC InComplete 12
222 5050 XYZ ABC Complete 7
222 1050 XYZ ABC InComplete 9
111 2750 MNM IJK Complete 2
111 2150 MNM IJK InComplete 3
111 6550 MNM IJK Complete 7
111 8250 MNM IJK InComplete 9

I need an output which merges the data based on Emp ID, however, adds the time spent for completed and incompleted job status in 2 rows.

Output Requirement:

EMP ID Emp Name Country Job Status Time Spent (In Hrs)
222 XYZ ABC Complete 13
222 XYZ ABC InComplete 21
111 MNM IJK Complete 9
111 MNM IJK InComplete 12

Which query to use? I use Access 2010. Step by step info would greatly help.

Regards,
Joe

[June7]

Try an aggregate GROUP BY (Totals) query

SELECT [Emp ID], [Emp Name], Country, [Job Status], Sum([Time Spent (In Hrs)] AS TotHrs FROM tablename GROUP BY [Emp ID], [Emp Name], Country, [Job Status];

Access Help has guidelines on using the query builder to create aggregate query.