Missing Records On Report

[funderburgh]

This problem is self inflicted. My report is a school transcript. The data is drawn from a make table query. Each time the report is run the table is re-created with the data for the student in question. Not all class records in the table are showing on the report, and the condition of the skipped records is clear: the class records are missing grades.

I have created this problem in the past but I can't remember how. The data is in the table, there is no VB code selecting or manipulating the table, the report data is the table with no select/where condition. The report has one group and two sorts with no conditions.

I simply can't figure out what I did to omit these records and today is the day the transcript is given to the student.

Any brain-storming would be very welcome.

Thanks is advance.

 

[vbaInet]

This problem is self inflicted. My report is a school transcript. The data is drawn from a make table query. Each time the report is run the table is re-created with the data for the student in question. Not all class records in the table are showing on the report, and the condition of the skipped records is clear: the class records are missing grades.

But why a Make-Table query?

 

[funderburgh]

The process of calculating GPA is very complex in our school because we serve special needs students who may be in one of four categories of instruction, each with a GPA modified calculation, for example, an 'A' is a special education course (clearly identified on the transcript) does not equal an 'A' in regular instruction.

The IIF conditional that would be required for four quarters and four different grade levels exceed the character size limitations of a regular query, so the table is constructed from multiple queries.

Your question however lead me to the answer. I had forgotten that I was asked to remove records that lack grades for a special application and replaced the table itself with a "one time use" (right!) query that I gave a very similar name as the table. I told you this was self inflicted. Thanks for the nudge.

 

[vbaInet]

Great! Glad you got that sorted.

NB: I think you would have been able to produce the record source of your report without needing to make a table.

 

[funderburgh]

Perhaps you are right, but here was my stumbling block: To calculate a quarter GPA on the query of the source table (Class/grade records) I would need:

If Class is type "m"

IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3))))

or if Class is type "'s'

IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,0,IIf([First Quarter Grade]<90,1,2))))

or if Class type is 'n'

IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,1,IIf([First Quarter Grade]<80,2,IIf([First Quarter Grade]<90,3,4))))

or if class type is "c" (or 'else')

IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,1.5,IIf([First Quarter Grade]<80,2.5,IIf([First Quarter Grade]<90,3.5,4.5))))

I built the conditional in one statement, but I ran into a size limit (I think it was about 125 characters) in the FIELD statement in query builder, so I broke it into multiple queries.

 

[vbaInet]

You can write a function for this instead. Do you know how to translate that into vba?