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?
Start Free Trial
