Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Consolidate Table

Posted on 2007-11-19
4
Medium Priority
?
668 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

664 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