Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • 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]
ID
Event
BookingID
AVTech

[Time Tracker]
ID
Date
Minutes
OTMinutes
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.

So:
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?
0
MDauphinais1
Asked:
MDauphinais1
  • 2
1 Solution
 
puppydogbuddyCommented:
See this link for a step by step
          http://www.fabalou.com/Access/Queries/delete_dupe_records.asp
0
 
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.
0
 
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!
0
 
MrXmasCommented:
MDauphinais1,

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,
     BookingID,
     AVTech,
     Event
INTO Temp_DuplicateRows
FROM [Time Tracker Event Details]
GROUP BY BookingID,
     AVTech,
     Event
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).

DELETE Dtls.*
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
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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