This is my understanding of your problem:
1) Product received is in units that is 1 unit = 30 dozen
2) Product Sold are in dozens that can be of various denominations ranging from 30 - 0.5
3) Stock at any point of time will be calculated in Dozens.
(I have not yet addressed the Sales returns issue which I will if my current assumptions and understanding of your problem is correct)
I have very limited information to your approach and I will try to demonstrate the solution with a very simple example:
I have three tables:
Table 1: tbl_in
ID (Auto Number PK), in_date (Date/Time), in_units (Number) (assumed 1 unit = 30 dozen)
Table 2: tbl_out
ID (Auto Number PK), in_date (Date/Time), in_units (Number) (assumed unit are in dozen)
Table 3: Table 1
ID (Auto Number PK), stock_date (Date/Time), stock_units (Number) (dozen)
I have a Form with two Text Boxes where I type my starting date and ending date. I have a command Button with the following Code:
Code:
Private Sub Command0_Click()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim myDate As Date
Dim myCounter As Integer
Dim mystockin As Integer
Dim mystockout As Integer
'Clears Data from the Table for Current Data
strSQL = "Delete * From Table1"
CurrentDb.Execute strSQL, dbFailOnError
myCounter = -1
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
Do While Not myDate = Me.Text3
myCounter = myCounter + 1
myDate = DateAdd("d", myCounter, Me.Text1)
mystockin = IIf(IsNull(DSum("[in_units]", "tbl_in", "[in_date]<=#" & myDate & "#")), 0, DSum("[in_units]", "tbl_in", "[in_date]<=#" & myDate & "#"))
mystockout = IIf(IsNull(DSum("[out_units]", "tbl_out", "[out_date]<=#" & myDate & "#")), 0, DSum("[out_units]", "tbl_out", "[out_date]<=#" & myDate & "#"))
rst.AddNew
rst!stock_date = myDate
'Assuming 1 unit received = 30 dozens
'Assuming Product Sold are recorded in dozens
rst!stock_units = (mystockin * 30) - (mystockout)
rst.Update
Loop
rst.Close
Set rst = Nothing
End Sub
What does the Code do:
1) Creates Dates Between the Start and the End Dates in Table Table 1 e.g. Starting Date 01/01/2013 End Date 15/01/2013 all dates in between are auto populated and the stock on that date is populated in the field stock_units.
rst!stock_units = (mystockin * 30) - (mystockout)
I have multiplied mystockin with 30 as 1 unit received = 30 dozens.
2) Code Clears old data and populates the table with current data each time you type the Starting Date and Ending Date and run the code.