Three queries into one

[Mo]

I'm trying to combime the following three queries into one sql statement
but I don't seem to be able to get the syntax quite right. Can someone
please help out?

TIA,

Mo

Query1

SELECT KASE, DGN, CSNM, KARY, KCD, SD, SM, SY, TSU, MSN, HCD, POST
FROM tblscreening
WHERE DGN=1
AND IsNull(CSNM)
OR IsNull(KARY)
OR IsNull(KCD) Or (KCD=0 Or KCD=50 Or KCD=70)
OR IsNull(SD)
OR IsNull(SM)
OR IsNull(SY)
OR IsNull(MSN)
OR IsNull(HCD)
OR IsNull(POST)

Query2

SELECT KASE, DGN, CSNM, KARY, KCD, SD, SM, SY, TSU, MSN, HCD, POST
FROM tblscreening
WHERE DGN=2
AND IsNull(CSNM)
OR IsNull(KARY)
OR IsNull(KCD) Or (KCD=0 Or KCD=50 Or KCD=70)
OR IsNull(SD)
OR IsNull(SM)
OR IsNull(SY)
OR IsNull(MSN)
OR IsNull(HCD)
OR IsNull(POST)

Query3

SELECT KASE, DGN, CSNM, KARY, KCD, SD, SM, SY, TSU, MSN, HCD, POST
FROM tblscreening
WHERE isNull(CSNM)
AND IsNull(KARY)
OR IsNull(KCD) Or (KCD=0 Or KCD=50 Or KCD=70)
OR IsNull(SD)
OR IsNull(SM)
OR IsNull(SY)
OR IsNull(MSN)
OR IsNull(HCD)
OR IsNull(POST

 

[Guest]

Hi.

Use a union

Make a search here for union queries.

Marco

 

[John Spencer]

Combine the criteria and use parentheses to isolate each test.

SELECT KASE, DGN, CSNM, KARY, KCD, SD, SM, SY, TSU, MSN, HCD, POST
FROM tblscreening
WHERE( DGN=1
AND IsNull(CSNM))

OR (DGN=2
AND IsNull(CSNM))

OR (isNull(CSNM)
AND IsNull(KARY))

OR IsNull(KARY)
OR IsNull(KCD)
Or (KCD=0 Or KCD=50 Or KCD=70)
OR IsNull(SD)
OR IsNull(SM)
OR IsNull(SY)
OR IsNull(MSN)
OR IsNull(HCD)
OR IsNull(POST)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mo]

Combine the criteria and use parentheses to isolate each test.

SELECT KASE, DGN, CSNM, KARY, KCD, SD, SM, SY, TSU, MSN, HCD, POST
FROM tblscreening
WHERE( DGN=1
AND IsNull(CSNM))

OR (DGN=2
AND IsNull(CSNM))

OR (isNull(CSNM)
AND IsNull(KARY))

OR IsNull(KARY)
OR IsNull(KCD)
Or (KCD=0 Or KCD=50 Or KCD=70)
OR IsNull(SD)
OR IsNull(SM)
OR IsNull(SY)
OR IsNull(MSN)
OR IsNull(HCD)
OR IsNull(POST)

Thanks very much John. I think this is exactly what I need.
Incidentally, if I added a date criteria to the sql statement, would I
need to it to each of the three 'OR' clauses?

 

[John Spencer]

If the date had to be met for any record to be returned, you could add it
only once. All you need to do is get the parentheses correctly placed.

WHERE SomeDateField = #1/1/2007#
AND (
(DGN=1
AND IsNull(CSNM))

OR (DGN=2
AND IsNull(CSNM))

OR (isNull(CSNM)
AND IsNull(KARY))

OR IsNull(KARY)
OR IsNull(KCD)
Or (KCD=0 Or KCD=50 Or KCD=70)
OR IsNull(SD)
OR IsNull(SM)
OR IsNull(SY)
OR IsNull(MSN)
OR IsNull(HCD)
OR IsNull(POST)
)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mo]

If the date had to be met for any record to be returned, you could add it
only once. All you need to do is get the parentheses correctly placed.

Thanks again - you've been really helpful. I've been struggling with
this statement for what seems like days now!