Solved

Consolidate Table

Posted on 2007-11-19
4
658 Views
Last Modified: 2008-02-01
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
Comment
Question by:MDauphinais1
  • 2
4 Comments
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 20315367
See this link for a step by step
          http://www.fabalou.com/Access/Queries/delete_dupe_records.asp
0
 

Author Comment

by:MDauphinais1
ID: 20315489
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
 

Author Comment

by:MDauphinais1
ID: 20317591
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
 
LVL 4

Expert Comment

by:MrXmas
ID: 20317700
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now