Complex calculations, passing value to tables, etc

V

vavroom

I thought I knew how to proceed, but I've wasted nearly the entire day
and I realise that I'm not even sure I'm barking up the right tree! I
hope some of you might be able to enlighten me :)

What I want to do, in a nutshell, is to calculate how many exam papers
to print, based on a number of factor (including how many students are
sitting the exam and the location of the exam).

I am working with an inherited poorly designed database to start, and
I have *no* control over that. I have what I have, and I have to work
with it, unfortunately.

In one table (Examsbm), I have a list of paper offerings, and fields
for how many copies to print.

In another table(Studpervenue), I have a list of all students sitting
exams, and their location.

There's basically 4 locations. PN, Wel, Alb, and Extramural.
Extramural is complicated by the fact that I have nearly 75 venues.
So, if the location is PN, Wel or Alb, there is nothing in the venue
field. If it's Extramural, there's a venue reference.

Using a query with Group By on the student's table, I was able to
calculate how many students are sitting at each location/venue.

I based a form on that query, and using the code at the bottom (sorry
for the long, somewhat messy code), I am able to determine how many
papers need to be printed for each paper offering, broken down by
venue.

Which is fine, however, the next step I need to do is to compile the
information from all the venues into one to determine the number of
papers to print for the Extramural location. Then I need to write
that information to the Examsbm table, as Studpervenue is a temporary
table.

I have a feeling I'm going about this the wrong way. If anyone could
help and point a kind finger in the right direction, I would greatly
appreciate it.

Thanks

===
Note that the code below is really only just to apply the right
calculations, which I don't feel would have worked in a query, what
with the resulting nested if/then, etc.

Private Sub Form_Current()


Dim sExtra As String
sExtra = (Me.paper_line Like "*E*" Or Me.paper_line Like "*B*")

'Calculates the number of extra copies for extramural papers
'Puts the value in sECopies
Dim sECopies As String
Dim stEAlb As String
If Me.exam_centre Like "1010*" Then
If Me.TotalStuds >= 1 And Me.TotalStuds <= 4 Then
sECopies = "5"
Me.txtEMExtras = "5"
ElseIf Me.TotalStuds >= 5 And Me.TotalStuds <= 9 Then
sECopies = "5"
Me.txtEMExtras = "10"
ElseIf Me.TotalStuds >= 10 And Me.TotalStuds <= 14 Then
sECopies = "10"
Me.txtEMExtras = "15"
ElseIf Me.TotalStuds >= 15 Then
sECopies = "10"
Me.txtEMExtras = "20"
End If
Else
If Me.TotalStuds >= 1 And Me.TotalStuds <= 4 Then
sECopies = "2"
Me.txtEMExtras = "5"
ElseIf Me.TotalStuds >= 5 And Me.TotalStuds <= 9 Then
sECopies = "3"
Me.txtEMExtras = "10"
ElseIf Me.TotalStuds >= 10 And Me.TotalStuds <= 14 Then
sECopies = "4"
Me.txtEMExtras = "15"
ElseIf Me.TotalStuds >= 15 Then
sECopies = "5"
Me.txtEMExtras = "20"
End If
End If

'Calculates the number of extra copies for internal papers
'Puts the value in sICopies
Dim sICopies As String
Dim stPN As String
Dim stAlb As String
Dim stWel As String
If Me.paper_line Like "*I 4*" Or Me.paper_line Like "*I2 4*" Or
Me.paper_line Like "*I 15*" Or Me.paper_line Like "*I 20*" Then
stWel = "0"
stAlb = "0"
If Me.TotalStuds <= 5 Then
stPN = "10"
ElseIf Me.TotalStuds >= 6 And Me.TotalStuds <= 30 Then
stPN = "15"
ElseIf Me.TotalStuds >= 31 And Me.TotalStuds <= 60 Then
stPN = "20"
ElseIf Me.TotalStuds >= 61 And Me.TotalStuds <= 120 Then
stPN = "25"
ElseIf Me.TotalStuds >= 121 And Me.TotalStuds <= 180 Then
stPN = "30"
ElseIf Me.TotalStuds >= 181 And Me.TotalStuds <= 240 Then
stPN = "35"
ElseIf Me.TotalStuds >= 241 And Me.TotalStuds <= 300 Then
stPN = "40"
ElseIf Me.TotalStuds >= 301 And Me.TotalStuds <= 360 Then
stPN = "45"
ElseIf Me.TotalStuds >= 361 And Me.TotalStuds <= 420 Then
stPN = "50"
ElseIf Me.TotalStuds >= 421 And Me.TotalStuds <= 480 Then
stPN = "55"
ElseIf Me.TotalStuds >= 481 Then
stPN = "60"
End If
ElseIf Me.paper_line Like "*I 10*" Then
stPN = "0"
stWel = "0"
'if the paper is internal Albany
If Me.TotalStuds <= 60 Then
stAlb = "10"
ElseIf Me.TotalStuds >= 61 And Me.TotalStuds <= 120 Then
stAlb = "15"
ElseIf Me.TotalStuds >= 121 And Me.TotalStuds <= 180 Then
stAlb = "20"
ElseIf Me.TotalStuds >= 181 And Me.TotalStuds <= 240 Then
stAlb = "25"
ElseIf Me.TotalStuds >= 241 And Me.TotalStuds <= 300 Then
stAlb = "30"
ElseIf Me.TotalStuds >= 301 And Me.TotalStuds <= 360 Then
stAlb = "35"
ElseIf Me.TotalStuds >= 361 And Me.TotalStuds <= 420 Then
stAlb = "40"
ElseIf Me.TotalStuds >= 421 And Me.TotalStuds <= 480 Then
stAlb = "45"
ElseIf Me.TotalStuds >= 481 Then
stAlb = "50"
End If
ElseIf Me.paper_line Like "*I 33*" Then
stPN = "0"
stAlb = "0"
'if the paper is internal Wellington
If Me.TotalStuds <= 20 Then
stWel = "15"
ElseIf Me.TotalStuds >= 21 And Me.TotalStuds <= 60 Then
stWel = "20"
ElseIf Me.TotalStuds >= 61 And Me.TotalStuds <= 120 Then
stWel = "30"
ElseIf Me.TotalStuds >= 121 And Me.TotalStuds <= 180 Then
stWel = "35"
ElseIf Me.TotalStuds >= 181 And Me.TotalStuds <= 240 Then
stWel = "40"
ElseIf Me.TotalStuds >= 241 And Me.TotalStuds <= 300 Then
stWel = "45"
ElseIf Me.TotalStuds >= 301 And Me.TotalStuds <= 360 Then
stWel = "50"
ElseIf Me.TotalStuds >= 361 And Me.TotalStuds <= 420 Then
stWel = "55"
ElseIf Me.TotalStuds >= 421 And Me.TotalStuds <= 480 Then
stWel = "60"
ElseIf Me.TotalStuds >= 481 Then
stWel = "65"
End If
End If

'Looks if the paper is extramural or not. Assigns the number of extra
copies
If sExtra = True Then
Me.txtEM = sECopies
Else
Me.txtPN = stPN
Me.txtAlb = stAlb
Me.txtWel = stWel
Me.txtEMExtras = "0"
End If

End Sub
 
B

BruceM

I will take a stab at a few elements of this since nobody else has, but
these are more along the lines of observations and questions than solutions.
Rest of response is inline.

I thought I knew how to proceed, but I've wasted nearly the entire day
and I realise that I'm not even sure I'm barking up the right tree! I
hope some of you might be able to enlighten me :)

What I want to do, in a nutshell, is to calculate how many exam papers
to print, based on a number of factor (including how many students are
sitting the exam and the location of the exam).

I am working with an inherited poorly designed database to start, and
I have *no* control over that. I have what I have, and I have to work
with it, unfortunately.

In one table (Examsbm), I have a list of paper offerings, and fields
for how many copies to print.

In another table(Studpervenue), I have a list of all students sitting
exams, and their location.

There's basically 4 locations. PN, Wel, Alb, and Extramural.
Extramural is complicated by the fact that I have nearly 75 venues.
So, if the location is PN, Wel or Alb, there is nothing in the venue
field. If it's Extramural, there's a venue reference.

Using a query with Group By on the student's table, I was able to
calculate how many students are sitting at each location/venue.

I based a form on that query, and using the code at the bottom (sorry
for the long, somewhat messy code), I am able to determine how many
papers need to be printed for each paper offering, broken down by
venue.

Which is fine, however, the next step I need to do is to compile the
information from all the venues into one to determine the number of
papers to print for the Extramural location. Then I need to write
that information to the Examsbm table, as Studpervenue is a temporary
table.

I have a feeling I'm going about this the wrong way. If anyone could
help and point a kind finger in the right direction, I would greatly
appreciate it.

Thanks

===
Note that the code below is really only just to apply the right
calculations, which I don't feel would have worked in a query, what
with the resulting nested if/then, etc.

Private Sub Form_Current()


Dim sExtra As String
sExtra = (Me.paper_line Like "*E*" Or Me.paper_line Like "*B*")
Click to expand...

What is sExtra? Where is it used? It's hard to see how this is a string.
'Calculates the number of extra copies for extramural papers
'Puts the value in sECopies
Dim sECopies As String
Click to expand...

Why is the number of copies a string?
What is txtEMExtras?
Dim stEAlb As String
Click to expand...

What is stEAlb? Annotating the code is helpful for your own purposes, but
almost essential when you are asking a question.
If Me.exam_centre Like "1010*" Then
If Me.TotalStuds >= 1 And Me.TotalStuds <= 4 Then
sECopies = "5"
Me.txtEMExtras = "5"
ElseIf Me.TotalStuds >= 5 And Me.TotalStuds <= 9 Then
sECopies = "5"
Me.txtEMExtras = "10"
ElseIf Me.TotalStuds >= 10 And Me.TotalStuds <= 14 Then
sECopies = "10"
Me.txtEMExtras = "15"
ElseIf Me.TotalStuds >= 15 Then
sECopies = "10"
Me.txtEMExtras = "20"
End If
Click to expand...

Select Case may be simpler in this situation.
If Me.exam_centre Like "1010*" Then
Select Case Me.TotalStuds
Case 1 To 4
sECopies = "5"
Me.txtEMExtras = "5"
Case < 10
sECopies = "5"
Me.txtEMExtras = "10"
Case Else
etc.
End Select
Else
etc.
End If
Else
If Me.TotalStuds >= 1 And Me.TotalStuds <= 4 Then
sECopies = "2"
Me.txtEMExtras = "5"
ElseIf Me.TotalStuds >= 5 And Me.TotalStuds <= 9 Then
sECopies = "3"
Me.txtEMExtras = "10"
ElseIf Me.TotalStuds >= 10 And Me.TotalStuds <= 14 Then
sECopies = "4"
Me.txtEMExtras = "15"
ElseIf Me.TotalStuds >= 15 Then
sECopies = "5"
Me.txtEMExtras = "20"
End If
End If

'Calculates the number of extra copies for internal papers
'Puts the value in sICopies
Click to expand...

Why? Where is sICopies used?
Dim sICopies As String
Dim stPN As String
Dim stAlb As String
Dim stWel As String
Click to expand...

What are these other strings? I see that they are used, but I don't see
why.
If Me.paper_line Like "*I 4*" Or Me.paper_line Like "*I2 4*" Or
Me.paper_line Like "*I 15*" Or Me.paper_line Like "*I 20*" Then
stWel = "0"
stAlb = "0"
Click to expand...

What is this Me.Paper-line? I sort of see how it is used below, but it's a
bit obscure.
If Me.TotalStuds <= 5 Then
stPN = "10"
ElseIf Me.TotalStuds >= 6 And Me.TotalStuds <= 30 Then
stPN = "15"
ElseIf Me.TotalStuds >= 31 And Me.TotalStuds <= 60 Then
stPN = "20"
Click to expand...

Stop here when posting sample code. Skip to the End If. The rest of the
stPN assigning is not relevant to the problem at hand.
ElseIf Me.TotalStuds >= 61 And Me.TotalStuds <= 120 Then
stPN = "25"
ElseIf Me.TotalStuds >= 121 And Me.TotalStuds <= 180 Then
stPN = "30"
ElseIf Me.TotalStuds >= 181 And Me.TotalStuds <= 240 Then
stPN = "35"
ElseIf Me.TotalStuds >= 241 And Me.TotalStuds <= 300 Then
stPN = "40"
ElseIf Me.TotalStuds >= 301 And Me.TotalStuds <= 360 Then
stPN = "45"
ElseIf Me.TotalStuds >= 361 And Me.TotalStuds <= 420 Then
stPN = "50"
ElseIf Me.TotalStuds >= 421 And Me.TotalStuds <= 480 Then
stPN = "55"
ElseIf Me.TotalStuds >= 481 Then
stPN = "60"
End If
Click to expand...

You shoul be able to do this stuff in a single calculation. A Google groups
search for:
"microsoft public access" round "nearest 5"
will turn up some useful links.
"microsoft public access" round up "nearest 5"
turned up one answer, but maybe some rephrasing of the search string would
improve things. Anyhow, there is surely a way to divide the number of
students by 8, ignoring the decimal places performed by the calculation, and
using that as stPN (except it should probably not be a string).

Round (Me.TotalStuds/8)
may get you close to the correct answer for stPN, as long as it is not a
string. The Mod function may be helpful. I am not skilled in this sort of
number manipulation, but I know it can be a lot simpler than you are making
it.
ElseIf Me.paper_line Like "*I 10*" Then
stPN = "0"
stWel = "0"
'if the paper is internal Albany
If Me.TotalStuds <= 60 Then
stAlb = "10"
ElseIf Me.TotalStuds >= 61 And Me.TotalStuds <= 120 Then
stAlb = "15"
ElseIf Me.TotalStuds >= 121 And Me.TotalStuds <= 180 Then
stAlb = "20"
ElseIf Me.TotalStuds >= 181 And Me.TotalStuds <= 240 Then
stAlb = "25"
ElseIf Me.TotalStuds >= 241 And Me.TotalStuds <= 300 Then
stAlb = "30"
ElseIf Me.TotalStuds >= 301 And Me.TotalStuds <= 360 Then
stAlb = "35"
ElseIf Me.TotalStuds >= 361 And Me.TotalStuds <= 420 Then
stAlb = "40"
ElseIf Me.TotalStuds >= 421 And Me.TotalStuds <= 480 Then
stAlb = "45"
ElseIf Me.TotalStuds >= 481 Then
stAlb = "50"
End If
ElseIf Me.paper_line Like "*I 33*" Then
stPN = "0"
stAlb = "0"
'if the paper is internal Wellington
If Me.TotalStuds <= 20 Then
stWel = "15"
ElseIf Me.TotalStuds >= 21 And Me.TotalStuds <= 60 Then
stWel = "20"
ElseIf Me.TotalStuds >= 61 And Me.TotalStuds <= 120 Then
stWel = "30"
ElseIf Me.TotalStuds >= 121 And Me.TotalStuds <= 180 Then
stWel = "35"
ElseIf Me.TotalStuds >= 181 And Me.TotalStuds <= 240 Then
stWel = "40"
ElseIf Me.TotalStuds >= 241 And Me.TotalStuds <= 300 Then
stWel = "45"
ElseIf Me.TotalStuds >= 301 And Me.TotalStuds <= 360 Then
stWel = "50"
ElseIf Me.TotalStuds >= 361 And Me.TotalStuds <= 420 Then
stWel = "55"
ElseIf Me.TotalStuds >= 421 And Me.TotalStuds <= 480 Then
stWel = "60"
ElseIf Me.TotalStuds >= 481 Then
stWel = "65"
End If
End If

'Looks if the paper is extramural or not. Assigns the number of extra
copies
If sExtra = True Then
Me.txtEM = sECopies
Else
Me.txtPN = stPN
Me.txtAlb = stAlb
Me.txtWel = stWel
Me.txtEMExtras = "0"
End If

End Sub
Click to expand...

The difficulties for anybody reviewing your questions are trying to sort out
the purpose of some of the code, and understanding the database's structure.
 
V

vavroom

Hello Bruce, thanks for taking a stab and making comments :)
What is sExtra? Where is it used? It's hard to see how this is a string.
Click to expand...

Perhaps string isn't the best. sExtra is used a few other times in
the code and instead of retyping me.paper_line, etc I "plugged" it
that way.
Why is the number of copies a string?
Click to expand...

Because I'm an idiot? :)
What is txtEMExtras?
Click to expand...

A field in my form to display the results. Perhaps it's a bad habit,
but I often just get things displayed on a form to see if I get the
result I want, before figuring out the rest.

What is stEAlb? Annotating the code is helpful for your own purposes, but
almost essential when you are asking a question.
Click to expand...

A remnant I should have deleted before posting, apologies.

Select Case may be simpler in this situation.
If Me.exam_centre Like "1010*" Then
Select Case Me.TotalStuds
Case 1 To 4
sECopies = "5"
Me.txtEMExtras = "5"
Case < 10
sECopies = "5"
Me.txtEMExtras = "10"
Case Else
etc.
End Select
Else
etc.
End If
Click to expand...

For some reason, I seem never to be able to make select statements
work...
Why? Where is sICopies used?
Click to expand...

Another remnant. Should have cleaned it up better.
What are these other strings? I see that they are used, but I don't see
why.
Click to expand...

To pass the values into the textbox on the form.

What is this Me.Paper-line? I sort of see how it is used below, but it's a
bit obscure.
Click to expand...

paper_line is the name of the field in a table.

The difficulties for anybody reviewing your questions are trying to sort out
the purpose of some of the code, and understanding the database's structure.
Click to expand...


Thank you for your comments, I appreciate them

N
 
B

BruceM

Hello Bruce, thanks for taking a stab and making comments :)


Perhaps string isn't the best. sExtra is used a few other times in
the code and instead of retyping me.paper_line, etc I "plugged" it
that way.
Click to expand...

OK, then it needs to be in quotes. I think it would be:
sExtra = "Me.paper_line Like ""*E*"" Or Me.paper_line Like ""*B*"""
This link explains the nuances:
http://allenbrowne.com/casu-17.html
Because I'm an idiot? :)


A field in my form to display the results. Perhaps it's a bad habit,
but I often just get things displayed on a form to see if I get the
result I want, before figuring out the rest.
Click to expand...

I often use message boxes, or Debug.Print, but I find message boxes pretty
convenient. I often use one or the other immediately after defining a
string or other variable, so I can see if I am getting the expected value.
Nothing wrong with it, but I usually leave it out of posted code unless it
has a purpose later in the code.
A remnant I should have deleted before posting, apologies.



For some reason, I seem never to be able to make select statements
work...
Click to expand...

What happens when you try to use it?
Another remnant. Should have cleaned it up better.


To pass the values into the textbox on the form.



paper_line is the name of the field in a table.




Thank you for your comments, I appreciate them

N
Click to expand...
Although you did not respond to the remarks about a single calculation in
place of the chains of ElseIf statements, I still think that some sort of
calculation could get you there more simply.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Backwards compatibile macro 0
How to average values from 6 different drop downs in a separate text box? 0
calculate sign module 2
Access 2007 Subscript out of Range (9) 4
Combobox linked to Custom Views 17
Date input to exclude weekends 10
Report Freeze Due to On_Format Event 1
On Format Print Preview Freeze 1

Top