38. 현재고 작성하기(+전월이월계산) "만능레시피"

 

https://youtu.be/jBMRRbuy4ME

월별 재고 확인하는 폼 만들기

크게 2가지를 고민해봅니다.

첫 번째는 사용자 입장에서 사용시 몇번의 컨트롤로 원하는 리스트를 출력하는가?

두 번째는 개발자 입장에서 원하는 결과를 위해서 어떻게 코드를 짜는가?

 

1) 폼 컨트롤 횟수 고민 (몇번 클릭을 할까?)

아래의 그림처럼 컨트롤을 두가지로 나눠서 사용할 수 있습니다.

둘다 결국엔 시작일과 종료일 그리고 이전 말일 이라는 3가지 날짜를 추출하는게 목적입니다.

 

첫번째 텍스트 박스는 월중 아무 날짜나 찍으면 3가지 날짜를 구하는 형식이고

사용자가 편하긴 하지만, 처음 이걸 보는 사람에게는 무엇을 요구하는지 두번째 콤보 상자에 비해서 전달력이 확실히 떨어지지요.

 

두번째 콤보상자는 년과 월을 선택하면, 3가지의 날짜를 구해주는 방법입니다.

사용자가 직관적으로 어떤의미 인지 알아차리기는 좋지만, 매번 두번씩 컨트롤을 해줘야한다는 단점이 있습니다.

 

타인이 사용하는 컨트롤이라면 두번째 컨트롤로 가는게 맞는것 같고, 만약 개발자=사용자라면 저는 첫번째가 조금 더 편할것 같습니다. 손한번 덜가게 만드는게 저에게는 더 매력적인것 같네요.

 

 

 

 

2) 코드에 대한 고민(어떻게 구현할것인가?)

 

(산문모드 시작-------------------------------------)

원하는 결과부터 역순으로 찾아가보자. 

내가 원하는 결과를 먼저 구체화 해보는것이 중요하다고 생각합니다. 내가 원하는것이 정확하게 무엇인지 모를때가 많습니다. 제가 까페에 질문 글을 올릴때 가끔 느꼈던 점이 있습니다. 내가 원하는게 뭔지 알고 있다고 생각하면서 글을 올리는데, 그걸 만약에 코드는 차치하고라도 숫자로만으로도 엑셀상에서 구현을 못한다면, 분명 제대로 알지 못하고 바라는 것만 있는 상황입니다. 마치 "사막의 고래"같은 느낌을 찾는 것이지요.  

 

"코드실행을 해보면 코드 100개중 1개만 틀려도 잘못된 코드라고 합니다."

 

새로운 코드를 짜실때,

가능하다면 예제파일을 만들어보는것을 권합니다.

그게 안된다면 엑셀에서 구현해봅시다.

그게 안된다면 엑셀에 숫자만이라도 넣어봅시다.

(산문모드 끝-------------------------------------)

 

아래 그림을 보면 제가 원하는 리스트가 있습니다. 엑셀에서 우선 구현해봤구요.

저걸 구현하기 위해서 어떤 재료가 필요하고 그건 어떻게 구하는지 알아봐야할것 같습니다.

 

현재고 = 초기재고+전월이월+당월입고+당월출고+보정수량

 

으로 공식이 하나 나왔네요. 

 

 

 

아이템 코드와 초기재고 보정수량을 insert or add 해줍니다.

 

 

다음 전월까지의 합계를 각각 loop로 구해줍니다.  그리고 그 결과를 edit을 하던지 update를 해줍니다.

 

 

당월 입고와 당월 출고를 구해주고 그걸 기준으로 현재고를 구해준후 값을 update or edit 해줍니다.

 

 

 

그리고 리스트를 보여주면 됩니다. 

 

위의 그림과 같이 설계를 했으니 코드를 뽑아 보겠습니다.

 

폼에 "검색일" 이라는 텍스트 박스를 하나 만들어주고,  "검색"이라는 버튼을 하나 만들어줍니다.

 

 

 

검색일 텍스트 박스의 형식은 간단한 날짜로 만들어줍니다.

 

 

 

 

검색버튼에 클릭이벤트를 넣어주고 코딩을 시작합니다.

 

 

이하 코드는 파란색으로, 주석은 VBA와 같이 녹색으로 작성하겠습니다.

 

1) 검색일 error 제어

If Nz(검색일, "") = "" Then MsgBox "검색일을 넣으세요" Exit Sub End If

 

검색일이 없는 상태에서 코드를 진행하면 에러가 발생되는 코드입니다. 

검색일이 없을 경우에 코드를 나가게 해서 에러를 방지할수도 있지만

여기서 한가지 더 생각해볼때, 귀차니즘을 극복하는 용도로 사용합니다.

날짜가 비었을때는 오늘을 기준으로 검색을 실행시키는 코드로 바꿔줍니다.

 

'If Nz(검색일, "") = "" Then 'MsgBox "검색일을 넣으세요" 'Exit Sub 'End If If Nz(검색일, "") = "" Then Me.검색일 = Date End If

 

이전 강의에서 보여준 패턴을 여기서도 이용합니다.

2) 't_재고_월' 이라는 테이블을 delete 해주고,

 

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

 

3개의 날짜를 구해줍니다.

선택한 날짜기준의 월 1일과 말일 그리고 전월 말일.

 

3) 3개의 날짜 구하기

참고링크: cafe.naver.com/xpath/311

 

 

 

1) 선택 이전월 말일

BLday = DateSerial(Year(검색일), Month(검색일), 0)

2) 선택월 말일

TLday = DateSerial(Year(검색일), Month(검색일) + 1, 0)

3) 선택월 1일

TFday = DateSerial(Year(검색일), Month(검색일), 1)

 

위의 검색일은 me.검색일 이며 텍스트박스 오브젝트 입니다.

 

 

위 그림 하단에 직접실행 창을 보시면

debug.print로 날짜들이 나온것을 보실 수 있습니다.

'3개의 날짜 구하기 Dim BLday As Date, TLday As Date, TFday As Date BLday = DateSerial(Year(검색일), Month(검색일), 0) TLday = DateSerial(Year(검색일), Month(검색일) + 1, 0) TFday = DateSerial(Year(검색일), Month(검색일), 1) Debug.Print  BLday, TLday, TFday

 

4) 테이블에 아이템코드와 아이템ID를 넣기 + 초기재고

'아이템순서대로 넣기 Dim RS As DAO.Recordset, RS1 As DAO.Recordset '레코드셋은 필요에 따라 증가시켜도 무방합니다. Set RS = CurrentDb.OpenRecordset("select * from t_아이템정보", dbOpenDynaset, dbSeeChanges) 'set RS : 레코드셋 정의 If RS.RecordCount Then      RS.MoveFirst    Do    CurrentDb.Execute "insert into t_재고_월(아이템ID,모델코드) values(" & RS!ID & ",'" & RS!모델코드 & "')", dbSeeChanges    RS.MoveNext    Loop Until RS.EOF End If RS.Close set RS = Nothing '레코트셋 비움

 

이 테이블의 위의 코드를 실행시키면

 

 

이런식으로 채워집니다.

 

 

 

구매수량, 초기재고, 판매수량, 현재고, 보정수량 중 날짜에 영향을 안받는 필드를 찾아봅니다.

 

제가 만든 프로그램에서의

초기재고의 설계는 날짜에 영향을 안받습니다. (설계에 따라서 달라지겠죠?)

나머지는 날짜의 영향을 받네요.

 

그럼 코드를 초기재고까지 들어가도록 변경하겠습니다.

'아이템순서대로 넣기 Dim RS As DAO.Recordset, RS1 As DAO.Recordset '레코드셋은 필요에 따라 증가시켜도 무방합니다. Set RS = CurrentDb.OpenRecordset("select * from t_아이템정보", dbOpenDynaset, dbSeeChanges) 'set RS : 레코드셋 정의 If RS.RecordCount Then      RS.MoveFirst    Do    CurrentDb.Execute "insert into t_재고_월(아이템ID, 모델코드, 초기재고) values(" & RS!ID & ",'" & RS!모델코드 & "'," & nz(RS!초기재고,0) & ")", dbSeeChanges    RS.MoveNext    Loop Until RS.EOF End If RS.Close set RS = Nothing '레코트셋 비움

숫자형을 요구하는데 null을 반환하는 경우 에러가 발생할수 있으므로 nz(데이터,0) NZ함수를 써줍니다.

 

다시 결과를 보겠습니다.

 

 

5) 날짜구간에 따른 구매수량 판매수량 보정수량 구하기 + 현재고 + 전월 이월

 

테이블 모습을 보여드리겠습니다.

[t_재고_전월] 을 생성했습니다. 스크린샷은 제가 테스트한 그림이고, 아래와 같이 [필드]를 생성하시면 됩니다.

 

 

[t_재고_월] 테이블에 필드 [전월이월]을 추가했습니다.

 

 

 

현재고를 구하기까지의 순서를 먼저 적겠습니다. (위에서 했던 부분에 중복 및 수정되는 부분도 있습니다.)

 

1) [t_재고_월] 과 [t_재고_전월]  Delete

2) [t_재고_월] 과 [t_재고_전월]에 아이템순서대로 넣기

3) 전월까지의 발주합계를 구한다.

4) 전월까지의 판매합계를 구한다.

5) 전월이월을 구한다.

6) 당월 발주합계를 구한다.

7) 당월 판매합계를 구한다.

8) 현재고를 구한다.

 

의 단계로 진행됩니다.

 

 

 

1) [t_재고_월] 과 [t_재고_전월]  Delete : 테이블 초기화 개념, 이 두 테이블은 계산한 정보를 적어두는 장소로 사용한다. 

'[t_재고_월] 과 [t_재고_전월]  Delete CurrentDb.Execute "Delete * from t_재고_월", dbSeeChanges CurrentDb.Execute "Delete * from t_재고_전월", dbSeeChanges

실행 결과 이미지 (테이블 내부에서 이뤄지는 코드 결과)

 

 

2) [t_재고_월] 과 [t_재고_전월]에 아이템순서대로 넣기

'아이템순서대로 넣기 Dim RS As DAO.Recordset, RS1 As DAO.Recordset, RS2 As DAO.Recordset, RS3 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) & ")", dbSeeChanges CurrentDb.Execute "insert into t_재고_전월(아이템ID,모델코드,초기재고) values(" & RS!ID & ",'" & RS!모델코드 & "'," & Nz(RS!초기재고, 0) & ")", dbSeeChanges RS.MoveNext Loop Until RS.EOF End If RS.Close Set RS = Nothing

실행 결과 이미지 (테이블 내부에서 이뤄지는 코드 결과)

 

 

알아두면 좋은 함수

1)iif(A=B,yes,no)

해석) 만약 A=B 이라면 yes에 값을, 아니면 no의 값을 출력한다.

예제1) iif( 3=2, 1, 0 )

결과값 0

예제2) iif( 2=2, 1, 0 )

결과값 1

 

2)X Between A and B 

X between A and B 는 X >= A And X <= B

X가 A와 B 사잇값.

 

 

 

3) 전월까지의 발주합계를 구한다.

4) 전월까지의 판매합계를 구한다.

 

*이쯤에서 궁금할만한 "레코드셋을 눈으로 확인하기"

쿼리작성을 하시면 내가 만든 레코드셋을 눈으로 확인할수 있습니다.

'1) 전월의마지막날과 같거나 작은 날의 합계를 구한다. (전월이월) '전월이월 = 초기재고 + 구매수량(전월) - 판매수량(전월까지) Set RS = CurrentDb.OpenRecordset("select 아이템ID, Sum(수량) as 수량합 from t_발주 " & _ "WHERE IIf([날짜]<=#" & BLday & "#,1,0)=1 GROUP BY 아이템ID, IIf([날짜]<=#" & BLday & "#,1,0) ", dbOpenDynaset, dbSeeChanges) 2020-5-10일자 기준 쿼리생성결과(위의 코드 굵은 글자를 쿼리로) SQL Tip: 위의 코드에서 #" & BLday & "#를 사용했는데, 아래의 SQL 코드는 날짜를 직접 넣었습니다.  이유:  쿼리와 폼안에 귀속된 변수인 BLday는 연결고리가 없습니다. 고로 변수를 공용변수로 만들어서 테스트 할수도 있고, 아니라면 날짜형의 예를 넣어서 테스트를 해보는 방법이 있습니다. 이후, 날짜형을 변수로 변경해주면 됩니다. select 아이템ID, Sum(수량) as 수량합 from t_발주 WHERE IIf([날짜]<=#2020-05-10#,1,0)=1 GROUP BY 아이템ID, IIf([날짜]<=#2020-05-10#,1,0)  디자인보기 역으로 쿼리를 만들고 레코드셋을 만들때 Set RS1 = CurrentDb.OpenRecordset("select 아이템ID, Sum(수량) as 수량합 from t_거래입력 " & _ "WHERE IIf([날짜]<=#" & BLday & "#,1,0)=1 GROUP BY 아이템ID, IIf([날짜]<=#" & BLday & "#,1,0) ", dbOpenDynaset, dbSeeChanges)

5) 전월이월을 구한다.

'1)전월이월(누적) 1-1 전월까지의 발주합계 If RS.RecordCount Then RS.MoveFirst Do CurrentDb.Execute "UPDATE t_재고_전월 set 구매수량 = " & Nz(RS!수량합, 0) & " WHERE 아이템ID = " & RS!아이템ID & "", dbSeeChanges RS.MoveNext Loop Until RS.EOF End If RS.Close Set RS = Nothing If RS1.RecordCount Then RS1.MoveFirst Do CurrentDb.Execute "UPDATE t_재고_전월 set 판매수량 = " & Nz(RS1!수량합, 0) & " WHERE 아이템ID = " & RS1!아이템ID & "", dbSeeChanges RS1.MoveNext Loop Until RS.EOF End If RS.Close Set RS = Nothing '전월이월계산 Set RS = CurrentDb.OpenRecordset("select * from t_재고_전월", dbOpenDynaset, dbSeeChanges) If RS.RecordCount Then RS.MoveFirst Do CurrentDb.Execute "UPDATE t_재고_전월 set 구매수량 = " & Nz(RS!수량합, 0) & " WHERE 아이템ID = " & RS!아이템ID & "", dbSeeChangesCurrentDb.Execute "UPDATE t_재고_월 set 전월이월 = " & RS!초기재고 + RS!구매수량 - RS!판매수량 & " WHERE 아이템ID = " & RS!아이템ID & "", dbSeeChanges RS.MoveNext Loop Until RS.EOF End If RS.Close Set RS = Nothing

실행 결과 이미지 (테이블 내부에서 이뤄지는 코드 결과: 틈틈히 업데이트 하느냐고, 매번 선택 날짜가 달라서 필드안의 합계숫자들이 다를수 있지만 개의치 마시고, 어떤 필드에 숫자들이 채워지는 지에 대해서만 신경을 쓰고 봐주세요~ )

 

 

 

6) 당월 발주합계를 구한다.

7) 당월 판매합계를 구한다.

'2) 당월의 첫날 부터 마지막 날까지의 합계를 구한다.(구매수량) Set RS1 = CurrentDb.OpenRecordset("select 아이템ID, Sum(수량) as 선택월수량합 from t_발주 " & _ "WHERE IIf([날짜]<=#" & TLday & "# AND [날짜]>=#" & TFday & "#,1,0)=1 GROUP BY 아이템ID, IIf([날짜]<=#" & TLday & "# AND [날짜]>=#" & TFday & "#,1,0) ", dbOpenDynaset, dbSeeChanges) '3) 당월의 첫날 부터 마지막 날까지의 합계를 구한다.(판매수량) Set RS2 = CurrentDb.OpenRecordset("select 아이템ID, Sum(수량) as 선택월수량합2 from t_거래입력 " & _ "WHERE IIf([날짜]<=#" & TLday & "# AND [날짜]>=#" & TFday & "#,1,0)=1 GROUP BY 아이템ID, IIf([날짜]<=#" & TLday & "# AND [날짜]>=#" & TFday & "#,1,0) ", dbOpenDynaset, dbSeeChanges) '4) 보정수량 Set RS3 = CurrentDb.OpenRecordset("select 아이템ID, Sum(보정수량) as 보정 from t_재고_보정 " & _ "WHERE IIf([날짜]<=#" & BLday & "#,1,0)=1 GROUP BY 아이템ID, IIf([날짜]<=#" & BLday & "#,1,0) ", dbOpenDynaset, dbSeeChanges)

8) 현재고를 구한다.

'당월 데이터 '2)구매수량(이달의 수량) If RS1.RecordCount Then RS1.MoveFirst Do CurrentDb.Execute "UPDATE t_재고_월 set 구매수량 = " & Nz(RS1!선택월수량합, 0) & " WHERE 아이템ID = " & RS1!아이템ID & "", dbSeeChanges RS1.MoveNext Loop Until RS1.EOF End If RS1.Close Set RS1 = Nothing '3)판매수량(이달의 수량) If RS2.RecordCount Then RS2.MoveFirst Do CurrentDb.Execute "UPDATE t_재고_월 set 판매수량 = " & Nz(RS2!선택월수량합2, 0) & " WHERE 아이템ID = " & RS2!아이템ID & "", dbSeeChanges RS2.MoveNext Loop Until RS2.EOF End If RS2.Close Set RS2 = Nothing '4)보정수량(누적:이달까지) If RS3.RecordCount Then RS3.MoveFirst Do CurrentDb.Execute "UPDATE t_재고_월 set 보정수량 = " & Nz(RS3!보정, 0) & " WHERE 아이템ID = " & RS3!아이템ID & "", dbSeeChanges RS3.MoveNext Loop Until RS3.EOF End If RS3.Close Set RS3 = Nothing '5)현재고(현시점 재고) = 전월이월 + 구매수량 -판매수량 + 보정수량 Set RS = CurrentDb.OpenRecordset("select * from t_재고_월", dbOpenDynaset, dbSeeChanges) If RS.RecordCount Then RS.MoveFirst Do RS.Edit RS!현재고 = RS!전월이월 + RS!구매수량 - RS!판매수량 + RS!보정수량 RS.Update RS.MoveNext Loop Until RS.EOF End If RS.Close Set RS = Nothing

리스트 만들어 보기

 

 

드디어 재료가 완성됐군요. 리스트를 하나 만들고 이름을 검색리스트라고 짓겠습니다.

불러올 테이블은 [t_재고_월]이라는 테이블이고 여기서 리쿼리를 시켜주는 코드를 넣어주면 됩니다.

 

 

 

날짜에 따라서 재고가 바뀌는걸 보실 수 있습니다.

 

 

 

드디어 검색일에 따른 현재고를 구하는 리스트를 완성했습니다.

 

이와 같은 방법으로 재고 및 결재내역, 구매내역을 월별로 끊어서 내 기록들을 확인하실수 있습니다.

 

이걸 영상으로 담으려니 엄청 막막하네요. 이 내용을 어떻게 하면 이해하기 쉽게 영상으로 만들수 있을까요?;;;; 후덜덜합니다.

 

이후에 다시 코드 쪼개기를 한다면, 설정한 시작월에서 종료월까지의 누계가 있는 레포트만드는 게 있을겁니다.

그것도 시간이 나는 대로 틈틈히 만들어 보겠습니다.