Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using WHERE clause for multiple criteria from more than 1 table

Status
Not open for further replies.

melost

Technical User
May 5, 2005
18
US
Hello, I'm wanting to add another field (tbl_claim_line.proc_code) into my search criterial below, but keep receiving syntax errors when I try to modify the WHERE clause. Currently, it works fine retrieving duplicates based on (tbl_dental_claim.claim_date). Just wondering what's the correct syntax to add another fieldto return records for both (tbl_dental_claim.claim_date) AND (tbl_claim_line.proc_code).

Here's what I have.

SELECT tbl_dental_claim.claim_date, tbl_dental_claim.patient_id, tbl_dental_claim.CRN, tbl_dental_claim.provider_num, tbl_dental_claim.claim_status

FROM tbl_dental_claim LEFT JOIN tbl_claim_line ON tbl_dental_claim.CRN = tbl_claim_line.proc_code

WHERE (((tbl_dental_claim.claim_date) In (SELECT [claim_date] FROM [tbl_dental_claim] As Tmp GROUP BY [claim_date],[patient_id] HAVING Count(*)>1 And [patient_id] = [tbl_dental_claim].[patient_id])) AND ((tbl_dental_claim.claim_status)<>"Duplicated"))

ORDER BY tbl_dental_claim.claim_date;


melost
 
You're saying that you can't add the WHERE criteria?

This doesn't work:
Code:
SELECT tbl_dental_claim.claim_date, tbl_dental_claim.patient_id, tbl_dental_claim.CRN, tbl_dental_claim.provider_num, tbl_dental_claim.claim_status

FROM tbl_dental_claim LEFT JOIN tbl_claim_line ON tbl_dental_claim.CRN = tbl_claim_line.proc_code

WHERE (tbl_claim_line.proc_code = [COLOR=red]some criteria[/color]) [b]AND[/b] (((tbl_dental_claim.claim_date) In (SELECT [claim_date] FROM [tbl_dental_claim] As Tmp GROUP BY [claim_date],[patient_id] HAVING Count(*)>1  And [patient_id] = [tbl_dental_claim].[patient_id])) AND ((tbl_dental_claim.claim_status)<>"Duplicated"))

ORDER BY tbl_dental_claim.claim_date;

Depending on what you are looking for the AND may need to be an OR. You'll also need to fill in the criteria for tbl_claim_line.proc_code.

HTH

leslie
 
Thanks. This portion...

(((tbl_dental_claim.claim_date) In (SELECT [claim_date] FROM [tbl_dental_claim] As Tmp GROUP BY [claim_date],[patient_id] HAVING Count(*)>1 And [patient_id] = [tbl_dental_claim].[patient_id])) AND ((tbl_dental_claim.claim_status)<>"Duplicated"))

... is returning rows that are duplicate (with status other than Duplicated) based on the tbl_dental_claim.claim_date and tbl_detnal_claim.patient_id, would I have to do something similiar for tbl_claim_line.proc_code?

I'm basically wanted to ask "Give me anything you see that shows identical claim_date AND proc_code AND patient_id with claim_status other than Duplicated (there's Pending, Voided, etc.) Sorry if I'm sounding vague. There's probably an easier way. This was someone's else DB and is using this query.

melost
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top