Removing recs with duplicates from one field base on data in another 1

[Jeanie]

I have a very large table in ACCESS. One field is Claim number, another field is revenue code. Each revenue code has it's own line (or record) so the Claim number is repeated as many times as there are revenue codes for that claim. The revenue codes can be repeated as many times as needed, but only once per claim. For example:

Claim # Rev Code
11111 21
11111 32
11111 55
22222 21
33333 32
33333 55

I want to find all claim #s that have one specific revenue code and remove all records for that claim number. So, in the example above, if I want to find all claims with revenue code 21 and remove that claim completely the following would be left:

Claim # Rev Code
33333 32
33333 55

I can do a query finding the specific revenue code, but then I don't know the next step (how to remove any record with a claim number that matches any claim number found in the first query).

As I'm sure you can tell, I have a pretty limited knowledge of ACCESS, so any help you can give would be appreciated.

Thx,

Jeanie

 

[RickSpr]

You need a subquery to do this:
DELETE * FROM MyTable
WHERE ClaimNumber IN
(SELECT ClaimNumber FROM MyTable AS T
WHERE T.RevCode = '21')

The subquery returns a list of claim numbers for the selected revenue code. That list is then used to filter all the rows in the main table.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Jeanie]

Sorry...I'm confused. I went into my query where I had it find all lines with "21", and added a subquery (I think I did this right, however, I have never done it before). It did not find any claims. So, I tried just creating a query with no other criteria other than the subquery you provided, it still pull up no claims. I know I am just missing something - with my limited knowledge!! Any clarification would help.

Thx,

Jeanie

 

[RickSpr]

What is the SQL statement of the last query you tried? Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BrockLanders]

Jeanie, just an idea. Please do not take the wrong way. With your being so new to Access, do you know how to view SQL in queries. If not, when you have your query open in Design view, at the top menu bar click View and then SQL View. Sorry if you already knew this.

 

[Jeanie]

The SQL statement is:

SELECT MyFile.ID, MyFile.ClaimID, MyFile.PCPClmID, MyFile.PCPID, MyFile.RevCodeUB, MyFile.McaidProgType, MyFile.Allowed, MyFile.Submitted, MyFile.NetPay, MyFile.ServiceDate, MyFile.PaidDate
FROM MyFile
WHERE (((MyFile.RevCodeUB)=21 Or (MyFile.RevCodeUB)=22));

It has identified all the records with 21 or 22, but now I don't know how to filter out any other records that have a ClaimID that matches one identifed with the statement. Do I add the statement you gave me to this query somewhere, or do I make a new query???

(BrockLanders - Don't worry about maybe "talking down to me"!! That was part of my problem...I had been there before, but couldn't remember how to get back. The little bit I know about ACCESS I have taught myself - with help from Tek-Tips. And, I know nothing about working in any other data-base).

Thx, Again!

Jeanie

 

[RickSpr]

Ok. We had some miscommunication. I thought you said it didn't pull up any claims, but now it seems it does. Your problem must then be in how you attempted to create the query I first gave you.

Here is that query, modified with the table and field names you've just given me:

DELETE * FROM MyFile
WHERE ClaimID IN 
     (SELECT ClaimNumber FROM MyFile AS T
      WHERE T.RevCodeUB = 21 OR T.RevCodeUB = 22)

Create a new query, canceling the Add Table dialog. From the menu, choose View|SQL View. Replace what is there with the above query. Then choose View|Design View. This will show you how to specify this kind of subquery in design view. (Frankly, though, it's probably easier to specify it in SQL view in the first place.)

I hope this makes sense to you, but if not it's because I've been sick today. Not thinking as clearly as usual.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Jeanie]

Sorry for making this all so confusing! I copied your formula. When I click on it to run it asks for a ClaimID number - do I need to put something in there? I tried just skipping past that and it may be working...but it is very slow (and, unfortunatelly our system is going down in 5 minutes so I had to get out).

I am going to be away from work until Thursday...so, just because it takes me a while to respond doesn't mean I'm not still interested in...and thankful for...your help!

Jeanie

 

[RickSpr]

My mistake. Change "ClaimNumber" to "ClaimID" in the query.

Jet couldn't figure out what ClaimNumber was (since it didn't exist), so it assumed it was a parameter. That's why it popped up an input dialog.

The fact that you didn't realize what was wrong suggests that you aren't experienced with SQL. Please take the time to understand how this subquery works. If you don't learn from it, I'm just writing your code for you. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Jeanie]

Thanks for your help...that worked. And, you are right, I am not experienced with SQL. I always do try to learn from the help I get from anyone.