Any SQL and/or MS Access wizard out there?

Discussion in 'Computers' started by Mike D., Apr 12, 2005.

  1. Mike D.

    Mike D. Stunt Coordinator

    Joined:
    Apr 8, 1999
    Messages:
    97
    Likes Received:
    0
    Trophy Points:
    0
    I have a little task that I need to accomplish, but I'm not sure how to go about it. I have the following database structure (this is just a small part of the data, just dummy stuff since the real stuff has health care provider info in it):

    ProviderNumber ProviderName

    900404 Windows Test 1
    900405 Windows Test 1
    900406 Windows Test 1
    900683 Windows Test 45
    900684 Windows Test 45
    901616 Windows Test 4595bg
    901528 nother record 55
    902407 nother record 55
    902219 Windows Test 1a
    902764 Windows Test 1b
    902843 Windows Test 1b
    902844 Windows Test 1b

    What I need to do is determine the maximum Provider #, and then delete the OTHER records for each Provider Name. So essentially, there will be 1 record for each provider. Don't ask how the data got in it's current state, I have no idea! :) I am working on some SQL but I can't quite get there...

    Thanks in advance for any advice anyone has!
     
  2. SethH

    SethH Cinematographer

    Joined:
    Dec 17, 2003
    Messages:
    2,867
    Likes Received:
    0
    Trophy Points:
    0
    Ok, I think this will work. I can't remember the Not Equals notation for Access, so you'll have to fill that in (also, I assume there are few enough providers that you can use an input field to do each individually):

    DELETE ProviderNumber, ProviderName
    FROM **Table**
    WHERE ProviderName = **Input** AND ProviderNumber NOT EQUAL TO
    (SELECT Max(ProviderNumber)
    FROM **Table**
    WHERE ProviderName = **Input**)

    Note that you'll have to fill in the things surrounded by asterisks.

    EDIT: I think I've made a mistake in there, but I can't see it right now. I'm too tired. Hopefully someone else will drop by and fix it up or give you something better. Otherwise I'll look at it again tomorrow.
     
  3. Mike D.

    Mike D. Stunt Coordinator

    Joined:
    Apr 8, 1999
    Messages:
    97
    Likes Received:
    0
    Trophy Points:
    0
    Hey, just wanted to thank you for the response.. I didn't get a chance to look at it today, I'll give it a shot tomorrow... Hometheaterforum is blocked at my new job! Oh well, it's a great job otherwise. First time I even noticed the filter, but I'm not the surf-at-work type... :)
     
  4. Mike D.

    Mike D. Stunt Coordinator

    Joined:
    Apr 8, 1999
    Messages:
    97
    Likes Received:
    0
    Trophy Points:
    0
    Okay, just in case anyone is interested, I have a better understanding of what I need to do. I am trying to write a little VB.Net app to do it, reading in each line and the like, since it is already sorted, but I do think it should be doable in an Access module easier...

    SystemNumberSystemNumberMAXSystemNameSystemKey
    9007404 Windows Test 1 A
    9007405 Windows Test 1 A
    9007406 Windows Test 1 A
    9008683 Windows Test 45 A
    9008684 Windows Test 45 A
    9017616 Windows Test 4595b A
    9019528 Windows Test 1 B

    The SystemNumberMAX field is to be populated with the maximum SystemNumber for a group, where a group is a unique System Key and System Name combo. The SystemNumberMAX is only to be filled when there is more than 1 System Number
    for a group. Thus, when the module is complete, the above record set should look like this:

    SystemNumber SystemNumberMAXSystemNameSystemKey
    9007404 9007406Windows Test 1 A
    9008683 9008684 Windows Test 45 A
    9017616 Windows Test 4595bg A
    9019528 Windows Test 1 B
     
  5. Jim J

    Jim J Second Unit

    Joined:
    Mar 28, 1999
    Messages:
    290
    Likes Received:
    0
    Trophy Points:
    0
    hey, don't forget to check other datafields for uniqueness. don't just go by the name. There might be (Probably be) more than 1 dr. John Smiths.

    I'd also guess there is some other related data. If you just delete records, you may lose some of that related details (depending on how ref int was set up), or at a minimum lose proper refences to that other data

    good luck
     

Share This Page