Calculation result problem

[Misty]

I’m using MS Access 2000, SR-1.

The Data I have to use is in a linked, SQL7 database that I can only read from.

In my Access Database, I've created a query that pulls the following data from the SQL Database:

Query [Forcast_1] contains four fields.

[Jobnum]
[Planum]
[Interval]
[Counter]



Example:

Jobnum

1142
1142
1142

Planum

M-12
M-24
M-48

Interval

1
2
4

Counter

22
22
22

Problem:

To determine which Planum will be the correct one, I have to select the Planum with the HIGHEST Interval number that divides evenly, (no fractions), into the value in the Counter field. If no Interval number fits this criterion, I have to use whichever Planum has Interval number 1.

So far, I’ve only used Macros converted into VBA. I have two weeks to come up with something that will cycle through all of the [Jobnum] and calculate which of the [Planum] is the one to use for each of several hundred [Jobnum].

After I get the right [Planum] for each [Jobnum], there are a lot of Date related forcasting results I have to do also and I think I know how to do them, but first I have to get over this seemingly impossible calculation.

Can anyone please give me some advice at a VBA beginner level that will help me do this?

I’ve got so much to learn, sometimes I don’t understand how you guys all figured this stuff out!

TIA

M

 

[modest]

1) Is counter always the same number for all the rows?
2) VBA isn't really that difficult, once you learn the basics, the rest is just vocabulary and learning new "words" (or functions)

This won't be that hard.. a few minutes.

 

[Misty]

For each Jobnum, the counter will always be the same number for each record.

Did you say a few minutes? Gawd!

 

[Misty]

This is a sample of the Forcast Database

I'll remove this db in a day or two.

 

[Misty]

I've put a small sample of my data in the sample db area. It's named:

Planum.zip (31.2 KB)

 

[modest]

What do you want if two of the intervals are the same? Or is that not a factor?

 

[modest]

Public Function Example()
    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef
    Dim rs          As DAO.Recordset
    Dim rcd
    
    Dim intCurrent  As Integer
    Dim varInterval As Variant
    Dim varCounter  As Variant

  'set default values
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Forcast_1")
    Set rs = qdf.OpenRecordset()
    intCurrent = 0
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveLast
        rs.MoveFirst        
        
        Do Until rs.EOF
            varCounter = rs!Counter
            varInterval = rs!Interval

            If Not (varCounter / varInterval - Fix(varCounter / varInterval) <> 0) Then
                If varInterval > intCurrent Then
                    intCurrent = varInterval
                End If
            End If

            rs.MoveNext
        Loop
        
        If intCurrent <> 0 Then
            rs.FindFirst ("Interval = " & intCurrent)
            MsgBox "PlanNum: " & rs!Plannum & vbNewLine & _
                   "Interval: " & rs!Interval
        End If
    End If
    
  'cleanup
    qdf.Close
    Set qdf = Nothing
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    
End Function

 

[Misty]

Having two Intervals that are the same but for different [Jobnum] is fine. The 12, 24 and 48 refer to how many weeks between working the respective [Planum]. The 24 includes all of the tasks in the 12, and the 48 includes all the tasks in the 12 and the 24. It’s a matter of which of the [Planum] are due. The end result of the calculation tells me which [Planum] is due for each [Jobnum].

Then I use the date span to figure out which one is next after that, (and on and on). Each time a [Jobnum] is printed, it adds 1 to the [Counter] in the host program. The host program won’t forcast which [Jobnum] are due into the future. That’s what I’m trying to do to balance the work load.

I’m not real sure how to use your code, but I’ll see if I can use the help files to figure it out.

Thank you so much for your help. It will really be neat when I can write code like that.

Thanks again,

Misty

 

[modest]

It will really be neat when I can write code like that.

Just to let you know... you just did. All you did was use a different language. A higher-level language. Like I said, all code is, is vocabulary. Of course there's a certain grammar and sentence structure (syntax) involved, but the main part is the logic behind the code and since you can already produce that, you can easily do the code (in any language).

I will update the changes tonight when I get home, I must leave now.


Edit:
By the way, just copy and paste what I did into a module. To test it,
1) paste the code into a new module
2) open the module
3) click anywhere inside the function
4) then click the green arrow button on your toolbar - It's the "Run" button.
a messagebox should popup

Please note that I still need to make the changes so that it will look at each individual JobNum. Additionally, there are many ways to do this. This way isn't necessarily the best, just one of many.

 

[Misty]

You're a nice person. Did you see the little db I put in the samples? That's the way the data looks when I run the query, except there are many, many more [Jobplan].

They are going to put A2003 on my machine at work, so I'm going to go and get another book. The last one was $45. I'll bet this one will be more.

I hope you have a nice evening,

Misty

 

[Mile-O]

Did you see the little db I put in the samples?

You should have attached the db to this thread - at least then people know what the db is for. The Sample DB forum is for complete examples, not for works in progress or for problem solving.

I've attached it to this thread now.

 

[modest]

I'm not sure if this is what you wanted. But it should help you learn how to do it. There are two modules with similar, but different functions. The form uses the Example2 one, the other is just easier to understand and a different way of looping through your table to find the right data.

I hope this is something similar to what you might need.

 

[Misty]

Thank you for this example. I'm going to try to adjust it to get the exact information I need. I do have one other question. If I wanted to show the results as a query that could be used as a step in a process, how would I make that happen?