I have difficulty with a query

[keawee]

Hello,

In the Table DataBase1, i have the field "CallNumber". In this field, i can find most recording with the same "CallNumber". I must to keep just one record. In the field Activities, i have "REPARATION" or "Primary Assign" or other but for me, i look only "REPARATION" or "Primary Assign".

If i have most recording with the same "callNumber", the priority is "REPARATION" but if i don't have "REPARATION" , i keep "Primary Asssign" and i delete the other recording. I want just keep one record for one "call Number". I have a table with the table result what i want, his name is DataBase2

It's very difficult for me, could you look the base and could you help me.

Keawee

 

[Jon K]

I have added a NewTable to your database. In the table, the CallNumber field is indexed as Yes (No Duplicates).

You can open the form and click on the command button to append the required records to the NewTable. The code is in the On Click event of the command button:-

Private Sub cmdAppend_Click()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry3REPARATION"
    DoCmd.OpenQuery "qry4PrimaryAssign"
    DoCmd.SetWarnings True
    
    MsgBox "Records appended to NewTable"
    
End Sub

The code runs the query "qry3REPARATION" and then the query "qry4PrimaryAssign".

qry3REPARATION is built from table "DataBase1" and query "qry1REPARATION"
qry4PrimaryAssign is built from table "DataBase1" and query "qry2PrimaryAssign"

qry1REPARATION and qry2PrimaryAssign are two Totals Queries, in which I used the aggregate function Min() to extract the first Date End for each CallNumber in REPARATION and Primary Assign.

.

 

[keawee]

Thanks you jon K for your help,

Keawee