Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Combine records on condition but also delet on condition

    Hi all of you aswome access gurus..
    I`m finding myself migrating to access and learing all its useful features but i have something that is stomping me that might seem simple to some of you

    I have 2 tables TblMTD and TblDuplicates_Temp but will focus on the second one (TblDuplicates_Temp)

    This table is a result from a query that finds the duplicate accounts from the Rawdata in TblMTD

    The TblDuplicates_Temp has the following colums

    Emplnum,EmplName,Skill,Call_Center,Manager,Calls

    note that
    skill=what that employee sells
    call center =where they are located

    In this duplicate table the major difference in records is that sometimes and employee number appears twice but one of his records has calls and other data and another one does not.

    like this

    Emplnum,EmplName,Skill,Call_Center,Calls,attempts, promo,stat
    11153,Jon Do,internet,london,123,5,null,temp
    11153,John Do,cellular,india,0,9,low,perm

    What i would need is to COMBINE same employee number records but to KEEP ONLY the SKILL,CALLS,STAT (maybe others in the future)of the employee number with the most calls

    like this (as per the data above.

    Emplnum,EmplName,Skill,Call_Center,Calls,attempts, promo,stat
    11153,Jon Do,cellular,london,123,9,null,temp


    AND..... to delete all the records for AN emploYee number if there are no calls (i do not want to keep those records if some emplyees have 2 records but no calls)

    Thank you soooo much in advance...i am simply stuck at that point

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by RCMIRAN View Post
    What i would need is to COMBINE same employee number records but to KEEP ONLY the SKILL,CALLS,STAT (maybe others in the future)of the employee number with the most calls
    In your sample data:
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    Jon Do      internet    london           123           5    null    temp
    11153    John Do     cellular    india              0           9    low     perm
    The line with the most calls is Line 1 (123 calls). However, you also wrote that you expect the result to be:
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    Jon Do      cellular    london           123           9    null    temp
    Why (according to which rule or set of rules) do you retain (Skill = 'cellular') and (attemps = 9), that are values from Line 2, while on the line with the most calls (the only criteria you specify), which is Line 1, we have: (Skill = 'internet') and (attempts = 5)?
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    5

    I Appologise

    i realise that my inexperiance on forums has confused my cutting and pasting capabilities...
    The results i require would be more like this...

    This would be my sample data
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    Jon Do      internet    london           123           0    null    0
    11153    John Do     cellular    india              0           9    low     perm

    This is my required output as long as the rule is we take the Emplnum EmplName, Skill ,calls,of the person with the most calls..and merge the rest

    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    Jon Do      internet    london           123           9    null    perm


    Thank you so much for your time

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome but I still don't understand.

    Why do you keep (attempts = 9) and (stat = perm), that come from line 2, while the rule says to KEEP ONLY the SKILL,CALLS,STAT of the employee number with the most calls? "attempts" is not in the list of columns to be kept (moreover attemps = 9 is from line 2) and on the line with the most calls (line 1) "stat" = '0', not 'perm'.
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    5

    i see that you can see my difficulty

    my point is to merge whatever data is at 0 is replaced by whatever data available from the other line...

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This becomes clearer now. However, what if there is more than one line that does not contains the Max(Calls) value and that has different non-zero values in attempts and stat? Which one must provide the non-zero values?

    Example:
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    John Do     internet    london           123           0    null    0
    11153    John Do     cellular    india              0           9    low     xyz
    11153    John Do     cellular    india              10          8    low     perm
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Posts
    5

    more info

    This a callcenter environment. where th first and most important criteria is the calls and to keep the skills and related info of the reps who do the most calls.
    whatever other record that is null or 0 is to be replaced by whatever info is in the cell from the person with the most calls.or filled by the data that is there from the other recorset
    by looking at the data i have noticed that in all cases the data seems to be split between 2 records for the employee number

    1 set has the regular data upto the calls and the one`s other data is 0

    here is a better example
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    John Do     internet    london           123           0    0        0
    11153    John Do     cellular    india              0           9    low     xyz
    
    result <
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    John Do     internet    london           123           9    low     xyz
    example 2:
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11152    John Do     cellular    india              10          8    low     perm
    11152    John Do     internet    india             990          9    0       0
    
    
    result <
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11152    John Do     internet    india             990          9    low     perm
    example 3
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11158    John Doh    cellular    tunesia            89          8    0       0
    11158    John Do     cellular    india              10          0    low     perm
    
    
    result <
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11158    John Doh    cellular    tunesia            89          8    low     perm
    thanks again

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT TOP 1 s.Emplnum, 
                 s.EmplName, 
                 s.Skill, 
                 s.Call_Center, 
                 m.MaxOfCalls as Calls, 
                 m.MaxOfattempts as Attempts, 
                 m.MaxOfPromo as Promo,
                 m.MaxOfstat as Stat
    FROM ( 
           SELECT TblDuplicates_Temp.Emplnum, 
                  TblDuplicates_Temp.EmplName, 
                  TblDuplicates_Temp.Skill, 
                  TblDuplicates_Temp.Call_Center
           FROM   TblDuplicates_Temp
         ) AS s
    INNER JOIN (
           SELECT TblDuplicates_Temp.Emplnum, 
                  Max(TblDuplicates_Temp.Calls) AS MaxOfCalls, 
                  Max(TblDuplicates_Temp.attempts) AS MaxOfattempts, 
                  Max(TblDuplicates_Temp.promo) AS MaxOfPromo,
                  Max(TblDuplicates_Temp.stat) AS MaxOfstat
           FROM   TblDuplicates_Temp
           GROUP BY TblDuplicates_Temp.Emplnum
               ) AS m
    ON s.Emplnum = m.Emplnum;
    Have a nice day!

  9. #9
    Join Date
    Nov 2011
    Posts
    5

    Input

    thank you for your wonderful input Sinndho
    but this code poses a challenge as all the recordsets would be in 1 table as you can se in the following snippet thus the SELECT TOP 1 commande would not select the other records if i understand correctlty..thanks for youre awsome help
    Code:
    Emplnum  EmplName    Skill       Call_Center    Calls    attempts    promo   stat
    ---------------------------------------------------------------------------------
    11153    John Do     internet    london            123          0    0        0
    11153    John Do     cellular    india               0          9    low     xyz
    11152    John Do     cellular    india              10          8    low     perm
    11152    John Do     internet    india             990          9    0       0
    11158    John Doh    cellular    tunesia            89          8    0       0
    11158    John Do     cellular    india              10          0    low     perm

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Ok ! I think I get a solution (sorry for the delay!). Try:
    Code:
    SELECT a.Emplnum, 
           a.EmplName, 
           b.Skill, 
           b.Call_Center, 
           a.MaxOfCalls AS Calls, 
           a.MaxOfattempts AS attempts, 
           a.MaxOfpromo AS promo, 
           a.MaxOfstat AS stat
    FROM (
        SELECT TblDuplicates_Temp.Emplnum, 
               TblDuplicates_Temp.EmplName, 
               Max(TblDuplicates_Temp.Calls) AS MaxOfCalls, 
               Max(TblDuplicates_Temp.attempts) AS MaxOfattempts, 
               Max(TblDuplicates_Temp.promo) AS MaxOfpromo, 
               Max(TblDuplicates_Temp.stat) AS MaxOfstat
        FROM TblDuplicates_Temp
        GROUP BY TblDuplicates_Temp.Emplnum, 
                 TblDuplicates_Temp.EmplName
          ) AS a 
    INNER JOIN (
        SELECT TblDuplicates_Temp.Emplnum, 
               TblDuplicates_Temp.Skill, 
               TblDuplicates_Temp.Call_Center, 
               Max(TblDuplicates_Temp.Calls) AS MaxOfCalls
        FROM TblDuplicates_Temp
        GROUP BY TblDuplicates_Temp.Emplnum, 
                 TblDuplicates_Temp.Skill, 
                 TblDuplicates_Temp.Call_Center
          ) AS b
    ON b.Emplnum = a.Emplnum AND
       b.MaxOfCalls = a.MaxOfCalls;
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •