Subject | How to create a report looking 'un normalised' |
---|---|
Author | petesouthwest |
Post date | 2004-08-21T11:00:32Z |
Hi
I have 2 tables, that look like:
student(adminNo,fname,sname)
results(adminNo,testNo,total,qofwc)
where adminNo is the primary key.
so i might have data in the student table that looks like:
adminNo,fname,sname
001,fred,smith
002,emma,burt
and in the results table:
results:
001,maths1,23,3
001,maths2,30,2
002,maths1,56,1
002,maths2,25,4
The students sit two tests. I need to create a report that looks
like:
student test1Score test2score avg Qofwc
fred,smith 23 30 3
emma,burt 56 25 3
Basically the the report looks like an 'un-normalised' database but
with qofwc being the average value of qofwc.
Can anyone suggest how I should do this? Can it all be done with one
SQL query, and if so what would it be?
My delphi app is using quickrep components that come with delphi 6
and IBObjects sql components.
Thanks
Pete
I have 2 tables, that look like:
student(adminNo,fname,sname)
results(adminNo,testNo,total,qofwc)
where adminNo is the primary key.
so i might have data in the student table that looks like:
adminNo,fname,sname
001,fred,smith
002,emma,burt
and in the results table:
results:
001,maths1,23,3
001,maths2,30,2
002,maths1,56,1
002,maths2,25,4
The students sit two tests. I need to create a report that looks
like:
student test1Score test2score avg Qofwc
fred,smith 23 30 3
emma,burt 56 25 3
Basically the the report looks like an 'un-normalised' database but
with qofwc being the average value of qofwc.
Can anyone suggest how I should do this? Can it all be done with one
SQL query, and if so what would it be?
My delphi app is using quickrep components that come with delphi 6
and IBObjects sql components.
Thanks
Pete