[엑세스] 문제4. 처리기능 (쿼리 만들기)
일반 쿼리 만들기
▶ 거래처코드는 1 ~ 9에 해당하는 자료만 표시하시오(like 이용)
→ like [1-9]
▶ 거래일자는 월 ~ 금요일에 해당하는 자료만을 표시하시오(weekday, between)
★ weekday(날짜, 형식)
· 형식 = 1 : 일(1), 월, 화, 수 ~
· 형식 = 2 : 월(1), 화, 수 ~
· 형식 = 3 : 월(2), 화, 수 ~
→ 거래일자 필드는 그대로 두고 조건 거래일자 필드를 추가해서 따로 적는다
| Expr1: Weekday([거래일자],2) |
| Between 1 And 5 |
▶ 쿼리 실행 후 생성되는 테이블의 이름은 직급별처리결과로 설정하시오
매개 변수 값 입력 후 실행 > 디자인 보기 > 쿼리 디자인 > 테이블 만들기 > 이름: 직급별처리결과
다시 쿼리 실행 > 테이블 쿼리 만들기를 실행하여 ... [예] 클릭 > 매개 변수 값 입력 후 N행을 붙여넣습니다 ... [예] 클릭
▶ '대여횟수'는 '일련번호'의 개수이다
· 필드 : 대여횟수: count([일련번호]) · 조건 : 식
▶ '코드'는 A부터 E까지 대상으로 하시오
· 조건 : Like "[A-E]"
(24_01_4번) '수당'은 '기본수당'+'위험수당'이며 '위험수당'은 테이블에는 없는 개체이다. 위험수당은 '위험도'가 1 이상이면 1000원, 3이상이면 2000원, 5이상이면 5000원이다. Switch
(필드) 수당: [기본수당]+Switch([위험도]>=5,"5000",[위험도]>=3,"2000",[위험도]>=1,"1000")
(24_01_4번) '점수'는 아래와 같은 매개변수 메시지를 통해 입력된 값의 범주에 해당하는 자료만이 추출되도록 하되, 2000 이상 3000미만의 값을 입력해 쿼리결과를 출력하고 쿼리결과를 '점수대입력' 테이블로 전달하시오
| 매개 변수 값 입력 점수이상 [ 2000 ] 확인 취소 |
매개 변수 값 입력 점수미만 [3000] 확인 취소 |
(조건) >=[점수이상] And <[점수미만]
업데이트 쿼리
(예제) 제품관리 테이블에서 '인상후가격'에 대해 업데이트하는 가격갱신 쿼리를 작성하시오. 인상후가격은 소비자가에 인상율만큼 인상한 금액이다. 제조사와 인상율을 입력받도록 지정하며, 제조사는 입력한 단어가 포함된 레코드를 모두 포함하시오. 인상율은 0.2, 제조사로 '전기'를 입력받아 실제적으로 제품관리 테이블에 반영되도록 하시오
→ 업데이트할 항목은 인상후가격이다. 그런데 조건에 매개변수를 적어서는 안되며, 업데이트에 적절히 섞어 넣는다
→ 매개변수를 알려주는 문항이다. 제조사에 대한 조건은 가시적으로 업데이트하는 것이 아닌, 조건이므로 조건란에 적는다
→ 업데이트 쿼리 제작 후 작동시켜 직접 매개변수에 적어야 한다.
| 필드 | 인상후가격 | 제조사 |
| 업데이트 | [소비자가]*(1+[인상율을 입력하시오]) | |
| 조건 | Like "*" & [제조사를 입력하시오] & "*" |
(23_02_4번) 회원, 주문 테이블을 이용해 최근 주문이 없는 고객에 대해 회원 테이블의 비고 필드의 값을 ★ 관리대상회원으로 변경하는 관리대상회원처리 업데이트 쿼리를 작성한 후 실행하시오. 최근 주문이 없는 고객이란 주문일자가 2023년 4월 10일부터 30일까지 중에 회원테이블에는 고객ID가 있으나 주문테이블에는 고객ID가 없는 고객임. Between, Not In, 하위쿼리 사용
혹시 불일치쿼리? 틀렸다. 어쨌든 업데이트 쿼리이다
| 필드 | 비고 | 고객ID |
| 테이블 | 회원 | 회원 |
| 업데이트 | "★ 관리대상회원" | |
| 조건 | Not in (select 고객ID from 주문 where 주문일자 between #2023-04-10# and #2023-04-30#) |
(24_02_4번) 등록자관리의 비고를 업데이트하는 비고_갱신 쿼리를 작성하여 업데이트하시오. ① 주민등록번호의 8번째 글자가 1또는 3이라면 남, 2또는 4라면 여가 비고에 입력되도록 하시오(CHOOSE, MID) ② 등록일자에 대해 2023년에 해당하는 자료들만 업데이트 대상이 되도록 하며, 조건에 사용되는 2023은 아래 그림처럼 매개변수로 입력받도록 설계하시오.
| 매개 변수 값 입력 갱신 대상 년도를 입력하시오 [ 2023 ] 확인 취소 |
필드 |
비고 | Year([등록일자]) | |
테이블 |
등록자관리 | 등록자관리 | 등록자관리 |
| 업데이트 | CHOOSE(MID([주민등록번호],8), "남", "여", "남", "여") | ||
조건 |
[갱신 대상 년도를 입력하시오] |
(24_02_4번) 소방출동, 소방연락처를 이용해 출동건수의 최댓값을 가진 자료의 비고 필드 값을 '우수본부'로 변경하는 우수본부체크 업데이트 쿼리를 작성한 후 실행하시오. (In연산자와 하위쿼리 사용)
※ 테이블 별 개체
1) 소방출동: 소방서, 소방서코드, 시작시간, 마감시간, 출동건수
2) 소방연락처: 소방서코드, 연락처, 비고
출동건수의 최댓값을 구하기 위해서는 소방출동을 참고해야 한다.
그러나 비고 필드는 소방연락처에 있다. 그러므로 두 테이블을 '소방서코드'를 기준으로 구해야 한다.
① 첫 번째 쿼리만들기 > 소방출동 > 출동건수 + 요약: 최대값
> 그럼 출동건수의 최대값을 구할 수 있으나 이제 이 출동건수의 최대값을 가진 자료(=소방서코드)를 구해야 함
그래서 같은 쿼리만들기에 > 소방서코드 추가하면
디자인보기 해제 > 출동건수의 최댓값을 가진 소방서코드 딱 한 개가 나오는게 아니라
모든 목록이 다 나오게 된다. 왜냐하면, 각 소방서코드별 최댓값이 나오기 때문이다.
이는 SQL문의 GROUP BY 때문이다. 지워준다.
SELECT Max(소방출동.출동건수) AS 출동건수의최대값, 소방출동.소방서코드
FROM 소방출동
GROUP BY 소방출동.소방서코드; 세미콜론은 무조건 지워야 한다.
SQL 문만 필요하기 때문에 쿼리는 저장하지 않는다.
② 두 번째 쿼리 만들기
IN (SELECT Max(소방출동.출동건수) FROM 소방출동)
SELECT 소방출동.소방서코드 FROM 소방출동 WHERE (((소방출동.출동건수) In (SELECT Max(소방출동.출동건수) FROM 소방출동)))
SQL 문만 필요하기 때문에 쿼리는 저장하지 않는다.
③ 세 번째 쿼리 만들기
드디어 소방연락처의 비고란에 "우수본부"를 업데이트 해주는 업데이트 쿼리를 만들거다!
불일치검색쿼리처럼 보이지만 SQL문을 넣어야 하는 조회쿼리
※ IsNull을 사용해야 불일치검색쿼리(마법사)이다
(24_03) 도서관세부정보의 '도서코드'에는 기재되어있지만 도서소장처의 '소장처코드'에는 기재되지 않은 항목들을 출력하시오
(단, NOT IN 및 하위쿼리 이용)
필드명: 도서코드
조건: Not In (Select 소장처코드 from 도서소장처)
▶ 전국세차장에는 기재되어 있지만 회원가입세차장에는 기재되지 않은 '세차장명'의 항목들을 조회하는 쿼리를 작성하시오. (단, NOT IN 및 하위쿼리 이용)
| 필드명 | 세차장명 |
| 조건 | Not In ( Select 세차장명 from 회원가입세차장 ) |
(24_04_문제1) 야간진료센터추가 테이블의 내용을 야간진료센터 테이블에 추가하시오
야간진료센터 테이블에 존재하지 않은 레코드만 추가되도록 하시오(Not In)
센터명을 이용해 중복여부를 판단하는 추가 쿼리 '센터추가질의'를 작성해 실행하시오. 기타사항은 비고에 추가하시오
| 필드명 | 센터명 |
| 조건 | Not In (select 센터명 from 야간진료센터) |
크로스탭 쿼리
(24_01_4번) 신청기간은 시작일자의 2개월 이전부터 10일 이전까지의 기간이다.
신청기간: DateAdd("m",-2,[시작날짜]) & " ~ " & DateAdd("d",-10,[시작날짜])
(24_01_4번) '휴직관리', '휴직자주택관리'라는 두 테이블을 이용해 만드는 크로스탭 쿼리에서 '휴직코드'라는 필드를 사용한다고 해보자. 그런데 두 테이블에 모두 있는 쿼리라 디자인보기 해제가 안된다. 어떻게 표시해야 하는가?
(정답) [휴직코드.휴직관리]
(24_02_4번) '장애분류'는 장애코드의 앞 2글자가 시각이거나 청각이면 신체장애, 그외는 지체장애로 나타내시오. IIF, LEFT (단, OR을 사용하지 마시오)
(조건) IIF(Left([장애코드],2)="시각", "신체장애", IIF(Left([장애코드],2)="청각", "신체장애","지체장애))
(24_02_4번) 거주구분의 조건에는 공동주택, 다세대주택이라는 글자를 포함하지 않는 자료를 대상으로 하시오(NOT LIKE, AND 이용)
(조건) Not Like "*" & "공동주택" & "*" And Not Like "*" & "다세대주택" & "*"
(24_03) 판별결과는 TRUE이고 유효기간은 4-6개월에 해당하는 자료만을 대상으로 하시오
필드명: 판별결과
조건: TRUE
필드명: 유효기간
조건: Like "[4-6]개월" ← *이 없지만 '개월'이라는 문자가 포함되어 있으므로 Like를 쓴다
(24_03) CCTV분류, CCTV검사결과를 이용해 '대수'의 합계를 구하는 CCTV요약 크로스탭 쿼리를 작성하시오.
'카메라 각도'가 빈 셀인 자료는 '정보없음'그 외는 해당 필드 값 뒤에 '도'가 출력되도록 하시오. ISNULL, IIF 이용
CCTV용도에서 화재예방은 제외하고 대수는 10 이상인 자료만을 대상으로 하시오.
| 필드 | CCTV용도 | 총 대수: 대수 | IIF(ISNULL([카메라각도]=TRUE, "정보없음", [카메라각도] & "도") | 대수 | 대수 |
| 테이블 | |||||
| 요약 | 개수 | 합계 | |||
| 크로스탭 | 행 머리글 | 행 머리글 | 열 머리글 | 값 | |
| 정렬 | >=10 | ||||
| 조건 | <> "화재예방" |
(24_03) '출동가능여부'의 '상태'필드를 업데이트하는 쿼리이다. '최대높이'+'최소높이'가 15이상인 자료를 대상으로 하시오
상태 필드의 조건란에 적는 것이 아니다
필드명: [최대높이] + [최소높이]
조건: >=15
▶ 목재인허가일자 테이블을 이용해 쿼리를 작성하시오. 허가개시일은 인허가일자의 첫값이다. 가장 최근 허가된 일자의 년도를 기준으로 5년간의 데이터를 출력하시오. MAX와 YEAR 함수를 사용하라
SQL문 복붙으로 조건을 만든다. 최근 허가일자의 년도 기준 5년이라는 뜻을 다시 해석해보면 최근 허가일자가 2025년이라면 2020년까지 5년간의 데이터를 가져온다고 볼 수 있다. 즉 2025-5라는 것이다.
조건: >=(SELECT Max(Year([인허가일자])) FROM [목재인허가일자])-5
▶ 열머리글은 2월 ~ 7월까지를 나타내시오.(month)
(답) in("2월", "3월", "4월", "5월", "6월", "7월")
▶ 열 머리글은 '개업일자'의 년도를 표시하되, '2020년 이전' 처럼 2020년 이전 자료는 하나로 취합하고, 나머지는 개업일자의 년도를 추출한 뒤 '년'을 붙이시오
(답) Expr1: IIf(Year([개업일자])<=2020,"2020년 이전",Year([개업일자]) & "년")
▶ 다음과 같은 크로스탭 쿼리를 만들었다고 가정해보자. 해당 쿼리의 값 영역에는 총점(시설점수+위치점수+운영점수+기타점수)의 합계가 출력되었으며 행 머리글 영역에도 총점의 합계가 쓰였다고 한다. 즉 두 총점의 합계 모두 요약: 합계이나 크로스탭 부분이 다르다는 것이다. 차이가 무엇인가?
값 영역의 총점 합계는 2020년 이전, 2021년, 2022년 별 셀프세차/손세차/자동세차 별 총점이다.
그러나 행머리글 영역의 총점 합계는 세체 유형 옆 B열의 '합계총점'이다. 예를 들면 행머리글 영역의 합계총점 중 154의 경우 셀프세차 영역의 2020년 이전+21년+22년 모두를 더한 것이다. 이러한 경우 보통 크로스탭 쿼리 마법사로 자동생성되지만, 그렇지 못할 때는 본인이 직접 만들어야 한다.
추가 쿼리
업데이트 쿼리와의 차이점: 업데이트 쿼리의 경우 필요한 필드만 추가해서 쿼리를 만들면 된다.
반면 추가쿼리는 모든 쿼리를 추가하되 조건 등을 변경해야 한다.
(24_01_4번) 구매추가 테이블의 내용을 구매 테이블에 추가하시오. 쿼리명은 '60점이상추가'이다.
▶ 구매수량이 60 이상인 자료들을 구매 테이블에 추가하시오. 구매가격은 구매금액 필드에 추가할 것
테이블 만들기 쿼리
※ 매개변수로 '은'을 입력하여 쿼리결과를 출력하고 해당 결과를 '구매자료결과'테이블로 전달하시오
※ 이벤트 프로시저 실전문제
▶ 재학생관리 폼의 cmb조회 버튼을 클릭할 때 다음 기능을 수행하도록 구현하시오. Txt조회 컨트롤에 입력된 글자를 포함하는 데이터만 표시할 것 (ApplyFilter, Like 이용)
→ Txt조회 컨트롤에는 학번의 일부를 적을 수 있다는 것을 유추해야 한다.
(오답) DoCmd.ApplyFilter , 학번 = txt조회
(정답) DoCmd.ApplyFilter ,"학번 like '*" & Txt조회 & "*'"
▶ 고객관리 폼의 폼머리글 클릭 시 아래와 같이 메시지 상자를 출력하고 [고객명을 조회하시겠습니까? 예/아니오] 예를 누르면 'Txt조회'를 공백으로 처리하고, 포커스가 'Txt조회'에 배치되도록 설계하시오.
Private Sub 폼_머리글_Click()
v = MsgBox("고객명을 조회하시겠습니까?", vbYesNo)
If v = vbYes Then
Txt조회 = ""
DoCmd.GoToControl = "txt조회"
End If
End Sub