|
※ 아래 Daily Tip은 최천군님이
제공해주신 내용입니다.
(오늘의 학습자료 다운받기)
오늘은 QBF를 통한 다중검색 방법과 Null값의 처리 방법에 대해서
소개하겠습니다. 오늘 이후로 검색의 압박에서 벗어나 당당히 원하는 정보를 찾으십시요.
먼저 쿼리에서 조건절의 처리가 어떻게 이루어지는지에 대한 이해가 선행되어야 할 것입니다. 아무도 알려 주지
않았던 비법을 전해 드리겠습니다.
쿼리에서 조건절을 작성하여 원하는 정보를 찾으실 때는 다음의 두가지만 기억하십시요. 북치기.. 박치기..
<북치기>
익히 보시던 쿼리 디자인 보기입니다. 아래 디자인 눈금에서 빨간 상자로 표시된 부분이 쿼리의 조건을
정의하는 부분이며 SQL구문에서 WHERE 이하와 동일합니다.
쿼리를 실행시키면 액세스는 어떻게 조건에 일치하지 않는 것을 걸러내게
될까요? 논리연산을 하여 빨간상자의 값이 False인 것을 누락 시키고 True인 레코드만을 선택하게
됩니다.
<박치기>
조건절이 여러 개 일때는 어떻게 할까요?
디자인 눈금의 가로 방향으로 논리곱, 세로방향으로 논리합 연산을
합니다.
즉, 가로 방향은 And(하나라도 False면 결과는 False)로 결합되고 세로방향은 Or(하나라도
True면 결과는 True)로 결합됩니다.
이렇게 여러 개의 논리합, 논리곱의 전체 연산결과를 판단하여 북치기.. 에서 설명드린 것처럼 조건절의
최종결과가 True인 것만 선택하게 되는 것입니다. 즉, SQL 구문으로 보자면 SELECT *
FROM Orders WHERE TURE 인 값만 선택되는 것이지요. 실제로 위의 SQL 구문을
SQL보기에서 입력하면 모든 레코드의 조건이 모두 True가 되어 모든 레코드가 선택됩니다.
이 두가지의 개념을 가지고 있으면 조건절을 작성하기가 수월해 집니다.
<북치기 박치기 북북박박>
좀 더 상세히 설명해 보겠습니다.
위와 같이 디자인 그리드에서 표현된 것은 SQL 구문에서 WHERE 이하의 CustomerID=”LONEP”
AND EmployeeID=4와 같은 것입니다.
이러한 조건절 내의 하나의 조건 구문은 일단 비교연산을 거치게 됩니다. 일단 비교연산에 들어가면 반드시
하나의 비교대상값과 하나의 비교연산자와 하나의 비교값이 필요합니다. 당연하지만 종종 이 원칙을 까먹게
됩니다. 디자인 눈금에서 비교연산자를 생략하면 등호(=)로 비교연산하게 됩니다.
CustomerID 필드의 값이 “LONEP”인 레코드의 비교연산 결과는 “LONEP”=“LONEP”로
True가 될 것이고 나머지는 False가 될 것입니다. 마찬가지로 EmployeeID가 4인 것만
True가 되겠지요.
현재 비교되는 레코드의 두 필드값이 각각 “LONEP”와 5라면 각 조건의 비교연산 결과는 “LONEP”=”LONEP”
이므로 True와 5=4 이므로 False가 될 것입니다. 이 두가지의 값이 현재 논리곱(And)로 연결되어
있으므로 다음과 같이 논리연산이 될 것입니다.
True AND False
위의 연산결과는 당연히 False 이겠지요.
결국 WHERE 이하 전체의 논리연산 값은 False가 되고 이 레코드는 선택되지 못합니다.
하나 더 해 보겠습니다.
위와 같이 세 행의 조건이 있을 때 어떤식으로 연산이 이루어 질까요?
먼저 가로방향으로 논리곱(And) 연산을 하고 각각의 결과들을
세로방향으로 논리합(Or) 연산을 하게 됩니다.
현재 비교되는 레코드의 필드값이 각각 “UNENO” 와 5라고 가정합니다.
첫번째 행의 각 연산의 비교연산 결과는 False And False 이므로 논리곱 연산의 결과는
False입니다.
두번째 행의 연산결과는 5=5의 비교연산 결과가 True이고 조건이 하나만 있으므로 논리합 연산결과는
True입니다. 이 시점에서 레코드가 선택되고 다음 레코드로 넘어가겠지만 우리는 계속 해 보겠습니다.
세번째 행의 각 연산 결과는 False And False 이므로 연산결과는 False가 됩니다.
각각의 결과는 False Or True Or False로 결합 됩니다.
이 논리연산의 값은 True가 하나 있으므로 True가 되고 결국 WHERE 이하의 전체
논리연산 값은 True가 되어 이 레코드는 선택 됩니다.
<북박북박 츠츠츠 예~~ >
1. QBF에서의 조건검색
QBF에서의 조건값은 가변적입니다. 때문에 상당히 다양하게 응용할 수 있고 재미 있습니다.
지난번 팁에서 위의 그림과 같이 조건값에 해당하는 콤보상자의 값이 아무것도 없을 때 아무것도 검색되지
않았습니다. 왜 일까요? CustomerID 필드에 빈 값이 없었기 때문에? 아닙니다.
컨트롤의 값이 비어 있으면 이 컨트롤의 Value는 Null이
됩니다.
조건절에서는 다음과 같이 될 것입니다.
CustomerID=Null
Null 값을 연산 할 수 있는 연산자는 오직 하나 is 비교 연산자 입니다. 이외의 모든 산술연산,
비교연산, 논리연산자 등은 모두 무시 되고 계산되지 않습니다.
즉 어느 필드에서 값이 Null값인 레코드를 찾으려면 필드 Is
Null 로 조건절을 만들어 주어야 합니다. 해당 레코드의 필드값이 Null이면 True를 반환합니다. 쿼리
디자인 눈금에서는 Is Null을 입력하면 됩니다. Null이 아닌 레코드를 찾으려면 Is Not Null로
조건을 주면 됩니다. Not 연산자는 True를 False로 False를 True로 만들어 줍니다.
이제 콤보상자가 비어 있을 때는 모든 레코드를 보여주고 콤보상자에 값이 들어 있을 때는 그 값을 비교하여
해당하는 레코드만 보여 주고자 합니다.
SQL의 조건절로 먼저 생각해 보겠습니다.
일단 다음의 조건구문이 있어야 할 겁니다.
CustormerID=Forms!frmOrders!cmbCustomerID
그리고 콤보상자의 값이 비어있을 때, 즉 Null일 때는 모든 레코드를 보여 주려면? 논리합(Or)으로
결합해야 할 것입니다. 그리고 콤보상자의 값이 Null 이면 True 값이 되도록 비교연산문을 작성해 주면
앞의 조건문에 상관없이 True/False Or True가 되어 전체 논리연산값은 True가 되고 모든
레코드가 선택될 것입니다. 어떻게 하면 될까 한번 생각해 보십시요.
정답은 아래와 같습니다. 생각한 답과 일치하나요?
CustormerID=Forms!frmOrders!cmbCustomerID
Or
Forms!frmOrders!cmbCustomerID Is Null
쿼리 디자인 눈금에서는 아래와 같이 작성하면 됩니다.
2. 다중 필드 검색
CustomerID에 추가로 EmployeeID 검색하려고 한다면 어떻게 해야 할까요? 단 두가지 중 하나의
조건만 입력하면 그에 해당하는 것만 검색 되어야 하고 두 가지 모두 입력하면 두가지 모두 일치하는 레코드만
검색 되어야 합니다.
먼저 폼에서는 지난번 팁에서의 요령과 같이 CustomerID
콤보상자를 생성하고 컨트롤 원본을 지우고 이 콤보상자의 이름을 cmbCustomerID라고 만듭니다.
쿼리의 디자인 눈금에서는 아래와 같이 작성하시면 됩니다.
Forms!frmOders!cmbCustomerID 는 cmbCust로
Forms!frmOders!cmbEmployeeID 는 cmbEmpl로 약칭하겠습니다.
|
필드명 |
CustomerID |
EmployeeID |
cmbCust |
cmbEmpl |
|
조건 눈금 |
cmbCust |
cmbEmpl |
|
|
| |
|
cmbEmpl |
Is Null |
|
| |
cmbCust |
|
|
Is Null |
복잡합니다.
조건이 세개라면 어떻게 될까요? Crocii처럼 총명한 분이라면(^^) 쉽게 작성하실 수 있을 겁니다.
하지만 조건이 네개, 다섯개로 늘어갈수록 ‘이건 사람이 할 짓이 아니다.’ 라는 생각이 들게 됩니다.
그러므로 사람이 하지 말고 컴퓨터에게 시키면 됩니다.
다음과 같이 작성해 보십시요.
|
필드명 |
CustomerID |
EmployeeID |
|
조건 눈금 |
cmbCust Or cmbCust Is Null |
cmbEmpl Or cmbEmp Is Null |
| |
|
|
| |
|
|
저장하고 쿼리 디자인 보기를 닫았다가 다시 열면 첫번째 형식처럼 바뀌어 진 것을 확인할 수 있습니다. 이런
방식으로 조건문을 작성하게 된다면 조건이 10개라도 착오 없이 생성할 수 있습니다. 다만 쿼리를 저장하고
다시 열면 무서운 광경을 보게 될 겁니다. 수 많은 조건 구문들의 압박…
SQL구문을 보아도 역시나 마찬가지…
제대로 검색을 할 수 있을까 걱정 됩니다.
조건문의 압박을 벗어나기 위해 여러가지 방법들이 있습니다.
일반적으로 Like 연산자를 이용하는 방법이 많이 소개 되었고 또 많이 사용 되고 있습니다.
|
필드명 |
CustomerID |
EmployeeID |
|
조건 눈금 |
Like cmbCust & “*” |
Like cmbEmpl & “*” |
| |
|
|
| |
|
|
이와 같이 작성하면 컨트롤 값이 Null 일 때, Like “*” 이
되어서 해당 필드의 모든 레코드를 찾을 수 있게 됩니다. 하지만 Like 연산자는 가능하면 사용하지 않는
것이 좋습니다. 문자열 검색을 하기 때문에 검색속도가 떨어지고 인덱스를 사용할 수 없게 되어 더욱 느려지게
됩니다. 레코드가 많다면 검색 속도가 많이 떨어질 겁니다.
우리는 콤보상자를 사용하기 때문에 Like 연산자를 사용할 이유는 없습니다. 때문에 가능하면 등호(=)를
사용해야 합니다.
|
필드명 |
CustomerID |
EmployeeID |
|
조건 눈금 |
Nz(cmbCust,[CustomerID]) |
Nz(cmbEmpl,[EmployeeID]) |
| |
|
|
| |
|
|
Nz() 함수는 첫번째 인수가 Null 일때 그 값을 두번째 인수로 반환하는 함수입니다.
IIF(IsNull(cmbCust),[CustomerID],Null)과 같은 의미이지요.
위의 조건문은 콤보상자의 값이 Null 일때 현재 레코드의 같은 필드의 값을 비교시킵니다. 즉
CustomerID=CustomerID가 되어 모든 레코드는 True가 되겠지요.
이와 같은 방식으로 조건문을 작성하면 사람도 편하지만 컴퓨터에게도 상당히 부담을 줄여주게 됩니다.
컴퓨터에게 부담을 더 적게 주고 우리가 원하는 검색방식이 적용되는 방법이 있습니다. 이 방법은 디자인
보기에서 보다는 SQL보기에서 보기에서 작성하시는 것이 이해하기 쉽습니다.
설명에 앞서 한가지 상기시켜 드리면 비교연산에 들어가면 반드시 하나의 비교대상과 하나의 비교연산자와 하나의
비교값이 필요하다고 했습니다. 하지만 논리연산 층에서는 비교대상이나 비교연산자 같은 것은 필요없고 오직
True/False 값만 필요합니다. 즉 아래와 같이 WHERE 이하의 조건문을 작성해도 아무 오류 없이
모든 레코드가 선택됩니다.
WHERE IIF(True,True,False) Or True Or False
이것을 응용하여 SQL 보기에서 다음과 같이 SQL문을 작성합니다.
SELECT Orders.*
FROM Orders
WHERE
IIf(IsNull([forms]![frmOrders]![cmbCustomerID]),True,[CustomerID]=[forms]![frmOrders]![cmbCustomerID])
AND (IIf(IsNull([forms]![frmOrders]![cmbEmployeeID]),True,[EmployeeID]=[forms]![frmOrders]![cmbEmployeeID]) |
컨트롤의 값이 Null이면 논리연산값을 무조건 True로 주고 아니면
비교연산을 시킵니다. 두개의 조건 구문이 And 로 연결 되어 있으니까 두 조건 모두 만족해 레코드가 선택
됩니다. 때문에 모든 조건 구문을 비교해야 하지만 컨트롤이 Null일 경우 비교연산 층이 아닌 논리연산
층에서 바로 True값을 주었기 때문에 이 필드의 검색을 아예 하지 않게 되지요.
저장 하고 다시 열면 형태가 약간 바뀝니다. 디자인 눈금의 조건
부분에 <>False가 생겨 있습니다. 그냥 True로 바꿔도 됩니다. <>False 는 =True
이니까요.
이 방법을 응용하면 컨트롤의 내용에 따라 필드를 선택하여 조건을 줄 수도 있고 SELECT 절에 적용하면
컨트롤의 내용에 따라 원하는 필드의 값을 보여 줄 수도 있습니다.
아직 반은 남은 것 같은데… 이는 역시 여러분의 몫 입니다. 많이 응용하여 사용하십시요.
|
OfficeTutor 365