Serious help needed!

[Reppers]

Right, I've been bouncing this around in various parts of this forum, and I'm stumped. Still.

I have 2 tables - tblChild, and tblGrades.

The child table stores all the details about the child - surname, forname, gender etc.

The Grades table stores grades given in 5 areas - Reading, writing, numeracy, science, and speaking and listening. The grades are given in september, january, april, and july. At this point I'd like to point out that I know that this isn't fully normalised, however, I am a TEACHER not a database designer, and it seemed far simpler for the job required of it to just bosh them all in one table.

The data I'm actually interested in is the points progress made - so the most recent assessment minus the previous assessment.

I want to be able to display summaries of the progress made in a simple way - ideally all one one form. This form would show the average points progress made across the school, and then also various sub-sets of children - for example all the males, all the females, all those with a particular special need.

To attempt to achieve this I have columns in the grades table called ReadingProgress, writingProgress ... etc, which I populated using an update query and this formula;

IIf(IsNull([RJulyPoints]),IIf(IsNull([RAprilPoints]),IIf(IsNull([RJanuaryPoints]),"",[RJanuaryPoints]-[RSeptemberPoints]),[RAprilPoints]-[RJanuaryPoints]),[RJulyPoints]-[RAprilPoints])

This worked and on running the query, the progress table was updated with the difference between the previous and most current assessments.

However, when I came to make a query based on tblGrades, and tried to average the ReadingProgress (or any of the other columns updated by the update query) it now says it's typed incorrectly, or too complicated to evaluate. If I have the query to filter tblGrades it does it fine, and picks out the current year (or whatever), and is only when it adds the average that it does it.

I don't know if anyone can help me with this, but I'm putting it out there!

Please help me! Otherwise I'm sure my computer may soon be leaving via the window!

Thanks in advance for ANY help!

 

[Rabbie]

At this point I'd like to point out that I know that this isn't fully normalised, however, I am a TEACHER not a database designer, and it seemed far simpler for the job required of it to just bosh them all in one table.

Point noted but it is only fair to point out that data normalisation is not something thought up by database designers to make life difficult for other people - it is in fact a way of making things easier in the long run and your lack of normalisation is why you are having problems.

 

[Reppers]

I do appreciate that - and if this problem does stem from the lack of normalisation then I acknowledge I may have to start from scratch. I put that sentence in really to make it known that I was aware of the lack of normalisation - I am a novice, and had I known I'd reach this stage and then get screwed I would have spent more time trying to get my head around it!

 

[WayPay]

A quick fix might be replacing the "" in that mind-boggling expression with a 0.

I do agree with the previous replier, though. Having the database normalized will save you a lot of hassle in the long run. It all depends on how long the run is going to be

 

[Reppers]

You Sir, are a scholar and a gentleman!

It has fixed the problem! However, having been forced to contemplate the potential complete collapse of the whole database, and now having come on somewhat in my access-ing abilities, I do see the point in normalisation. However, this botch job will do for now . . . maybe I'll make version 2 at a later date!

 

[boblarson]

You Sir, are a scholar and a gentleman!

It has fixed the problem! However, having been forced to contemplate the potential complete collapse of the whole database, and now having come on somewhat in my access-ing abilities, I do see the point in normalisation. However, this botch job will do for now . . . maybe I'll make version 2 at a later date!

This might be helpful:
http://www.allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf

 

[Rabbie]

You Sir, are a scholar and a gentleman!

It has fixed the problem! However, having been forced to contemplate the potential complete collapse of the whole database, and now having come on somewhat in my access-ing abilities, I do see the point in normalisation. However, this botch job will do for now . . . maybe I'll make version 2 at a later date!

Glad to hear you have got it working Also to hear you do see the point of normalisation The rules can be bent for expediency but you have know why you are bending them