Query Question

[JD]

Here's the scenario.. can anyone help?!

I have a spreadsheet that I need to get rid of some fields on, using
certain criteria, but I'm not sure how to do the whole thing.

My issue:
I need to get rid of all records that contain a certain diagnosis code
(2222) that could be in 16 different fields (I chose not to list an
example with 16 fields). Once those fields are identified, there is a
further step needed. In each record, there is a field named, "Claim
ID", and for each claim ID there is a SubmitterID and an individual's
LastName associated with it. And there may be multiple claim ID/last
name records.. but not all claim ID/last name records will have that
certain diagnosis code in it. So, if I find that diagnosis code for
one claim ID and last name, but there are a total of four records that
have that same claim ID and last name combo.. I want to filter out all
four, how do I accomplish that?? Sorry if the example is a bit
choppy.. it's tough to explain without seeing the table. In the
example below, I want to get rid of ID #'s - 01, 02, 03, 04, 08, 09.

ID SubmitterID LastName FirstName DateOfBirth ClaimID DiagCode
01 999999 Smith John 021280 11111 2222
02 999999 Smith John 021280 11111
03 999999 Smith John 021280 11111
04 999999 Smith John 021280 11111
05 444444 Jones Indiana 052768 55555 7777
06 333333 Brick Joe 093070 88888
07 333333 Brick Joe 093070 88888
08 666666 Lane Cathy 012355 44444 2222
09 666666 Lane Cathy 012355 44444

If anyone could help, that would be great!!!!!
Or if anyone needs me to clarify something, please let me know.

Thanks again!!!!!!!!!!

 

[John Spencer (MVP)]

You'll need a subquery to identify which records you want.


SELECT Y.*
FROM YourTable as Y
WHERE Y.ClaimID IN
(SELECT T.ClaimID
FROM YourTable as T
WHERE T.DiagCode = 2222)

With multiple DiagCode fields

SELECT Y.*
FROM YourTable as Y
WHERE Y.ClaimID IN
(SELECT T.ClaimID
FROM YourTable as T
WHERE T.DiagCode1 = 2222 or
T.DiagCode2 = 2222 or
T.DiagCode3 = 2222)

Or Easier to write

SELECT Y.*
FROM YourTable as Y
WHERE Y.ClaimID IN
(SELECT T.ClaimID
FROM YourTable as T
WHERE 2222 in (T.DiagCode1,T.DiagCode2, ...))

In Access, you should be able change the SELECT to DELETE if you want to
actually delete records. I'm not sure this will work with Excel.

 

[Jamie Collins]

SELECT Y.*
FROM YourTable as Y
WHERE Y.ClaimID IN
(SELECT T.ClaimID
FROM YourTable as T
WHERE T.DiagCode = 2222)

Interesting. I'd write that as:

SELECT Y.*
FROM YourTable as Y
WHERE EXISTS
(SELECT T.ClaimID
FROM YourTable as T
WHERE T.DiagCode = 2222
AND Y.ClaimID = T.ClaimID)

Because this is more intuitive to me, I'd assume it was more efficient

In Access, you should be able change the SELECT to DELETE if you want to
actually delete records. I'm not sure this will work with Excel.

DELETE syntax is not supported for Excel.

Jamie.

--

 

[Jamie Collins]

PS John: re efficiency. I can get JETSHOWPLAN to work for my Jet 4.0
installation. It works for Jet 3.5 but at the top of showplan.out it
states:

NOTE: Currently does not handle subqueries, vt parameters, and
subqueries
NOTE: You may see ERROR messages in these cases

It *so* does not handle subqueries that it feels the need to state it
twice :-(

Jamie.

--

 

[Jamie Collins]

....typo! Should be:

I can't get JETSHOWPLAN to work for my Jet 4.0 installation

Jamie.

--