Access 데이터베이스 간소화:자동의 정확한 교차 테이블 계산을 위해 계산된 필드 추가

Microsoft Access는 수동 데이터 입력을 줄이고 오류를 최소화하며 데이터베이스의 실시간 일관성을 유지함으로써 연결된 테이블 전체에서 복잡한 계산을 자동화하는 강력한 기능을 제공합니다. 계산된 필드는 데이터베이스를 더욱 자동화할 수 있습니다. 사용자에게 총액, 할인, 기한 또는 이익 값을 수동으로 입력하도록 요청하는 대신 Access에서는 기존 필드에서 해당 값을 계산할 수 있습니다.

이 자습서에서는 자동 교차 연결 계산을 위해 Access 테이블에 계산된 필드를 추가하는 방법을 보여줍니다. 관련 테이블 전체에서 자동으로 값을 가져와 계산하는 계산된 필드를 만들어 보겠습니다.

고급 데이터베이스에서는 계산된 필드를 무작위로 사용해서는 안 됩니다. 가장 중요한 규칙은 다음과 같습니다:

• 동일한 레코드의 필드에 의존하는 값에 대해 계산된 필드 사용
• 연결된 테이블이나 여러 레코드에 의존하는 계산에 쿼리 사용

1단계:샘플 관련 테이블 설정

여러 테이블에 걸쳐 도달하는 계산된 필드는 견고한 관계에 따라 달라집니다. 단일 표현식을 작성하기 전에 여기에서 시작하세요.

관계 만들기:

• 데이터베이스 도구로 이동 탭>> 관계를 선택합니다.
• 테이블을 추가합니다.
  • 고객 ID를 드래그하세요. Customers 테이블에서 CustomerID로 주문 테이블에서
  • 제품 ID 드래그 Products 테이블에서 ProductID로 OrderDetails 테이블에서
  • 주문 ID를 드래그하세요. Orders 표에서 OrderID로 OrderDetails 테이블에서
• 참조 무결성 시행 활성화
• 확인을 클릭하세요.

이 설정을 사용하면 교차 연결된 계산이 가능합니다(예:라인 합계의 합계로 주문 소계). 이 연결은 교차 테이블 조회를 안정적으로 만드는 것입니다. 이것이 없으면 참조 레코드가 삭제되거나 일치하지 않을 때 계산된 필드가 자동으로 null을 반환할 수 있습니다.

2단계:테이블에 단순 계산 필드 추가

• 주문 세부정보를 엽니다. 디자인 보기의 표
• 첫 번째 빈 행:
  • 필드 이름:LineTotal
  • 데이터 유형:계산됨
• Access에서 식 작성기가 열립니다.
• 수식을 입력하세요:

• 또는 시각적으로 구축:OrderDetails를 확장합니다. 표>> 수량을 두 번 클릭합니다. 및 단가 을 클릭한 다음 *을 추가하세요. 연산자
• 결과 유형 설정 (통화, 숫자, 텍스트 등) 표현식이 반환하는 내용과 일치
  • 필드 속성 확장>> 통화를 선택하세요.
• 저장 테이블

할인이 포함된 총액:

[Quantity] * [UnitPrice] * (1 - [DiscountRate])

이제 Access에서는 모든 레코드에 대해 LineTotal을 자동으로 계산하므로 VBA나 수동 업데이트가 필요하지 않습니다. 데이터시트 보기에서 Quantity 또는 UnitPrice를 추가하거나 편집할 때마다 LineTotal이 자동으로 업데이트됩니다.

3단계:교차 연결된 계산 처리 - 쿼리에 도메인 집계 함수 사용

테이블 계산 필드에서는 직접 교차 테이블 참조가 허용되지 않습니다. 쿼리나 VBA를 사용해야 합니다. 도메인 집계 함수는 다른 테이블이나 쿼리에서 계산된 값을 식으로 가져오기 위한 Access의 기본 제공 메커니즘입니다. 가장 유용한 것은 다음과 같습니다:

검색어 만들기:

• 만들기로 이동 탭>> SQL 쿼리 선택
• 표 추가에서 창에서 Orders 및 Customers 테이블을 추가합니다.
• 필드 추가:CustomerName, OrderID
• 빈 필드 열에 총계에 대한 계산된 필드를 만듭니다.
• 다음 표현식을 삽입하세요:

Total: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])

• DSum()은 일치하는 OrderID에 대해 LineTotal을 합산합니다(테이블 전체에서 작동하는 도메인 집계 함수)
• qryOrderSummary로 저장
• 실행을 클릭하세요.

쿼리는 실행될 때마다 다시 계산됩니다. 이 쿼리를 양식, 보고서의 레코드 소스로 사용하거나 추가 계산의 기초로 사용하세요.

통화 형식:

Total: CCur(DSum("[LineTotal]","OrderDetails","[OrderID]=" & [Orders].[OrderID]))

4단계:계산 소스로 다중 테이블 계산 쿼리 작성

한 테이블의 고객 계층과 다른 테이블의 제품 가격을 포함하는 할인 합계 계산과 같은 보다 정교한 시나리오의 경우 모든 관련 테이블을 조인하는 기본 쿼리를 작성한 다음 계산된 필드 또는 양식에서 해당 쿼리를 참조해야 합니다.

단계:

• 만들기로 이동 탭>> SQL 쿼리 선택
• 주문 추가 , 제품 및 주문 세부정보 쿼리에
• 필드 추가:OrderID, ProductName
• 빈 필드 셀에 계산된 열을 추가합니다.

Profit: [DiscountedTotal] - [CostPrice]

• 쿼리를 qryOrderProfit으로 저장합니다.
• 실행을 클릭하세요.

이제 제품 이름이 포함된 모든 주문에 대한 수익 보고서가 제공됩니다.

이제 모든 양식, 보고서 또는 다운스트림 계산 필드는 DLookup() 또는 qryOrderProfit에 대한 하위 쿼리를 사용하여 완전히 계산된 값을 얻을 수 있습니다. 모두 교차 연결되고 자동입니다.

5단계:데이터 매크로를 사용한 업데이트 자동화

계산된 결과를 단순히 표시하는 것이 아니라 저장해야 하는 경우(예:OrderDetails 레코드가 변경될 때마다 계산된 합계를 Orders 테이블에 다시 쓰는 경우) 하위 테이블에 연결된 데이터 매크로를 사용하세요.

주문 테이블에 총계 필드 추가:

• 먼저 주문을 엽니다. 디자인 보기의 표
  • 필드 이름:합계
  • 데이터 유형:통화

계산된 필드가 아닌 일반 통화 필드여야 합니다.

설정:

• 주문 세부정보 열기 디자인 보기에서
• 표 도구로 이동 탭>> 표를 선택합니다. 탭>> 삽입 후/업데이트 후 선택

• 매크로 편집기에서 SetField를 사용합니다. 및 LookupRecord 작업:
• 조회 기록 선택

Lookup Up A Record In: Orders
Where Condition: [Orders].[OrderID] = [OrderDetails].[OrderID]

• 기록 편집 선택>> SetField를 선택하세요.

Name: [Orders].[Total]
Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderID])

• 저장을 클릭하세요.

이는 OrderDetails에 삽입하거나 업데이트할 때마다 자동으로 실행되어 다시 계산된 합계를 상위 Orders 테이블로 다시 푸시합니다. 완전히 자동화되어 VBA가 필요하지 않습니다.

업데이트 후 반복:

삽입 후 매크로는 새 세부 정보 행이 추가된 경우에만 합계를 업데이트합니다. 사용자가 수량, 단가 또는 할인율을 변경하면 총액도 업데이트되어야 합니다.

동일한 매크로를 추가하세요:

Look Up A Record In Orders
Where Condition: [Orders].[OrderID]=[OrderDetails].[OrderID]
 EditRecord
 SetField
 Name: [Orders].[Total]
 Value: DSum("[LineTotal]","OrderDetails","[OrderID]=" & [OrderDetails].[OrderID])

• 저장 그것

이제 OrderDetails에 레코드가 삽입되거나 업데이트될 때마다 Access에서는 자동으로 주문 총액을 다시 계산하여 Orders 테이블의 일치하는 레코드에 저장합니다.

6단계:계산 표시 및 사용

• 데이터시트 보기: 계산된 필드가 표시되고 실시간으로 업데이트됩니다.
• 양식/보고서: 전체 상호 연결된 결과를 보려면 쿼리(qryOrderSummary)를 기반으로 양식이나 보고서를 작성하세요. 표현식이 포함된 언바운드 텍스트 상자를 추가하세요.
• 필터링/정렬: 쿼리 기준이나 정렬에 계산된 필드를 사용할 수 있습니다.

도움말: 나중에 테이블의 계산된 필드를 편집하려면:

• 데이터시트 보기에서:열을 선택합니다.>> 필드를 선택합니다. 탭>> 표현식 수정
• 또는 디자인 보기로 돌아가기>> 속성을 선택합니다.>> 표현식을 선택하세요. .

모범 사례 및 성능 고려 사항

• 테이블 계산 필드보다 쿼리 선호 여러 테이블, 집계 또는 향후 변경 사항과 관련된 모든 경우. 쿼리는 이식성이 뛰어나고(예:SQL Server로) 유연합니다.
• 계산 저장 방지 성능이 요구하지 않는 한(예:즉석 합계가 느린 매우 큰 데이터 세트). 대신 쿼리에서 다시 계산하세요.
• 정규화: 원시 입력만 저장합니다. 출력을 동적으로 계산합니다.
• 테스트: 변경 후에는 항상 샘플 데이터로 확인하세요. 특히 관계를 추가한 후에는 더욱 그렇습니다.
• 성능: 계산된 필드가 너무 많거나 큰 테이블에 DSum() 호출이 복잡하면 속도가 느려질 수 있습니다. 외래 키를 색인화합니다.
• 제한사항 요약:
  • 테이블 계산 표현식에는 다른 테이블의 필드가 직접 입력되지 않습니다.
  • 테이블 계산 필드의 제한된 함수(전체 VBA 액세스를 위해서는 쿼리 사용).
  • 결과는 읽기 전용입니다.
• 확장 팁: 매우 고급 요구 사항이 있는 경우 논리를 SQL 보기 또는 계산 열이 더 많은 기능을 제공하는 SQL Server와 같은 백엔드로 마이그레이션하는 것을 고려해보세요.

일반적인 문제 및 문제 해결

• #오류 또는 #이름?: 필드 이름이 대괄호 [] 안에 있는지, 데이터 유형이 일치하는지, 관계가 활성화되어 있는지 확인하세요.
• 순환 참고문헌: 자체 표현식에서 계산된 필드를 참조하거나 루프를 생성하지 마세요.
• 데이터 유형 불일치: 결과 유형을 명시적으로 설정합니다(예:통화 필드).
• 교차 테이블 실패: 조인을 사용하여 논리를 쿼리로 이동하거나 DSum()을 사용하세요.
• 연결된 테이블(예:SharePoint 또는 다른 데이터베이스)을 사용하는 경우 계산된 필드에 행 제한이 있거나 새로 고침 문제가 있을 수 있습니다.

결론

위의 단계에 따라 자동 교차 연결된 계산을 위해 Access 테이블에 계산된 필드를 추가할 수 있습니다. 계산된 필드는 Microsoft Access에서 행 수준 계산을 자동화하는 데 유용합니다. 라인 합계, 할인, 기한 및 주문 라인당 이익과 같은 값에 적합합니다. 고급 교차 연결된 계산의 경우 쿼리는 올바른 도구입니다. 쿼리는 테이블 간의 관계를 따라 주문 총계, 고객 판매 총계, 재고 잔액 및 이익 요약을 계산합니다. Access에서 계산된 필드와 교차 연결된 계산을 마스터하면 정적 데이터 저장소가 살아있는 자체 유지 관리 시스템으로 변모됩니다.