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

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.

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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*")

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

Why is the number of copies a string?
What is txtEMExtras?

> Dim stEAlb As String

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

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

Why? Where is sICopies used?

> Dim sICopies As String
> Dim stPN As String
> Dim stAlb As String
> Dim stWel As String

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"

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"

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

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

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.

Hello Bruce, thanks for taking a stab and making comments

> > sExtra = (Me.paper_line Like "*E*" Or Me.paper_line Like "*B*")
>
> What is sExtra? Where is it used? It's hard to see how this is a string.

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?

Because I'm an idiot?

> What is txtEMExtras?

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.

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

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

> Why? Where is sICopies used?

Another remnant. Should have cleaned it up better.

> > Dim stPN As String
> > Dim stAlb As String
> > Dim stWel As String
>
> What are these other strings? I see that they are used, but I don't see
> why.

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.

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.


Thank you for your comments, I appreciate them

N

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello Bruce, thanks for taking a stab and making comments
>
>> > sExtra = (Me.paper_line Like "*E*" Or Me.paper_line Like "*B*")
>>
>> What is sExtra? Where is it used? It's hard to see how this is a
>> string.
>
> 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.

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

>
>> Why is the number of copies a string?
>
> Because I'm an idiot?
>
>> What is txtEMExtras?
>
> 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.

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.
>
>
>> What is stEAlb? Annotating the code is helpful for your own purposes,
>> but
>> almost essential when you are asking a question.
>
> 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
>
> For some reason, I seem never to be able to make select statements
> work...

What happens when you try to use it?
>
>> Why? Where is sICopies used?
>
> Another remnant. Should have cleaned it up better.
>
>> > Dim stPN As String
>> > Dim stAlb As String
>> > Dim stWel As String
>>
>> What are these other strings? I see that they are used, but I don't see
>> why.
>
> 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.
>
> 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.
>
>
> Thank you for your comments, I appreciate them
>
> N
>
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.