Matching Records with user definable criteria.

[Anne Lynn]

I have a query where I defined three fields must match - Physician, Date
of Service and Claim#. No problem. Now I need to add parameters that
allow a user to define a 4th field= Procedure Codes. After typing in
2-5 different procedure codes will only want to see records where those
procedure codes occur on the same day, same doctor and same claim. Any
help would be greatly appreciated. Thank You.

*** Sent via Developersdex http://www.developersdex.com ***

[Beacher]

I'm not quite sure I fully understand your problem.. but what are you
doing this for, what platforms? and are you only going to be using SQL ?

[Anne Lynn]

It is in Access 2003, thus far I utilized SQL

*** Sent via Developersdex http://www.developersdex.com ***

[Beacher]

What is the problem with continuing on with what you were doing and
matching up the new param's using SQL ? is it because it's 2-5 of them
and varying?

[Anne Lynn]

Each time I add my parameters to the SQL it appears to override the
matching portion. If a user types in procedure codes 1,2,3, these codes
will appear, but not for the same day, physician and claim#.

*** Sent via Developersdex http://www.developersdex.com ***

[Beacher]

Lets see the SQL statement

[Anne Lynn]

SELECT [Medical Detail].[Claim Number], [Medical Detail].[First Service
Date], [Medical Detail].[Servicing Provider Name], [Medical
Detail].[Servicing Provider Number], [Medical Detail].[Procedure Code],
[Medical Detail].[IW First Name], [Medical Detail].[IW Last Name],
[Medical Detail].[IW SSN], [Medical Detail].[Procedure Code
Description], [Medical Detail].[Invoice Number], [Medical Detail].[BWC
Authorized To Be Paid Amount]
FROM [Medical Detail]
WHERE ((([Medical Detail].[Claim Number]) In (SELECT [Claim Number] FROM
[Medical Detail] As Tmp GROUP BY [Claim Number],[Servicing Provider
Number],[First Service Date] HAVING Count(*)>1 And [Servicing Provider
Number] = [Medical Detail].[Servicing Provider Number] And [First
Service Date] = [Medical Detail].[First Service Date])) AND (([Enter
Code:])=[Procedure Code])) OR ((([Medical Detail].[Claim Number]) In
(SELECT [Claim Number] FROM [Medical Detail] As Tmp GROUP BY [Claim
Number],[Servicing Provider Number],[First Service Date] HAVING
Count(*)>1 And [Servicing Provider Number] = [Medical
Detail].[Servicing Provider Number] And [First Service Date] = [Medical
Detail].[First Service Date])) AND (([Enter Code 2:])=[Procedure Code]))
ORDER BY [Medical Detail].[First Service Date], [Medical Detail].[IW
SSN];


*** Sent via Developersdex http://www.developersdex.com ***