36. 데이터 핸들링의 시작 & 재고를 살펴보자.

 

 

 

엑세스따라하기36.accdb

1.77MB

 

36강을 올리기 전에 (예고편?) 글을 올립니다.

우선은 쿼리로 만드는 재고리스트를 생성해보고 그 뒤에 아래에 보여드리는 코드로 같은 리스트를 만들어 보겠습니다.

 

 

우리가 엑셀에서 DB로 넘어가는 가장 큰 이유는 입력의 간편성과 데이터로 통계를 비롯한 다양한 결과를 산출하기 위한 이유가 있습니다.

 

35강까지는 입력의 간편성을 위한 코드들로 이루어져 있고 36강 부터는 다른 영역인 데이터를 이용해서 통계를 비롯한 데이터 핸들링의 예시를 섞어 가려고 합니다. DB를 핸들링 할때 가장 쉬운 방법은 쿼리를 이용하는 방법입니다. 쿼리개체를 잘 사용하면 매우 간결하면서도 쉽게 핸들링이 됩니다만, 그렇지 못한경우는 거미줄같이 얽혀있는 쿼리에서 헤어나지 못할수도 있답니다. 쿼리에 쿼리를 덮어쓰는 형식을 사용할때 중간에 변경되는것이 하나가 있다면 연쇄적으로 오류가 나오기 시작하는데 그 부분이 저는 감당하기 어려워서, 가능하면 쿼리 개체를 사용하지 않습니다. 하지만 정리잘하시고 기억을 잘하시는 분들은 매우 쉽고 유용하게 사용할수 있습니다.

 

 

위의 그림을 보면 좌측에는 내가 구매할 물건을 만드는 회사, 우측에는 내 물건을 구매해주는 고객들입니다.

우리는 좌측의 데이터를 모아서 "t_구매"라는 테이블에 데이터를 쌓았고, 우측의 데이터를 모아서 "t_거래장" 과 "t_거래입력"이라는 테이블에 데이터를 쌓았습니다.

 

이제 이 3개의 테이블중 "t_구매"와 "t_거래입력"이라는 2개의 테이블의 데이터를 가지고 데이터 핸들링을 시작합니다. 그림을 그려서 올린 이유는 데이터의 이동과 데이터의 변화를 이런식으로 머릿속에 그리기 시작하시면 데이터 핸들링을 하시는데 한결 수월하게 접근하실수 있기에 예시를 보여드립니다.

 

 

재고

가장 간단하게 생각해보면 재고는 위의 그림과 같이 "구매한 각 아이템 갯수의 합 - 판매한 각 아이템 객수의 합" 으로 이루어져 있습니다. 위의 그림에서 가운데 줄을 보시면 "구매의 합계 - 판매의 합계 = 재고"라는걸 어렴풋 짐작하실수 있으실겁니다. 

 

 

보정

그런데말입니다~! 사용자 친화적인 데이터 베이스 핸들링이 되기위해서는 인간의 실수나 예상치 못한 변수에 대비를 해주는게 좋습니다.

 

이유는 다음과 같습니다. 첫번째 개발자는 일어날수 있는 상황들을 변수로 만들어 그 변수를 프로그램에 담습니다. 하지만 개발자는 신이 아니라는 점을 기억해야합니다. 나름 가능한 디테일하게 프로그램을 만들어도 막상 사용할때 놓치는 부분이 생기기 마련입니다.

 

두번째는 인간 사용하는 것이기 때문에 실수할 수 있는 부분을 보환할수 있는 룸을 하나 만들어두는것이 유용할 때가 있습니다.

 

기계적으로 생각한다면 재고는 "입고 - 출고 = 재고" 그 이상의 것이 필요없지요.

 

Q.

1. 가령 업을 시작한지 10년이 넘었고, 초기 입출고 수량에 대한 거래기록이 없다면??

2. 그간 거래한 업체들중 출고 내역이 일부가 없다면??

 

재고를 만드는데 가장 큰 영향을 미치는 두 부분입니다.

이 부분을 가장 기초적으로 보완할수 있는 것이, 바로 '초기재고'에 대한 개념과 '보정'이라는 개념입니다.

 

"재고 = 초기재고 + 입고 - 출고 + 보정" 으로 개념을 잡아보시길 권합니다. ^^

 

재고의 종류

수치상으로 재고를 나타내는데, 결과를 볼수 있는 재고들은 현재고, 검색일자에 따른 재고, 누계 등등 각기 다른 방법으로 만드는 재고들이 있습니다.

 

그 중 가장 기본이 되는 현재고에 대해서 이번회에는 진행을 해보겠습니다.

 

 

----------------------------------------------------------------------------------------(이론 끝)

 

 

재고 부분 코딩하기

 

이제 프로그램을

'이벤트 순서' 즉 '내가 컨트롤하는 순서'에 까지 생각을 해주면서 만들어 가야할 시점이 왔습니다.

 

35강까지는 무턱대고 컴퓨터가 알아서 해주는 연산을 코딩했지만,

이번화 부터는 각 이벤트를 타이밍을 직접 컨트롤 하는 경험을 하게됩니다. 차후 만드실 프로그램에서는 이 부분까지 디테일하게 짚고 넘어가셔야, 프로그램이 민첩하게 작동할수 있습니다.

 

쿼리로 만들기

가지고 있는 테이블을 이용해서 쿼리로 재고를 구해보겠습니다. 쿼리를 짜는것도 다양하지만, 가장 쉬운 난이도로 진행해볼께요. 우선 발주합계와 판매합계를 쿼리로 만듭니다. 아이템ID별 합계를 각각 구해줍시다.

 

- 발주합계 쿼리와 결과

 

- 판매합계 쿼리와 결과

- 쿼리를 재료를 사용해서 다시 쿼리로 재고 구하기

이런식으로 쿼리로 재고를 구할수 있습니다. 직관적으로 쉽게 이용할수 있는  장점이 있지만, 뭔가 하나를 추가할때 기존쿼리와 맞지 않는 부분이 생긴다면, 추가로 쿼리를 생성해서 연결해줘야 하는 단점이 생기기도 합니다.

 

"개인적"으로 쿼리와 연결조인을 사용하지 않는 이유는

개체가 많아짐에 따라서 생각해줘야 할 것들이 많아 지기 때문입니다. (나쁘다는게 아닙니다. ) 

 

이제 쿼리를 사용하지 않는 다른 방법을 사용해보겠습니다. 

테이블 만들기

 

재고 테이블에 필드를 우선 생성합니다.

 

(완성된 테이블을 보여드립니다. 이 데이터들은 모든 코드를 구현하고 얻은 값입니다.)

우선 테이블을 실행시키면 구조만있고 내용은 비어 있는 위와 같은 테이블을 보실 수 있으실 겁니다.

 

폼 만들기

폼에 필요한 오브젝트는 아래와 같습니다.

 

- 텍스트필드: 아이템ID, 초기재고, 보정수량

- 버튼: 저장, 재고확인

- 리스트: 재고리스트

 

일단 위의 오브젝트들을 생성해서 넣어주세요.

(레이블에 있는 이름대로 텍스트박스 이름을 지정해줬습니다.)

재고폼은 두개의 파트로 나뉩니다. 하나는 초기재고와 보정수량을 저장/수정하는 파트, 그리고 계산해서 보여주는 파트로 나뉩니다.

 

우선 초기재고와 보정수량은 현재 아이템리스트에 아무것도 구현을 안했기 때문에 사용할수 없으므로, 가장 마지막으로 미뤄두고 재고계산하는 버튼부터 코딩을 시작해보겠습니다.

 

 재고 확인 버튼에 클릭이벤트에 코딩을 시작합니다.

VBA editor가 열리고,

 

Private Sub 재고확인_Click()

'이곳에 코드를 넣을 것입니다.

End sub

 

우선 테이블 삭제하는 코드를 넣습니다.

'재고테이블 삭제
CurrentDb.Execute "Delete * from t_재고", dbSeeChanges

 

이 명령을 실행하면 테이블이 delete됩니다. 테이블을 열어놓고 삭제를 실행시키면 다음과 같은 화면을 보실수도 있습니다.

'아이템 순서대로 넣기

Dim RS As DAO.Recordset, RS1 As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("select * from t_아이템정보", dbOpenDynaset, dbSeeChanges)

If RS.RecordCount Then
RS.MoveFirst
Do

CurrentDb.Execute "insert into t_재고(아이템ID,모델코드,초기재고,보정수량) values(" & RS!ID & ",'" & RS!모델코드 & "'," & Nz(RS!초기재고, 0) & "," & Nz(RS!보정수량, 0) & ")", dbSeeChanges
RS.MoveNext

Loop Until RS.EOF

End If

RS.Close

set RS = Nothing

 

이렇게 실행하고 테이블을 보겠습니다.

이 뒤에는 반복계산을 해서 테이블에 계산 값을 넣는 코드를 넣겠습니다.

'반복해서 계산해서 넣기

Set RS1 = CurrentDb.OpenRecordset("select * from t_재고", dbOpenDynaset, dbSeeChanges)

If RS1.RecordCount Then
RS1.MoveFirst
Do

RS1.Edit

RS1!초기재고 = Nz(DLookup("초기재고", "t_아이템정보", "ID = " & RS1!아이템ID & ""), 0)
RS1!보정수량 = Nz(DLookup("보정수량", "t_아이템정보", "ID = " & RS1!아이템ID & ""), 0)

RS1!구매수량 = DSum("수량", "t_발주", "아이템ID = " & RS1!아이템ID & "")
RS1!판매수량 = DSum("수량", "t_거래입력", "아이템ID = " & RS1!아이템ID & "")

RS1!현재고 = RS1!초기재고 + RS1!구매수량 - RS1!판매수량 + RS1!보정수량

RS1.Update

RS1.MoveNext

Loop Until RS1.EOF

End If

RS1.Close

Set RS1 = Nothing

 

 

초기재고와 보정수량은 t_재고가 아닌, t_아이템정보 테이블에 저장을 해주고 데이터를 불러오는 형식으로 진행합니다.

 

35회동안 해온 저장/수정으로 마무리를 해서 현재고 파악하는 버튼을 실행시켜 보시기 바랍니다.

 

 

---------------------------------------------------------------------------------(코드 끝)

 

추가로 위와 같은 코드는 추후에 저장프로시저로 구현을 하게되면 훨씬 빠른 연산이 가능해집니다.