• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

Consolidate Table

OK, here's what I've got...

I have two tables that track time technicians spend on meetings:

[Time Tracker Event Details]

[Time Tracker]
IDReplica  <-- This is the linked field to [Time Tracker Event Details]

The way this works is that the table [Time Tracker Event Details] stores the information about each event and the table [Time Tracker] stores the related time each tech spends on each event. The two tables are linked together with the ID field in [Time Tracker Event Details] and the IDReplica field in [Time Tracker].

What I need to do is this: Some of the events in the [Time Tracker Event Details] table have duplicate entries because users created a new entrry when they had time to enter instead of going to an existing event and just adding to it. I can't simply remove these entries because they are linked to time entries in the [Time Tracker] table.

Somehow I need to basically find any BookingID and AVTech combination that matches another entry in the [Time Tracker Event Details] table, delete all but 1 of the records, and then replace the IDReplica values in the [Time Tracker] table for all the events that were removed from the [Time Tracker Event Details] table with the ID of the event that was not deleted. So that now all time in the [Time Tracker] table that used to match up with duplicated entries now all match the same entry.

Keeping in mind that is it OK to have seperate entries in the [Time Tracker Event Details] table for the same event where the technican names are different.

ID    BookingID       AVTech         Event
45    123456             Joe            My Event
46    123456             Joe            My Event

Is a duplicated event entry.

ID    BookingID       AVTech         Event
47    123456             Joe            My Event
48    123456            John           My Event

Is Not a duplicated event entry.

So keeping with the examples above, for the duplicated entry example, I would want to replace all IDReplica values in the [Time Tracker] table where the IDReplica value was 46 with 45 (the first record of the duplicates) and then delete record 46 from the [Time Tracker Event Details] table.

How can I do this the easiest way possible?
  • 2
1 Solution
See this link for a step by step
MDauphinais1Author Commented:
Thanks. But what about the second half of my issue?  How to I replace the IDReplica values in the [Time Tracker] table with the ID of the "first" record for each set of duplicates from the [Time Tracker Event Details] table only where the current IDReplica matches one of the ones considered "duplicates".

So for example, say I have this in my [Time Tracker Event Details] table:

ID    BookingID       AVTech         Event
45    123456             Joe            My Event
46    123456             Joe            My Event
47    123456             Joe            My Event
48    111222             Joe            My Event
49    111222             Joe            My Event
50    111222             Joe            My Event

And this in my [Time Tracker] table:

ID         IDReplica       Minutes         OTMinutes            Date
101          45                120                  10              11/30/2006
102          46                140                   5              10/30/2006
103          47                160                  15              09/30/2006
104          48                 80                   19              08/30/2006
105          49                100                  60              07/30/2006
106          50                120                  20              06/30/2006

In the [Time Tracker] table I would want to change the IDReplica values of records  102, 103  to a value of "45" and records 105. 106 to a value of "48".    So when I go through the [Time Tracker Event Details] table and remove all duplicated records I don't have dead end records in the [Time Tracker] table.
MDauphinais1Author Commented:
Ahh... I figured out how to get it done. I had to create a common "group" ID between the two tables so I could relate them as groups and not just individual files. It took me a few hours to think about but only a few minutes to implement once I figured it out. Then I could use your resource to delete the dups from the first table.  Thanks!

First, two quick notes...  If you can avoid having spaces in your table or field names it makes for easier programming.  Second, you may want to change the name of your "Date" field to something else.  "Date" is a reserved word and might cause you problems as you add more code to your database.

I'm done with my soap box now.  You'll need to create a temporary table to help you with all of this:

SELECT Count(ID) AS CountOfID,
     Min(ID) AS MinOfID,
INTO Temp_DuplicateRows
FROM [Time Tracker Event Details]
HAVING (Count(ID)>1);

That statement will give you a table called "Temp_DuplicateRows" that will contain the first row of all the problem children.  From there you can update your Time Tracker with a query like this:

UPDATE (Temp_DuplicateRows AS Temp
     INNER JOIN [Time Tracker Event Details] AS Dtls
          ON (Temp.Event = Dtls.Event)
                AND (Temp.AVTech = Dtls.AVTech)
                AND (Temp.BookingID = Dtls.BookingID))
     INNER JOIN [Time Tracker] AS TT ON Dtls.ID = TT.IDReplica
SET TT.IDReplica = [Temp].[MinOFID];

(Make a backup for yourself before you run that - please).

Now...  You're free to delete the duplicate rows in the Event Details table with:
(And again...  Make sure you backup first).

FROM [Time Tracker Event Details] AS Dtls
WHERE ((((SELECT Count(ID) as DelRows FROM Temp_DuplicateRows AS Temp WHERE Temp.MinOfID <> Dtls.ID AND Temp.BookingID = Dtls.BookingID AND Temp.AVTech = Dtls.AVTech AND Temp.Event = Dtls.Event;))>0));

Hope all that helps,

--Jim Christmas
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.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now