How to create a Case

[txwylde]

I have a table that brings in an ID, Date of Birth, and Indicator field, and a service group either 1 or 3. There are duplicates in these tables, so there is no primary key. How do I create a case so if I have two records which are identical other than the service group that if its a service group "1" it purges that one and only keeps the "3"? Thanks!
Bill

 

[PHV]

Take a look here:

http://www.tek-tips.com/viewthread.cfm?qid=905086

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[txwylde]

Here is the SQL for the table that creates the duplicate data:
SELECT [Make Table - ALL LTC Groups 1 and 3].[Client/Medicaid Nbr], [Make Table - ALL LTC Groups 1 and 3].[Client Birth Dt], [Make Table - ALL LTC Groups 1 and 3].[Medicare Ind], [Make Table - ALL LTC Groups 1 and 3].[Eligibility County Cd], [Make Table - ALL LTC Groups 1 and 3].[Srvc Auth Service Grp]
FROM [Make Table - ALL LTC Groups 1 and 3];

Here is my Delete code:
DELETE FROM [Make Table - ALL LTC Groups 1 and 3] WHERE
[Make Table - ALL LTC Groups 1 and 3].[Client/Medicaid Nbr] Count(*) >1 and [Make Table - ALL LTC Groups 1 and 3].[Srvc Auth Service Grp] ='1';

Should I make a new table with this delete sql?
Thanks
Bill

 

[dRahme]

Hi, generally it is a good idea not to perform deletes on your original table because you never know if you will end up needing the information you just deleted.

I would do am make table query, filtering out the '1's and turn that into your main table. I would assign a primary key to that table for future use and proceed on, but keep your original table.

One other word of advice learned the hard way - test your delete query on a backup of the table you will be working with first to ensure you do not get some unexpected results.