To Loop, To Query or something else

[Perdo123]

Hi,


I have just made my first database. It is based on an existing excel spreadsheet. I was able to get this far by solving any problems through looking at this forum.

The database is for recording tests made on 10 different products that are produced in batches of approx 30-35 tonnes.

What I want to achieve is this, when the test results for one product are being entered on the input form, count the quantity for that product already stored plus the quantity being entered and if the total is >= 200, highlight the quantity on the form.

The surplus over 200 will be the beginning of the next total.

[Perdo123]

Hi,

I have attempted to write a loop.

Sub makealoop()
Dim rs As DAO.Recordset
Dim db As Database
Dim ToTtonnes As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTonnesTest")
With rs
ToTtonnes = 0
If rs.EOF = False And rs.BOF = False Then
rs.MoveFirst

Do Until .EOF
ToTtonnes = ToTtonnes + !tonnes
If ToTtonnes >= 200 Then
.Edit
!Batch = -1
.Update
ToTtonnes = ToTtonnes - 200
End If
rs.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
ToTtonnes = 0
End Sub

I created a Table to test this on. I had 7 records in the table when I first tested it. I then added 3 more records and the code now starts on the 8th record and not the first.

Could someone explain this behaviour please.

Thanks

Peter

[orange]

There is no specific order to a table. Please show the records and tell us more about starting on the 8th and not the first.
Where did you add the records? Do you have a routine that you can show? Do the records have identifiers? Can you do some debug.prints to show the record identifiers as they are processed?

[Perdo123]

Hi Orange,

Please excuse the ignorance but how do I attach a database to the reply.

Thanks

[Perdo123]

Hi Orange,

Here is a copy of the table. I want the Loop to count the tonnage for each product seperately.

Thanks

Peter

[orange]

Please make an mdb version and post that.

[Perdo123]

Here is the mdb version

[orange]

Here is a small test to show the order of the records.

Sub makealoop2()
 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Dim ToTtonnes As Integer
 Dim i As Integer
 Set db = CurrentDb
 Set rs = db.OpenRecordset("tblTonnesTest")
 With rs

Do While Not .EOF
    i = i + 1
    'show the order of the records
    Debug.Print i & "  " & !Products & "  " & !Tonnes
rs.MoveNext
Loop
   .Close
End With
 Set rs = Nothing
End Sub

and here are the 13 records

1 cat 70 60
2 cat 50 50
3 cat 45 40
4 cat 45 135
5 cat 70 50
6 cat 70 55
7 cat 70 55
8 cat 45 100
9 cat 70 50
10 cat 50 50
11 cat 45 120
12 cat 70 50
13 cat 45 60

Where did this come from

and the code now starts on the 8th record and not the first.

[Perdo123]

Hi Orange,

The code starting on the 8th record occured when I had the Table in my original database, since I copied it to

the "LoopyDatabase" it is working as I would have expected, i.e. starting on the first record.

I now need to amend my code so that one product is totaled.

If product cat 70 was totaled then: records 1+5+6+7 = 220, as the total exceeds 200, the Batch for record 7 would be checked and the surplus of 20 would be carried forward to the next Total.

Would you be able to help me with this.

Thanks

Peter

[orange]

Sql to sum the tonnes and group by Product with a count of the number of (200 tonne) batches

SELECT tblTonnesTest.Products
, Sum(tblTonnesTest.Tonnes) AS SumOfTonnes
, Sum(([Tonnes]/200)) AS NumberOfBatches
FROM tblTonnesTest
GROUP BY tblTonnesTest.Products

;

Products	SumOfTonnes	NumberOfBatches
cat 45	455	2.275
cat 50	100	0.5
cat 70	320	1.6

[Perdo123]

Hi Orange,Many thanks for your reply,I got my code to work by first querying my table and then running the code.I used the code on my original table and the Tonnes field has the following settings.Field Size: DoubleFormat: FixedDecimal Places: 2I mention these settings as the code ignored the decimal values when adding up, this is not a major issue on this project but could you explain this behaviour.ThanksPeter

[orange]

?? I used your 2003 version, with no changes to the table. I created makealoop2 and the sql I showed above.
No idea why it isn't working same for you.

[Perdo123]

Thanks for trying and taking the time to reply

Peter