Combine Duplicates based on Hierarchy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm pulling records from an oracle dbase into Access 2000 for weekly
reporting and analysis. Due to the structure of the data there are
duplicates because of relationships between multiple tables etc. I need to
combine the duplicate records into one (summing dollar amounts etc) and
assign it to a single call type based on a heirarchy. If Service Request
(SR) 12345 has 2 records with call type W and R the records would be combined
into a single record with call type W. Duplicates like this are the
exception not the rule, but there are too many to do manually every week.

I've searched and searched and thought and thought. The best I've come up
with starts liike this. Grab all the records into a temporary table. Append
the singletons to the permanent table and remove them from the temporary
table. Use the temporary table with just duplicates to create a recordset
sorted by SR number and call type hierarchy. Do some magic here....

At this point my brain shuts off - can anyone just give me a jump start???

Thanks!

Karrie
 
Instead of temporary tables, can you just create one cross-reference table;
something like this:

OracleCallType AccessCallType
W R
R R

Assuming you can create an ODBC link to the Oracle DB, what about just
creating an append query, turning on totals, grouping by AccessCallType, and
summing the appropriate field?



Then, you can include this in your query and group by the AccessType.
 
Thanks for thinking about my problem!!!

I hadn't thought about that approach and I love it! But I don't think it
will work for this problem. The records can have any combination of 5 call
types. And "they" want the most important of the available call types used.
So if it's W and R use W, if it's R and U use R. etc..

The order is W, M, R, B, U.

Any new thoughts?
 
I started replying & may have posted accidentally halfway through, because my
(rather lengthy) post disappeared suddenly. Here it is again, replete with
sample table structure & data; just follow the example through, then apply to
your specifics when you see how it works:

Create two tables:

Table: CallTypes
Fields: CallType (Text) Level (Integer)

Records W 1
M 2
R 3
B 4
U 5

Table: SR
Fields: SR CallType Amount (Currency)

Records 1 B $100
1 W $275
2 U $ 23
2 R $981
3 R $ 3
3 W $ 76

Create two queries:

Query: FindMin
SQL:

SELECT Calls.SR, Min(CallTypes.Level) AS [Level]
FROM Calls INNER JOIN CallTypes ON Calls.CallType = CallTypes.CallType
GROUP BY Calls.SR;

Query: SRTotals
SQL:

SELECT FindMin.SR, CallTypes.CallType, Sum(Calls.Amount) AS Amount
FROM Calls INNER JOIN (CallTypes INNER JOIN FindMin ON CallTypes.Level =
FindMin.Level) ON Calls.SR = FindMin.SR
GROUP BY FindMin.SR, CallTypes.CallType;

You now have one entry for each SR that includes only the highest-priority
call type with the Amounts totaled.
 
Thanks so much. That's exactly what I needed. I knew I was overthinking
this!!!



Brian said:
I started replying & may have posted accidentally halfway through, because my
(rather lengthy) post disappeared suddenly. Here it is again, replete with
sample table structure & data; just follow the example through, then apply to
your specifics when you see how it works:

Create two tables:

Table: CallTypes
Fields: CallType (Text) Level (Integer)

Records W 1
M 2
R 3
B 4
U 5

Table: SR
Fields: SR CallType Amount (Currency)

Records 1 B $100
1 W $275
2 U $ 23
2 R $981
3 R $ 3
3 W $ 76

Create two queries:

Query: FindMin
SQL:

SELECT Calls.SR, Min(CallTypes.Level) AS [Level]
FROM Calls INNER JOIN CallTypes ON Calls.CallType = CallTypes.CallType
GROUP BY Calls.SR;

Query: SRTotals
SQL:

SELECT FindMin.SR, CallTypes.CallType, Sum(Calls.Amount) AS Amount
FROM Calls INNER JOIN (CallTypes INNER JOIN FindMin ON CallTypes.Level =
FindMin.Level) ON Calls.SR = FindMin.SR
GROUP BY FindMin.SR, CallTypes.CallType;

You now have one entry for each SR that includes only the highest-priority
call type with the Amounts totaled.


KJGinNC said:
Thanks for thinking about my problem!!!

I hadn't thought about that approach and I love it! But I don't think it
will work for this problem. The records can have any combination of 5 call
types. And "they" want the most important of the available call types used.
So if it's W and R use W, if it's R and U use R. etc..

The order is W, M, R, B, U.

Any new thoughts?
Click to expand...
Click to expand...
 
Back
Top