크로스탭 쿼리를 사용하여 요약 데이터를 읽기 쉽게 만들기

디자인 보기를 사용하여 크로스탭 쿼리를 만들면 원하는 수 만큼의 레코드 원본(테이블 및 쿼리)을 사용할 수 있습니다. 그러나 원하는 데이터를 모두 반환하는 선택 쿼리를 먼저 만든 다음 해당 쿼리를 크로스탭 쿼리의 유일한 레코드 원본으로 사용하여 디자인을 간단하게 유지할 수 있습니다. 선택 쿼리 작성에 대한 자세한 내용은 참고 항목 섹션을 참조하세요.

디자인 보기에서 크로스탭 쿼리를 만들 때는 디자인 눈금의 요약 및 크로스탭 행을 사용하여 열 머리글로 사용할 필드의 값, 행 머리글로 사용할 필드의 값, 합계, 평균, 개수를 구하거나 다른 방법으로 계산할 필드의 값을 지정합니다.

1. 이 두 행의 설정에 따라 필드가 행 머리글, 열 머리글 또는 요약 값인지의 여부가 결정됩니다.

2. 이 설정은 필드의 값을 행 머리글로 표시합니다.

3. 이 설정은 필드의 값을 열 머리글로 표시합니다.

4. 이러한 설정은 요약 값을 생성합니다.

쿼리 만들기

1. 만들기 탭의 쿼리 그룹에서 쿼리 디자인을 클릭합니다.

   

2. 테이블 표시 대화 상자에서 레코드 원본으로 사용할 각 테이블 또는 쿼리를 두 번 클릭합니다.

   레코드 원본을 두 개 이상 사용할 경우 테이블 또는 쿼리의 공통 필드에 해당 테이블 또는 쿼리를 조인해야 합니다. 테이블 및 쿼리 조인에 대한 자세한 내용은 참고 항목 섹션을 참조하세요.

3. 테이블 표시 대화 상자를 닫습니다.

4. 디자인 탭의 쿼리 유형 그룹에서 크로스탭을 클릭합니다.

5. 쿼리 디자인 창에서 행 머리글 원본으로 사용할 각 필드를 두 번 클릭합니다. 행 머리글로 사용할 필드는 3개까지 선택할 수 있습니다.

6. 쿼리 디자인 눈금에서 각 행 머리글 필드의 크로스탭 행에서 행 머리글을 선택합니다.

   조건 행에 식을 입력하여 해당 필드의 결과를 제한할 수 있습니다. 정렬 행을 사용하여 필드에 대한 정렬 순서를 지정할 수도 있습니다.

7. 쿼리 디자인 창에서 행 머리글 원본으로 사용할 필드를 두 번 클릭합니다. 열 머리글 필드는 하나만 선택할 수 있습니다.

8. 쿼리 디자인 눈금에서 열 머리글 필드의 크로스탭 행에서 열 머리글을 선택합니다.

   조건 행에 식을 입력하여 열 머리글 필드의 결과를 제한할 수 있습니다. 그러나 열 머리글 필드와 함께 조건 식을 사용하면 크로스탭 쿼리에서 반환되는 열 수가 제한되지 않습니다. 대신 데이터가 포함된 열을 제한합니다. 예를 들어 빨강, 녹색, 파랑의 세 가지 가능한 값이 있는 열 머리글 필드가 있다고 가정합니다. 열 머리글 필드에 ='blue' 조건을 적용하는 경우 크로스탭은 여전히 빨간색 열과 녹색의 열을 표시하지만 파란색 열에만 데이터가 포함됩니다.

   열 머리글로 표시되는 값을 제한하려면 쿼리의 열 머리글 속성을 사용하여 고정 값 목록을 지정할 수 있습니다. 자세한 내용은 다음 섹션을 참조하세요.

9. 쿼리 디자인 창에서 요약 값을 계산하는 데 사용할 필드를 두 번 클릭합니다. 요약 값에 사용할 필드는 하나만 선택할 수 있습니다.

10. 쿼리 디자인 눈금에서 요약 값 필드의 요약 행에서 값을 계산하는 데 사용할 집계 함수를 선택합니다.

11. 요약 값 필드의 크로스탭 행에서 값을 선택합니다.

    요약 값 필드에 대해서는 조건을 지정하거나 값을 정렬할 수 없습니다.

12. 디자인 탭의 결과 그룹에서 실행을 클릭합니다.

열 머리글에 고정 값 지정

열 머리글에 사용할 고정 값을 지정하려면 쿼리의 열 머리글 속성을 설정할 수 있습니다.

1. 디자인 보기에서 크로스탭 쿼리를 엽니다.

2. 속성 시트가 보이지 않으면 F4 키를 눌러 표시합니다.

3. 속성 시트의 일반 탭 바로 위에 선택 유형이 쿼리 속성으로 되어 있는지 확인합니다. 다르게 표시되어 있으면 쿼리 디자인 눈금 위쪽의 빈 곳을 클릭합니다.

4. 속성 시트에서 일반 탭의 열 머리글 속성에 열 머리글로 사용할 값 목록을 쉼표로 구분된 형식으로 입력합니다.

   열 머리글에는 대부분의 문장 부호를 비롯한 일부 문자를 사용할 수 없습니다. 값 목록에 이러한 문자를 사용하면 해당 문자에 밑줄(_)이 대신 표시됩니다.

크로스탭 쿼리에 대한 SQL 구문

SQL에서 크로스탭 쿼리는 TRANSFORM 문으로 표현됩니다. TRANSFORM 문은 다음 구문으로 구성됩니다.

TRANSFORM aggfunction
selectstatement
피 벗 피벗 필드 [IN(value1[, value2[, ...]]])]

TRANSFORM 문은 다음과 같이 구성됩니다.

구성 요소	설명
aggfunction	선택한 데이터에서 작동하는 SQL 집계 함수입니다.
selectstatement	SELECT 문입니다.
pivotfield	쿼리 결과 집합에서 열 머리글을 만드는 데 사용할 필드 또는 식입니다.
value1, value2	열 머리글을 만드는 데 사용되는 고정 값입니다.

SQL 보기는 크로스탭 쿼리의 레코드 원본으로 사용할 수 있는 테이블 또는 쿼리 수를 제한하지 않습니다. 그러나 크로스탭 쿼리에 사용할 데이터를 모두 반환하는 선택 쿼리를 만든 다음 해당 선택 쿼리를 레코드 원본으로 사용하여 디자인을 간단하게 유지할 수 있습니다. 선택 쿼리 작성에 대한 자세한 내용은 참고 항목 섹션을 참조하세요.

1. 만들기 탭의 쿼리 그룹에서 쿼리 디자인을 클릭합니다.

2. 테이블 표시 대화 상자를 닫습니다.

3. 디자인 탭의 보기 그룹에서 보기를 클릭한 다음 SQL 보기를 클릭합니다.

4. SQL 개체 탭에서 다음 SQL을 입력하거나 붙여넣습니다.

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. 첫 번째 줄에서 TRANSFORM 뒤에 요약 값을 계산하는 데 사용할 식을 입력합니다(예: Sum([금액])).

   레코드 원본으로 둘 이상의 테이블 또는 쿼리를 사용하는 경우 각 필드 이름의 일부로 테이블 또는 쿼리 이름을 포함합니다(예: Sum([경비].[금액])).

6. 두 번째 줄에서 SELECT 뒤에 행 머리글에 사용할 필드 또는 필드 식의 목록을 입력합니다. 쉼표를 사용하여 목록 항목을 구분합니다(예: [예산].[Dept_ID], [경비].[유형]).

7. 세 번째 줄에서 FROM 뒤에 레코드 원본으로 사용하는 테이블 또는 쿼리의 목록을 입력합니다(예: Budget, Expense).

8. 네 번째 줄에서 GROUP BY 뒤에 6단계에서 SELECT 절에 사용한 것과 동일한 필드 목록을 입력합니다.

9. 다섯 번째 줄에서 PIVOT 뒤에 열 머리글로 사용할 필드 이름 또는 식을 입력합니다(예: PIVOT [예산].[연도]).

경우에 따라 행 또는 열 머리글에 필드의 모든 값을 사용하는 대신 필드 값을 범위로 그룹화한 다음 행 또는 열 머리글에 해당 범위를 사용하려고 합니다. 예를 들어 열 머리글에 "Age" 필드를 사용한다고 가정해 보겠습니다. 각 연령에 대해 열을 사용하는 대신 연령 범위를 나타내는 열을 사용하는 것이 좋습니다.

식에 IIf 함수를 사용하면 행 또는 열 머리글에 사용할 범위를 만들 수 있습니다.

1. 디자인 보기에서 크로스탭 쿼리를 엽니다.

2. 쿼리 디자인 눈금에서 필드 행의 빈 열을 마우스 오른쪽 단추로 클릭한 다음 바로 가기 메뉴에서 확대/축소를 클릭합니다.

3. 확대/축소 상자에 필드 별칭을 입력하고 콜론(:)을 입력합니다.

4. IIf()를 입력합니다.

5. IIf 다음의 괄호 안에 필드 값의 첫 번째 범위를 정의하는 비교 식을 입력합니다.

   예를 들어 Age 필드에 대한 범위를 만들고 각 범위를 20년으로 지정한다고 가정해 보겠습니다. 첫 번째 범위에 대한 비교 식은 [Age]<21입니다.

6. 비교 식 다음에 쉼표를 입력한 후 범위 이름을 따옴표로 묶어 입력합니다. 이 이름은 해당 범위 안에 포함되는 값의 크로스탭 머리글로 사용됩니다.

   예를 들어 [나이]<21 다음에 쉼표를 입력하고 "0-20살"을 입력합니다.

7. 범위 이름의 닫는 따옴표 다음에 쉼표를 입력한 후 다음 중 하나를 실행합니다.

   • 다른 범위를 만들려면 IIf()를 입력한 후 5-7단계를 반복합니다.

   • 마지막 범위인 경우 범위 이름만 입력합니다.

     예를 들어 다음은 나이 필드를 20년 단위의 범위로 구분하는 중첩된 IIf 식입니다. 여기서는 읽기 쉽게 줄 바꿈을 적용했습니다.

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     참고: Access에서는 이 식을 계산할 때 IIf 문 중 하나가 True로 계산되면 계산을 즉시 중지합니다. 이 경우 특정 범위의 하한값 보다 작은 모든 값은 이미 True로 계산되었기 때문에 각 범위의 하한값은 따로 지정하지 않아도 됩니다.

8. 쿼리 디자인 눈금의 요약 행에서 묶는 방법을 선택합니다.

9. 크로스탭 행에서 범위를 행 머리글 또는 열 머리글로 사용할지 지정합니다. 참고로 행 머리글은 3개까지 지정할 수 있고 열 머리글은 하나만 지정할 수 있습니다.

크로스탭 쿼리가 실행되면 입력을 묻는 메시지를 표시할 수 있습니다. 예를 들어 여러 행 머리글을 사용한다고 가정해 보겠습니다. 그 중 하나는 국가/지역입니다. 모든 국가 또는 지역에 대한 데이터를 항상 표시하는 대신 쿼리에서 이름을 묻는 메시지를 표시한 다음 사용자의 입력에 따라 데이터를 표시할 수 있습니다.

매개 변수 프롬프트는 모든 행 머리글 필드에 추가할 수 있습니다.

1. 디자인 보기에서 크로스탭 쿼리를 엽니다.

2. 사용자 입력을 요청할 행 머리글 필드의 조건 행에 질문 텍스트를 대괄호로 묶어 입력합니다. 쿼리를 실행하면 이 질문 텍스트가 프롬프트로 표시됩니다.

   예를 들어 조건 행에 [Which country or region?]을 입력한 경우, 쿼리를 실행하면 "국가 또는 지역?"이라는 질문, 입력란, 확인 단추가 포함된 대화 상자가 나타납니다.

   팁:  Like 연산자를 사용하여 식에 와일드카드를 연결하면 보다 다양한 매개 변수를 사용할 수 있습니다. 예를 들어 [국가 또는 지역?]을 조건으로 사용하는 대신 Like [국가 또는 지역?]&"*"을 사용하여 다양한 사용자 입력을 허용할 수 있습니다. Like 연산자를 사용해도 매개 변수 프롬프트의 모양은 동일합니다.

3. 디자인 탭의 표시/숨기기 그룹에서 매개 변수를 클릭합니다.

4. 쿼리 매개 변수 대화 상자의 매개 변수 열에 조건 행에 사용한 것과 같은 매개 변수 프롬프트를 입력합니다. 이때 대괄호는 그대로 사용하되 Like 연산자 또는 연결된 와일드카드는 포함하지 마세요.

5. 데이터 형식 열에서 매개 변수의 데이터 형식을 선택합니다. 여기서 선택하는 데이터 형식은 행 머리글 필드의 데이터 형식과 같아야 합니다.

크로스탭 쿼리에서 요약 값을 계산하는 데 사용하는 필드에 null 값이 포함되어 있는 경우 이러한 값은 사용 중인 모든 집계 함수에서 무시됩니다. 일부 집계 함수에서는 null 값 때문에 결과가 달라질 수 있습니다. 예를 들어 평균을 계산하려면 모든 값을 합한 다음 그 결과를 값 개수로 나누어야 합니다. 그런데 필드에 null 값이 있으면 해당 null 값은 값 개수에 포함되지 않습니다.

경우에 따라 null 값을 0으로 바꾸어 집계 계산에 해당 값이 포함되도록 해야 할 수 있습니다. null 값은 Nz 함수를 사용하여 0으로 바꿀 수 있습니다.

Nz 구문

Nz ( variant [, valueifnull ] )

Nz 함수 구문에는 다음과 같은 인수가 사용됩니다.

인수	설명
variant	필수 요소입니다. Variant데이터 형식의 변수입니다.
valueifnull	선택 요소입니다(쿼리에 사용된 경우 제외). variant 인수가 Null일 경우 반환할 값을 제공하는 Variant 입니다. 이 인수를 사용하면 0이나 빈 문자열이 아닌 값을 반환할 수 있습니다. 참고: 쿼리 식에 Nz 함수를 사용할 때 valueifnull 인수를 지정하지 않으면 null 값이 포함된 필드에 빈 문자열이 반환됩니다.

1. 디자인 보기에서 쿼리를 연 상태로 쿼리 디자인 눈금에서 값 필드를 마우스 오른쪽 단추로 클릭합니다.

2. 바로 가기 메뉴에서 확대/축소를 클릭합니다.

3. 확대/축소 상자에서 필드 이름 또는 식을 괄호로 묶은 다음 괄호 앞에 Nz를 입력합니다.

4. 닫는 괄호 바로 앞에 0을 입력합니다.

   예를 들어 "손실 시간" 필드에 Nz를 사용하여 null 값을 0으로 변환하려는 경우 완성된 식은 다음과 같습니다.

Nz([Hours Lost], 0)

• 간결하게 유지합니다.    행 조합 수가 증가함에 따라 크로스탭 쿼리를 읽기 어려울 수 있습니다. 필요한 것보다 더 많은 행 머리글을 사용하지 마세요.

• 단계별로 크로스탭 작성    크로스탭의 사용 범위는 테이블에만 제한되는 것이 아닙니다. 필요한 경우 요약 쿼리를 작성한 후 이 쿼리를 크로스탭 쿼리의 레코드 원본으로 사용할 수도 있습니다.

• 신중하게 열 머리글 필드 선택    일반적으로 크로스탭 데이터시트는 열 머리글 수가 비교적 적을 때 읽기 쉽습니다. 머리글로 사용할 필드를 파악한 후에는 고유한 값이 가장 적은 필드를 사용하여 열 머리글을 생성하는 것이 좋습니다. 예를 들어 쿼리를 사용하여 나이 및 성별을 기준으로 값을 계산하는 경우 나이보다는 가능한 값이 적은 성별을 열 머리글로 사용하는 것이 좋습니다.

• WHERE 절에서 하위 쿼리 사용    크로스탭 쿼리에서 WHERE 절의 일부로 하위 쿼리를 사용할 수 있습니다.

맨 위로 이동