여러 액세스 파일 병합: 데이터 결합 및 통합을 위한 완벽한 가이드

여러 Microsoft Access 데이터베이스를 사용할 때 데이터 손실이나 링크 손상 없이 데이터베이스를 통합해야 하는 경우가 불가피하게 발생합니다. 실제 환경에서는 동일한 데이터베이스의 여러 복사본 , 프로젝트별로 분리된 테이블, 외부 시스템에서 가져온 데이터 등 다양한 상황이 발생할 수 있습니다. 이러한 데이터베이스를 효과적으로 병합하는 방법을 아는 것은 안정적인 시스템을 유지하는 것과 중복 데이터로 인해 혼란스러운 시스템을 만드는 것 사이의 차이를 만듭니다.

이 실용적인 가이드에서는 Access에서 JOIN이 작동하는 방식과 유형부터 UNION 쿼리 활용법, 자동 번호 충돌 해결, 충돌 감지, 두 개의 .accdb 파일이 별도로 생성된 경우 권장 사항까지 필요한 모든 내용을 단계별로 자세히 설명합니다 . 또한 워크플로에 따라 Adobe Acrobat 없이 VBA를 사용하여 PDF를 병합하는 유용한 팁도 포함되어 있습니다 .

여러 Access 파일을 병합한다는 것은 실제로 무엇을 의미합니까?

병합은 단순히 복사해서 붙여넣는 것이 아닙니다. 실제로 병합은 유사한 테이블을 통합하고, 참조 무결성을 유지하며, 중복되거나 충돌하는 레코드를 처리하는 기준을 정하는 것을 의미합니다 . 또한 Access에서 테이블 간의 관계 설정 방식과 서로 다른 소스의 데이터를 결합하는 방법을 이해하는 것도 중요합니다. 이는 호환 가능한 결과 집합을 쌓을 때 JOIN 쿼리나 UNION 쿼리를 통해 수행됩니다.

시작하기 전에 각 파일의 구조, 키 및 관계를 검토하는 것이 좋습니다. 어떤 테이블이 마스터 테이블이고, 어떤 테이블이 종속 테이블인지, 그리고 어떤 필드가 키(기본 키 및 외래 키) 역할을 하는지 명확히 이해하면 많은 문제를 예방할 수 있습니다. 잘 정의된 관계에 대한 연쇄 업데이트를 활성화하는 것과 같은 최소한의 준비만으로도 식별자가 변경될 때 하위 레코드가 멈추는 현상을 방지할 수 있습니다.

Access의 관계 및 조인: 모든 것의 기반

데이터베이스는 공통 필드를 통해 서로 연결된 테이블들로 구성됩니다. Access에서는 이러한 관계가 쿼리 디자인 보기에서 조인 선으로 표시됩니다. 선은 연결 고리 역할을 하는 필드를 연결하고 데이터가 어떻게 결합되는지 정의합니다. 일대다 관계에서 참조 무결성을 적용한 경우, Access는 선 위에 숫자 1과 무한대 기호(∞)를 표시합니다.

조인은 스마트 필터처럼 작동합니다. 각 측에서 어떤 행들이 쌍을 이루는지, 그리고 어떻게 쌍을 이루는지를 정의합니다. 필터링 기능 외에도, 조인을 통해 조건을 만족하는 각 쌍은 결과에서 단일 행으로 변환됩니다. 따라서 사용 중인 조인 유형을 정확히 파악하고, 필요한 경우 조인 속성 (행을 두 번 클릭)을 열어 동작을 조정하는 것이 중요합니다.

Access의 조인 유형

내부 조인

내부 조인은 조인 필드에서 일치하는 값을 가진 두 테이블의 행만 반환합니다. 이는 가장 일반적인 유형이며, Access는 호환되는 관계 또는 필드를 감지하면 자동으로 생성합니다. 유형을 지정하지 않으면 Access는 기본적으로 내부 조인을 사용합니다.

쿼리에 두 테이블을 추가할 때, 두 테이블이 호환되는 형식의 필드를 공유하고 그중 하나가 기본 키인 경우, Access는 일반적으로 자동으로 조인을 생성합니다. 하지만 쿼리를 데이터 소스로 사용하는 경우에는 디자인 그리드에서 한 필드를 다른 필드 위로 드래그하여 조인 선을 그려 수동으로 연결을 만들어야 합니다.

FROM 절에서 INNER JOIN을 사용하는 SQL 구문은 다음과 같습니다. 모든 유효한 관계 연산자(같음, 같지 않음, 크거나 작음 등)를 사용할 수 있습니다.

FROM tabla1 INNER JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

매우 특수한 경우를 제외하고는 가장 일반적인 연산자는 같음(equality)이라는 점을 명심하십시오. 다른 연산자를 사용하면 같지 않은 조합이 생성됩니다 (이에 대해서는 아래에서 살펴보겠습니다).

왼쪽 및 오른쪽 외부 조인(LEFT JOIN/RIGHT JOIN)

외부 조인은 한쪽 테이블의 모든 행을 포함하며, 다른 쪽 테이블에 일치하는 행이 없더라도 마찬가지입니다. 왼쪽 조인(LEFT JOIN)에서는 왼쪽 테이블의 모든 행이 나열되고, 오른쪽 테이블에서는 일치하는 행만 표시됩니다. 오른쪽 조인(RIGHT JOIN)에서는 그 반대입니다. 이는 데이터의 범위나 완전성을 감사할 때 한쪽 테이블의 "고아" 레코드를 보존하는 데 매우 유용합니다.

내부 조인을 외부 조인으로 변환하려면 디자인 보기에서 조인 행을 두 번 클릭하고 한쪽의 모든 레코드를 포함하는 옵션을 선택한 다음 확인을 클릭합니다. 그러면 행에 어떤 테이블에서 모든 행이 포함되는지를 나타내는 방향 화살표가 표시됩니다.

같은 쿼리에서 LEFT JOIN과 INNER JOIN을 함께 사용하는 경우, Access는 어떤 JOIN을 먼저 실행해야 할지 알 수 없어 모호성 오류가 발생할 수 있습니다. 이 문제를 해결하려면 쿼리 순서를 바꾸거나 , 하위 쿼리로 나누거나, 조인 순서를 재배열하여 평가 순서를 명확히 해야 합니다.

FROM 절에서 LEFT/RIGHT JOIN SQL 구문 :

FROM tabla1 LEFT JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

FROM tabla1 RIGHT JOIN tabla2
  ON tabla1.campo1 = tabla2.campo2

UNION을 사용한 전체 외부 조인(FULL OUTER)

Access에는 완전한 외부 조인 기능이 없지만, UNION 연산자를 사용하여 왼쪽 조인 결과와 오른쪽 조인 결과를 결합하고 중복되는 행을 필터링하여 유사한 기능을 구현할 수 있습니다. 원리는 간단합니다. 양쪽의 모든 행을 결합 하고 일치하는 행은 병합하고, 일치하지 않는 행은 null 값으로 남겨두는 것입니다.

일반적인 작업 계획:

1. 조인 필드에 LEFT JOIN을 사용하여 쿼리를 작성합니다.
2. SQL을 복사하고 LEFT를 RIGHT로 변경한 후 UNION 아래에 붙여넣습니다.
3. 오른쪽 부분에는 왼쪽 테이블의 키 필드가 무엇인지 확인하는 WHERE 절을 추가합니다. IS NULL 중복된 일치를 방지합니다.

이렇게 하면 Access에서 기본 지원 없이도 FULL OUTER의 효과를 재현할 수 있으며, 양방향 모두에서 일관되고 완전한 결과를 유지할 수 있습니다.

크로스 조인

크로스 조인은 한 테이블의 각 행을 다른 테이블의 각 행과 서로 매칭시키는 작업입니다(카르테시안 곱). 이는 쿼리에 테이블을 추가하면서 테이블 간의 관계를 정의하지 않은 경우에 자주 발생하며, 수천 개의 의미 없는 조합이 생성되고 실행 시간이 길어지는 결과를 초래 합니다. 하지만 크로스 조인을 유용하게 활용할 수 있는 시나리오도 있습니다. 예를 들어, 서로 다른 이론적 반품률을 가진 고객들의 모든 가능한 조합을 분석하는 경우(91명의 고객 x 5가지 반품률 = 455개의 가상 행)가 있습니다.

쿼리 결과에 비정상적으로 많은 레코드가 표시되는 경우, 모든 테이블에 명시적인 조인이 되어 있는지 다시 확인하십시오. 일반적으로 올바른 필드를 드래그하여 배치하는 것만으로도 의도치 않은 교차 ​​조인을 방지하고 일관된 결과를 얻을 수 있습니다.

불평등 조인(non-equi JOIN)

합집합은 반드시 같음을 기준으로 할 필요는 없습니다. >, <, >=, <=, <>와 같은 연산자를 사용하여 범위, 플래그 또는 더 복잡한 조건을 결합할 수 있습니다. Access에서는 이러한 유형의 합집합을 시각적 보기에서 직접 설계할 수 없습니다. SQL 보기 로 전환하여 '=' 연산자를 찾고 필요한 연산자로 바꿔야 합니다. 중요: 부등호로 변경하면 연산자를 다시 '='로 되돌리기 전까지는 디자이너에서 쿼리를 다시 열 수 없을 수 있습니다.

UNION 쿼리: 결과 스태킹 및 유형 조화

UNION 문은 여러 쿼리 결과를 하나의 가상 테이블로 결합하려는 경우에 매우 유용합니다. 단, 결합하려는 테이블의 열 개수와 데이터 형식이 동일해야 합니다. UNION 문은 완전한 외부 조인 (FULL OUTER JOIN) 과 유사한 효과를 내거나 , 서로 다른 유형의 트랜잭션을 동일한 뷰에 결합하려는 경우에 핵심적인 역할을 합니다.

일반적인 예시를 들어보겠습니다. 제품 거래 내역을 조회하고 수량을 구매량과 판매량 두 열로 나누려고 합니다. 첫 번째 방법은 해당되지 않는 열에 0을 입력하고 판매량 부분과 구매량 부분을 합치는 것입니다. 나중에 0이 표시되지 않도록 0을 Null 로 바꾸면 다음과 같은 문제가 발생할 수 있습니다. Access는 UNION 절의 첫 번째 SELECT 문에서 각 열의 데이터 형식을 추론합니다.

첫 번째 SELECT 문이 NULL(숫자가 아닌 값)을 반환하면 Access는 해당 열을 텍스트 또는 NULL로 간주하여 나머지 열에도 영향을 줄 수 있습니다. 첫 번째 SELECT 문에 빈 문자열("")을 입력하면 Access는 이를 텍스트로 처리하고, 결과적으로 전체 열을 텍스트로 변환합니다 (데이터시트에서 왼쪽 정렬로 표시되는 것을 통해 이를 확인할 수 있습니다).

Access에서 해당 열을 숫자형으로 처리하고 Null 값을 허용하도록 하는 간단한 방법은 다음과 같은 식을 사용하는 것입니다. 이 식은 항상 Null을 반환하지만 숫자 출력에 대한 평가를 강제합니다.

IIf(False, 0, Null)

구매와 판매를 통합하는 데 적용할 경우, 유효한 구조는 다음과 같습니다.

SELECT , , , ,
       IIf(False, 0, Null) AS Buy,  AS Sell
FROM   
UNION
SELECT , , , ,
        AS Buy, Null AS Sell
FROM   
ORDER  BY  DESC;

고급 대안: 데이터 형식을 정의하고 행을 반환하지 않는 템플릿 SELECT 문을 사용합니다. 이는 결과에 영향을 주지 않고 각 열의 데이터 형식을 설정하는 WHERE FALSE 절을 사용하는 SELECT 패턴입니다.

SELECT 0 AS , Date() AS ,
       "" AS , "" AS ,
       0 AS Buy, 0 AS Sell
FROM   
WHERE  False
UNION
SELECT , , , , Null AS Buy,  AS Sell
FROM   
UNION
SELECT , , , ,  AS Buy, Null AS Sell
FROM   
ORDER  BY  DESC;

이 접근 방식을 사용하면 Access에서 Null 값을 가진 숫자 형식을 올바르게 인식하여 UNION의 어느 부분에든 빈 문자열이 있을 때 원치 않는 텍스트 변환을 방지할 수 있습니다.

사용 방법 가이드: 두 개의 포크된 Access 데이터베이스 병합

흔히 발생하는 상황: 두 사람이 백업 파일에 데이터를 입력하기 시작했고, 이제 거의 동일하지만 변경 내용이 다른 두 개의 파일이 생겼습니다. 먼저 알아야 할 점은 Access는 특별히 설정하지 않는 한 각 레코드에 "마지막 수정일" 필드를 자동으로 저장하지 않는다는 것 입니다 . Now() 함수와 폼 이벤트를 사용하여 나중에 이 필드를 추가할 수는 있지만, 이미 파일이 분기된 경우에는 각 레코드의 어떤 버전을 유지할지 결정하는 규칙을 만들어야 합니다.

안전하게 진행하기 위한 권장 단계:

1. 백업 및 압축/수리: 모든 것을 복제한 다음 각 파일에 대해 압축 및 복구를 실행하여 깨끗한 상태에서 시작합니다.
2. 감사 구조테이블과 필드를 비교하고, 각 테이블의 기본 키를 식별하고, 관계를 기록하세요. 가능하면 LastEditedDate 필드를 추가하고 가장 유용한 정보(주문 날짜, 생성 날짜 등)로 채우세요.
3. 자동 번호 충돌을 해결합니다: 두 데이터베이스가 충돌할 수 있는 자동 번호 ID를 공유하는 경우, "shift" 기법을 적용하세요. 데이터베이스 중 하나의 자동 번호 필드를 숫자(긴 정수)로 변경하고 다른 데이터베이스의 최대 ID를 추가하여 업데이트를 실행하세요. 이렇게 하면 식별자가 밟히지 않았습니다.

메인 테이블의 이동 ID를 업데이트하는 예시로는 다른 데이터베이스에서 감지된 최댓값을 추가하는 방식이 있습니다. 이렇게 하면 중복을 방지할 수 있으며, 관계형 데이터베이스 간의 연쇄 업데이트 덕분에 하위 테이블도 새로운 ID를 참조하게 됩니다.

다음으로, 수정된 데이터베이스의 모든 테이블을 "대상" 데이터베이스로 가져온 다음, 두 데이터베이스를 결합하는 추가 쿼리를 생성합니다 . 충돌이 발생하는 경우(동일한 ID를 갖지만 내용이 다른 경우), 정책을 정의합니다. 가장 최근 날짜의 레코드를 유지하거나, 두 레코드 모두에 접미사를 붙여 유지하고 하나는 "검토 필요"로 표시합니다.

중복 및 충돌을 감지하려면 일치/불일치 레코드 쿼리 패턴이 매우 유용합니다. 예를 들어, 반대편에서 일치하지 않는 레코드를 찾으려면 다음과 같이 합니다.

SELECT A.*
FROM   TablaA AS A
LEFT JOIN TablaB AS B
  ON A.ID = B.ID
WHERE  B.ID IS NULL;

동일한 ID를 가진 두 버전을 비교하려면 관심 있는 열이 다른 행 목록을 표시하면 됩니다. 실제로 날짜 필드가 명확하지 않은 경우에는 "조정 대기 중" 보기를 생성하고 개별적으로 문제를 해결하는 것이 편리합니다.

마지막으로, 관계 창(도구 메뉴)에서 관계를 확인합니다. 모든 테이블을 포함하고 키를 사용하여 연결합니다. 필요한 경우 계단식 업데이트 확인란을 선택합니다 . 이렇게 하면 기본 테이블의 식별자를 변경해도 보조 테이블에 변경 사항이 반영되어 연결이 끊어지지 않습니다.

한 번에 여러 테이블을 가져와서 조인합니다.

일부 워크플로에서는 데이터 액세스 창에서 동일한 가져오기 작업으로 최대 5개의 테이블을 선택하고 쌍별 조인을 정의할 수 있습니다. 이 과정은 테이블을 선택 하고 임시 영역으로 가져온 다음 공통 키 필드(예: CustomerID)를 사용하여 두 개씩 조인하는 방식으로 진행됩니다. 값이 일치하면 결과는 개별 레코드와 일치합니다.

테이블이 두 개 이상인 스키마에서 "왼쪽 테이블"은 쌍을 이루는 첫 번째 테이블이고 "오른쪽 테이블"은 두 번째 테이블입니다. 여러 개의 조인을 연결할 수 있습니다(예: 고객 테이블과 주문 테이블, 그리고 주문 테이블과 제품 테이블). 연결 순서에 따라 동일한 테이블이 한 조인에서는 왼쪽 테이블이 되고 다른 조인에서는 오른쪽 테이블이 될 수 있다는 점에 유의하세요. 또한 단일 필드만으로는 고유성이 충분하지 않은 경우 여러 필드를 기준으로 조인 할 수도 있습니다.

한 가지 유의해야 할 제한 사항은 Apache Drill과 같은 특정 소스의 경우 시각적 편집기에서 두 테이블만 조인할 수 있다는 점입니다. 세 개 이상의 테이블을 조인하려면 SQL 모드를 사용 하고 조인 문에 괄호를 사용하지 마십시오. 해당 커넥터에서는 괄호를 지원하지 않습니다. 이는 매우 특수한 사항이지만, 다양한 소스에서 데이터를 가져올 때 알아두면 유용합니다.

잘못된 조합을 제거하세요

Access에서 원하지 않는 자동 병합이 생성되었거나 실수로 필드를 드래그하여 병합한 경우, 주저하지 말고 삭제하세요. 디자인 그리드에서 해당 행을 클릭하고 삭제 키를 누르거나 마우스 오른쪽 버튼을 클릭하고 삭제를 선택하면 됩니다 . 필요한 병합만 유지하는 것이 의도치 않은 병합을 방지하고 안정적인 결과를 얻는 데 중요합니다.

INNER, LEFT 및 RIGHT JOIN을 사용할 때의 좋은 사례

일반적으로 각 조합에 대해 간단한 필터를 적용하여 예상되는 행 수를 반환하는지 확인하는 것이 좋습니다. 동일한 쿼리에서 INNER 절과 LEFT 절을 혼합하여 사용하고 Access에서 모호성 오류가 발생하는 경우, 문제를 분리해야 합니다. 먼저 INNER 절 부분을 저장된 쿼리로 만들고, 이를 LEFT 절을 적용할 쿼리의 소스로 사용하여 평가 순서를 명확하게 정의하십시오.

또 다른 실용적인 팁은 각 조합이 어떤 역할을 하고 왜 그렇게 하는지 주석(쿼리 속성)에 기록해 두는 것입니다. 다른 동료들이 데이터베이스를 열었을 때, 특이한 RIGHT JOIN이나 범위를 기준으로 필터링하는 unequal join 과 같은 구문의 이유를 알면 도움이 될 것입니다.

일상 생활을 위한 Access의 SQL 트릭

위에서 언급한 패턴(IIf(False,0,Null) 및 WHERE 절에 False를 사용한 SELECT 템플릿) 외에도 Access에서는 조인에 관계 연산자를 사용하여 날짜 또는 기간이 겹치는 경우를 해결할 수 있다는 점을 기억하십시오. 적절하게 배치된 부등호는 복잡한 하위 쿼리를 대체하여 필요한 행 집합만 정확하게 검색할 수 있도록 해줍니다.

만약 UNION 절에서 의도적으로 중복 행을 유지해야 하는 경우(예: 발생 횟수를 계산해야 하는 경우)에는 UNION 대신 UNION ALL을 사용하세요 . Access는 UNION 절에서는 중복 행을 제거하지만 UNION ALL 절에서는 제거하지 않는데, 최종 보고서의 목적에 따라 이 점이 중요할 수 있습니다.

추가: Access의 PDF를 VBA와 결합(Acrobat 없이)

일부 사용자는 여러 개의 PDF 보고서를 생성하여 Access에서 하나의 문서로 병합해야 합니다. Adobe Acrobat이 없더라도 몇 줄의 코드로 이 문제를 해결할 수 있는 VBA 유틸리티가 있습니다. 개발자 Albert Kallal 이 제공하는 간단한 모듈은 Acrobat 없이 Access에서 PDF를 병합할 수 있는 유용한 솔루션입니다. 이 솔루션은 데이터베이스 프로세스에서 직접 통합 문서 또는 결과물을 생성하는 작업을 자동화하는 데 매우 효율적입니다.

기본적으로 PDF 파일 경로 목록 과 출력 대상 위치를 모듈에 전달하면 모듈이 자동으로 병합 작업을 수행합니다. 이렇게 하면 쿼리 실행이나 보고서 내보내기처럼 PDF 병합을 워크플로에 자연스럽게 통합할 수 있습니다.

위에서 설명한 모든 내용을 통해 이제 Access에서 데이터를 결합하는 방법(INNER/LEFT/RIGHT, 불균등 결합, 교차 결합)을 이해하고, UNION을 사용하여 FULL OUTER 결합을 구현하고, UNION 쿼리에서 데이터 형식의 영향을 측정하고, 충돌을 방지하기 위해 관계 및 자동 번호 매기기를 조정하고, PDF 병합과 같은 실용적인 자동화 기능을 익히는 등 완벽한 로드맵을 갖게 되었습니다. 이러한 기본기를 숙달하면 여러 Access 파일을 병합하는 작업이 더 이상 번거로운 과정이 아니라 여러 팀이 동시에 작업하는 경우에도 체계적이고 반복 가능한 프로세스로 바뀝니다.