Any SQL and/or MS Access wizard out there? (1 Viewer)

[Mike D.]

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!

 

[SethH]

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.

 

[Mike D.]

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...

 

[Mike D.]

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

 

[Jim J]

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