1. Registered User
Join Date
Dec 2004
Posts
10

Dear All,

We have two tables called OLDDAT and NEWDATas follows :-

OLDDAT
SECTION - ITEM NUMBER
JOB - JOB NUMBER VARCHAR
MARK - ITS LIKE A ITEM DESCRIPTION VARCHAR
QTY - NUMBER OF QTY IN PIECES
WT - TOTAL WEIGHT OF ALL THE PIECES
LENGTH - LENGTH OF THAT MARK

NEWDAT
SNO - AUTONUMBER ( FOR GETTING UNIQUE SERIAL NUMBER FOR BOX)
SECTION - ITEM -NUMBER
JOB - JOB NUMBER -VARCHAR
MARK - ITS LIKE A ITEM DESCRIPTION - VARCHAR
QTY - NUMBER OF QTY IN PIECES -NUMBER
WT - TOTAL WEIGHT OF ALL THE PIECES - NUMBER
LENGTH - LENGTH OF THE MARK - NUMBER
NOB - NUMBER OF BOXES -NUMBER
BOXQTY - NUMBER OF PIECES INSIDE EACH BOX BASED - NUMBER

There are different lengths for each and every item and based on the lengths we need to distribute the quantity into BOXES
I.E
CONDITION
IF LENGTH <= 390
THEN WT/1000
ELSE WT/2000
THIS WT/1000 OR WT/2000 WILL GIVE NUMBER OF BOXES EG.NOB
AND BASED ON NUMBER OF BOXES U WILL DISTRIBUTE QTY
LIKE QTY/NOB WILL GIVE NO OF QTY IN EACH BOX AND EACH BOX MUST BE INSERTED INTO NEWDAT TABLE

lets say eg
OLDDAT
section : A
JOB :A
MARK :ABC
QTY :500
LENGTH :380
WT:6000

NEWDAT WILL CONTAIN

SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33
SECTION = A ,JOB =A, MARK=ABC, QTY=500, LENGTH=380,WT=6000 BOXQTY=83.33

AND I HAVE DONE THIS USING FOLLOWING CODE :-

Private Sub Command0_Click()
Dim dbs As Database
Dim rec As Recordset, Recnew As Recordset, recme As Recordset
Dim Box As Integer, Twt As Integer, Ebox As Double, i As Integer
Dim intdone As Boolean

Set dbs = CurrentDb

Set recme = dbs.OpenRecordset("Select Mark,Job,Length,Wt,rqty from olddat;")
recme.MoveFirst
Do Until recme.EOF
If recme.Fields(2) < 390 Then
Box = recme.Fields(3) / 1000
Ebox = recme.Fields(4) / Box
ElseIf recme.Fields(2) > 390 Then
Box = recme.Fields(3) / 2000
Ebox = recme.Fields(4) / Box
End If
Set Recnew = dbs.OpenRecordset("newdat")
i = 1
intdone = False
Do Until intdone = True
If i > Box Then
Debug.Print " Comple"
intdone = True
Else
i = i + 1
With Recnew
!Mark = recme.Fields(0)
!Job = recme.Fields(1)
!Length = recme.Fields(2)
!Wt = recme.Fields(3)
!rqty = recme.Fields(4)
!Box = Box
!Ebox = Ebox
.Update

End With
End If
Loop
recme.MoveNext
Loop

dbs.Close

End Sub

My problem is inside each box there must be round quantity like each box must contain 83 instead of 83.33 as it is in

pieces and when i round it i get the sum of qty of all boxes in newdat as 498 which is less than 500 of olddat in this way i

am losing 2 pieces which is wrong and to avoid this i need a way in which each box must contain round qty like 83 and

the remaining qty of 2 pieces must be inserted into newdat as 7'th extra box with this 2 pieces .

Best Regards

Arif

2. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
Hi

After you finished your loop that creates the first "full" records you need to work out your remainder:

RemainingQty=recme.Fields(4)-(int(recme.Fields(4)/Box)*Box)
' where Box = recme.Fields(3) / 1000 (from your code)

If RemainingQty<>0
'create another record using the RemainingQty
Endif

Chris
Last edited by howey; 12-22-04 at 05:44. Reason: I got the formula wrong !!!

3. Registered User
Join Date
Dec 2004
Posts
10

Howey can u jus append that in my code as i m struggling
with the syntax

Best Regards

Arif

4. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
Here's the updated code (I haven't tested it). I see your original code put stores "Box" in the record wher "Box" is the calculated number of full boxes. So in your example, all six records will have 6 as the Box value. The way I've written the code below, the 7th record will also have a Box value of 6. Do you need them all to be 7 or should they really be sequenced 1-7 ?

Code:
```Dim dbs As Database
Dim rec As Recordset, Recnew As Recordset, recme As Recordset
Dim Box As Integer, Twt As Integer, Ebox As Double, i As Integer
Dim intdone As Boolean
Dim remainder As Integer

Set dbs = CurrentDb

Set recme = dbs.OpenRecordset("Select Mark,Job,Length,Wt,rqty from olddat;")
recme.MoveFirst
Do Until recme.EOF
If recme.Fields(2) < 390 Then
Box = recme.Fields(3) / 1000
Ebox = recme.Fields(4) / Box
ElseIf recme.Fields(2) > 390 Then
Box = recme.Fields(3) / 2000
Ebox = recme.Fields(4) / Box
End If
remainder = recme.Fields(4) - (Int(recme.Fields(4) / Box) * Box)

Set Recnew = dbs.OpenRecordset("newdat")
i = 1
intdone = False

Do Until intdone = True
If i > Box Then
Debug.Print " Comple"
intdone = True
Else
i = i + 1
With Recnew
!Mark = recme.Fields(0)
!Job = recme.Fields(1)
!Length = recme.Fields(2)
!Wt = recme.Fields(3)
!rqty = recme.Fields(4)
!Box = Box
!Ebox = Ebox
.Update
End With
End If
If remainder <> 0 Then
With Recnew
!Mark = recme.Fields(0)
!Job = recme.Fields(1)
!Length = recme.Fields(2)
!Wt = recme.Fields(3)
!rqty = recme.Fields(4)
!Box = Box
!Ebox = remainder
.Update
End With
End If
Loop
recme.MoveNext
Loop

dbs.Close

End Sub```
regards
Chris

5. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
Just a quick thought for your remainder Formula

Code:
`remainder = recme.Fields(4) - (Int(recme.Fields(4) / Box) * Box)`
to
Code:
`remainder=recme.Fields(4) mod Box`
Just a thought
Dave

6. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
Code:
`remainder=recme.Fields(4) mod Box`
Good point. I must admit I didn't know mod was available in VB. I'm not that great on VB.

Chris

7. Registered User
Join Date
Dec 2004
Posts
10

## Dear Chris

HI chris

I need a 7th box and that 7 box must contain the remaining values.I am very thankfull for your help. Meanwhile i will test your code and check.

Regards

Arif

8. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
The 7th record will contain the remaining quantity. The question I am asking is what is the point on the "Box" field in your code. In your example it will store 6 as the value of Box for all six records. If there are going to be 7 records then shouldn't that value be 7 ?
Chris

9. Registered User
Join Date
Dec 2004
Posts
10

## Chris Thanks v much

HI chris

but there were some changes had to be made like the if condition given by you must come in outer range.I made the change and its very good.

thanks for all the support but there is one criteria left
the box number must be displayed as 7 in the seventh box i m pasting the recent revised code as follows :-

Private Sub Command0_Click()
Dim dbs As Database
Dim rec As Recordset, Recnew As Recordset, recme As Recordset
Dim Box As Integer, Twt As Integer, Ebox As Double, i As Integer
Dim intdone As Boolean
Dim remainder As Integer

Set dbs = CurrentDb

Set recme = dbs.OpenRecordset("Select Mark,Job,Length,Wt,rqty from olddat;")
recme.MoveFirst
Do Until recme.EOF
If recme.Fields(2) < 390 Then
Box = recme.Fields(3) / 1000
Ebox = recme.Fields(4) / Box
ElseIf recme.Fields(2) > 390 Then
Box = recme.Fields(3) / 2000
Ebox = recme.Fields(4) / Box
End If

remainder = recme.Fields(4) - (Int(recme.Fields(4) / Box) * Box)

Set Recnew = dbs.OpenRecordset("newdat")
i = 1
intdone = False
Do Until intdone = True
If i > Box Then
Debug.Print " Comple"
intdone = True
Else
i = i + 1
With Recnew
!Mark = recme.Fields(0)
!Job = recme.Fields(1)
!Length = recme.Fields(2)
!Wt = recme.Fields(3)
!rqty = recme.Fields(4)
!Box = Box
!Ebox = Ebox
.Update

End With
End If

Loop

If remainder <> 0 Then
With Recnew
!Mark = recme.Fields(0)
!Job = recme.Fields(1)
!Length = recme.Fields(2)
!Wt = recme.Fields(3)
!rqty = recme.Fields(4)
!Box = Box
!Ebox = remainder
.Update
End With

End If

recme.MoveNext
Loop

dbs.Close

End Sub