Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Thumbs down Unanswered: Compare Records and Move them

    Stuck here, I have a text file that I import into Access, then go through and remove duplicates and then copy those that have a status of "CAN", "COMP" or "STOUT", the field titled reqnumber gets moved to another table. The problem I have is that now they want to compare the reqnumbers, if they say "CAN" three times move a req number to another table, if they say "COMP" or "Stout", move a req number to another table. I have added a sample display.

    id reqnumber patname ctrlnum status
    58181 0039670B lastname FirstName 1008:EAW:C00001R COMP
    58182 0039671B lastname FirstName 1008:EAW:C00002R COMP
    58183 0039672B lastname FirstName 1008:EAW:C00003S COMP
    58184 0039673B lastname FirstName 1008:EAW:C00004S COMP
    58185 0039674B lastname FirstName 1008:EAW:C00005S CAN
    58186 0039674B lastname FirstName 1008:EAW:C00005S CAN
    58187 0039674B lastname FirstName 1008:EAW:C00005S CAN
    58188 0039695B lastname FirstName 1008:EAW:C00006S Req
    58189 0039695B lastname FirstName 1008:EAW:C00006S COMP
    58190 0039697B lastname FirstName 1008:EAW:C00007R COMP
    58191 0039701B lastname FirstName 1008:EAW:C00008R COMP
    58192 0039704B lastname FirstName 1008:EAW:C00009R COMP
    58193 0039705B lastname FirstName 1008:EAW:C00010R COMP
    58194 0039706B lastname FirstName 1008:EAW:C00011R COMP
    58195 0039707B lastname FirstName 1008:EAW:C00012R COMP
    58196 0039708B lastname FirstName 1008:EAW:C00013R COMP
    58197 0039708B lastname FirstName 1008:EAW:C00013R COMP
    58198 0039709B lastname FirstName 1008:EAW:C00014R COMP
    58199 0039711B lastname FirstName 1008:EAW:C00015R COMP
    58200 0039712B lastname FirstName 1008:EAW:C00016R STOUT
    58201 0039714B lastname FirstName 1008:EAW:C00017R COMP
    58202 0039714B lastname FirstName 1008:EAW:C00017R COMP

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    OK this bit of SQL will pull the req number of the patients(?) who have CAN(Cancelled?) 3 or more times

    Code:
    SELECT reqnum 
    FROM
     (SELECT reqnum, COUNT(*) as numCOMP
       FROM tblPatients
       WHERE Status = "CAN"
        GROUP BY ReqNum)
    WHERE numCOMP >=3;
    it should then be a case of just moving those patients to your new table

    HTH
    Dave

Posting Permissions

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