Three queries into one

  • Thread starter Thread starter Mo
  • Start date Start date
M

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
 
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
..
 
John said:
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)
Click to expand...


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?
 
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
..
 
John said:
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.
Click to expand...
Thanks again - you've been really helpful. I've been struggling with
this statement for what seems like days now!
 
Back
Top