Using WHERE clause for multiple criteria from more than 1 table

[melost]

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

 

[lespaul]

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

This doesn't work:

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

 

[melost]

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