Selecting the Correct Field For A Query

[SHPsalm139]

I'm working on a school application. I have a form where the user
selects the marking period
(1, 2, 3 or 4). That ties in to the "marking_period" field in a table.
If the user indicates, for example, that it's marking period 1, then a
student's report card grade would be found (from that same table) in
the field "grade1", if marking period 2 is indicated, the report card
grade would be found in the field "grade2" and so forth. The report
card grade for each student then goes through some formatting and is
appended to a grade field (along with other fields) to a table. In my
append query, I would like to say that if the marking period=1 do the
processing on the grade1 field, if marking period=2, process grade2,
etc. My append query:
grade:IIf(([marking_period]="1"),IIf(Left(Format([grade1],"000"),1)="0",Format([grade1],"00"),
IIf(Right([grade1],1)="F","F")))
appends to the field "grade" and has a criteria of <"65" Or "F"
What I'm not sure how to do cleanly is say if the marking_period="2",
do the same processing on field2 and likewise if marking_period = 3 or
4.

Thanks, Sheldon

[Allen Browne]

Hi Sheldon

This problem exists because the data structure is wrong. If you change the
table structure, you will be able to do this easily (as well as a heap of
other things you will want to do in future.)

Whenever you see repeating fields such as marking_period1, marking_period2,
.... it *always* means the structure is wrong. Dead giveaway. Redesign the
table so it contains a field to distinguish whatever is the difference
between these 4 fields, and a single field for the value:
Period Number 1 - 4 or whatever
Grade the actual result
along with the other fields you have.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<SH********@aol.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I'm working on a school application. I have a form where the user
selects the marking period
(1, 2, 3 or 4). That ties in to the "marking_period" field in a table.
If the user indicates, for example, that it's marking period 1, then a
student's report card grade would be found (from that same table) in
the field "grade1", if marking period 2 is indicated, the report card
grade would be found in the field "grade2" and so forth. The report
card grade for each student then goes through some formatting and is
appended to a grade field (along with other fields) to a table. In my
append query, I would like to say that if the marking period=1 do the
processing on the grade1 field, if marking period=2, process grade2,
etc. My append query:
grade:IIf(([marking_period]="1"),IIf(Left(Format([grade1],"000"),1)="0",Format([grade1],"00"),
IIf(Right([grade1],1)="F","F")))
appends to the field "grade" and has a criteria of <"65" Or "F"
What I'm not sure how to do cleanly is say if the marking_period="2",
do the same processing on field2 and likewise if marking_period = 3 or
4.

Thanks, Sheldon

[SHPsalm139]

Thanks Allen. Unfortunately the table I'm reading from is part of a
"mature" software product for student information data that has been
around for about fifteen years that our group didn't write, but do
support. So I do have to work with those fields as they exist. :-(

Sheldon

[Wayne Gillespie]

On 24 Jan 2006 07:01:16 -0800, "SH********@aol.com" <SH********@aol.com> wrote:

I'm working on a school application. I have a form where the user
selects the marking period
(1, 2, 3 or 4). That ties in to the "marking_period" field in a table.
If the user indicates, for example, that it's marking period 1, then a
student's report card grade would be found (from that same table) in
the field "grade1", if marking period 2 is indicated, the report card
grade would be found in the field "grade2" and so forth. The report
card grade for each student then goes through some formatting and is
appended to a grade field (along with other fields) to a table. In my
append query, I would like to say that if the marking period=1 do the
processing on the grade1 field, if marking period=2, process grade2,
etc. My append query:
grade:IIf(([marking_period]="1"),IIf(Left(Format([grade1],"000"),1)="0",Format([grade1],"00"),
IIf(Right([grade1],1)="F","F")))
appends to the field "grade" and has a criteria of <"65" Or "F"
What I'm not sure how to do cleanly is say if the marking_period="2",
do the same processing on field2 and likewise if marking_period = 3 or
4.

Thanks, Sheldon

If you are creating the SQL for the append query dynamically, you could lose the outer IIF statement and build the
string by concatenating the value of [marking_period] directly into the string.
So instead of [Grade1] you would have [Grade" & [marking_period] & "] ......
This would give you [Grade1], [Grade2],[Grade3] or [Grade4] depending on the value of [marking_period].

grade:IIf(Left(Format([grade" & [marking_period] & "],"000"),1)="0",Format([grade" & [marking_period] & "],"00"),
IIf(Right([grade" & [marking_period] & "],1)="F","F"))
*** The syntax may not be 100% correct.

If the append query is a stored query, I think you would need to use 4 nested IIF statements. One for each possible
value of [marking_period].
Wayne Gillespie
Gosford NSW Australia

[Allen Browne]

Perhaps your calculated field could be:
Switch([Marking_period]="1", [grade1], [Marking_period]="2", [grade2],
[Marking_period]="3", [grade3], [Marking_period]="4", [grade4], True,
Null)

If Marking_period is a Number field (not a Text field), lose all the quote
marks.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<SH********@aol.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Thanks Allen. Unfortunately the table I'm reading from is part of a
"mature" software product for student information data that has been
around for about fifteen years that our group didn't write, but do
support. So I do have to work with those fields as they exist. :-(

Sheldon

[SHPsalm139]

Thanks Wayne. I had tried something similar earlier but was
unsuccessful. Taking the simple case, if marking_period = 1
x: "grade" & [marking_period]
x evaluates to "grade1" where I would want to have the actual value of
grade1. I tried different changing the syntax to accomplish this but,
thus far, have been unsuccessful.

Sheldon

[Wayne Gillespie]

On 24 Jan 2006 07:43:28 -0800, "SH********@aol.com" <SH********@aol.com> wrote:

Thanks Wayne. I had tried something similar earlier but was
unsuccessful. Taking the simple case, if marking_period = 1
x: "grade" & [marking_period]
x evaluates to "grade1" where I would want to have the actual value of
grade1. I tried different changing the syntax to accomplish this but,
thus far, have been unsuccessful.

Sheldon

But if you are dynamically building the SQL string you want it to evaluate to "grade1" not the actual value.

The idea is to build an sql statement that can be executed successfully. Say we have a simple update statement like

Dim strSQL as String
strSQL = "Update tblMyTable SET [Grade1] = 75;"
CurrentDb().Execute strSQL, dbFailOnError

The SQL statement will be executed and [Grade1] will be updated to 75.

If we dynamically build the SQL statement by referencing [marking_period] -

strSQL = "UPDATE tblMyTable SET [Grade" & [marking_period] & "] = 75;"

strSQL will be set to one 4 different strings depending on the value of [marking_period].
"Update tblMyTable SET [Grade1] = 75;"
"Update tblMyTable SET [Grade2] = 75;"
"Update tblMyTable SET [Grade3] = 75;"
"Update tblMyTable SET [Grade4] = 75;"

The value of [marking_period] will determine which string is created and which grade field is updated when the sql
string is executed.

Dim strSQL as String
strSQL = "UPDATE tblMyTable SET [Grade" & [marking_period] & "] = 75;"
CurrentDb().Execute strSQL, dbFailOnError
Wayne Gillespie
Gosford NSW Australia

[SHPsalm139]

Thanks, Allen and Wayne, for your help and suggestions. I was able to
use the Switch function to accomplish what I wanted -
grade:
Switch([Marking_period]="1",IIf(Left(Format([grade1],"000"),1)="0",Format([grade1],"00"),IIf(Right([grade1],1)="F","F")),[Marking_period]="2",IIf(Left(Format([grade2],"000"),1)="0",Format([grade2],"00"),IIf(Right([grade2],1)="F","F")),[Marking_period]="3",IIf(Left(Format([grade3],"000"),1)="0",Format([grade3],"00"),IIf(Right([grade3],1)="F","F")),[Marking_period]="4",IIf(Left(Format([grade4],"000"),1)="0",Format([grade4],"00"),IIf(Right([grade4],1)="F","F")))

Sheldon